34,837
社区成员




--方法1
BEGIN TRAN
DECLARE @id INT
SELECT TOP 1 @id=id FROM list WITH(ROWLOCK,UPDLOCK) WHERE USED=0 ORDER BY id ASC
UPDATE list SET USED=1 WHERE id=@id
SELECT @id
COMMIT TRAN
--方法2
SET ROWCOUNT 1
UPDATE list SET [used] = 1
OUTPUT Deleted.id
WHERE [used]=0
下面的代码经50个并发验证没有问题。
using System;
using System.Data.SqlClient;
using System.Threading.Tasks;
namespace ConsoleApp3
{
class Program
{
static void Main(string[] args)
{
string sql1 =
@"BEGIN TRAN
DECLARE @id INT
SELECT TOP 1 @id=id FROM list WITH(ROWLOCK,UPDLOCK) WHERE USED=0 ORDER BY id ASC
UPDATE list SET USED=1 WHERE id=@id
SELECT @id
COMMIT TRAN
";
string sql2 =
@"SET ROWCOUNT 1
UPDATE list SET [used] = 1
OUTPUT Deleted.id
WHERE [used]=0";
//测试两种方法,每次50个并发
Console.WriteLine("方法1.");
Parallel.For(0, 50, item => {
ExecuteSQL(sql1);
});
Console.WriteLine("方法2.");
Parallel.For(0, 50, item => {
ExecuteSQL(sql2);
});
Console.Read();
}
private static void ExecuteSQL(string sql)
{
string connString = @"Data Source=(local)\sqlserver2014;Initial Catalog=AdventureWorks2014;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
object r = cmd.ExecuteScalar();
Console.WriteLine(r==null?"null":r.ToString());
}
}
}
}
IF OBJECT_ID('list') IS NOT NULL DROP TABLE list
CREATE TABLE list(id INT PRIMARY KEY,[used] BIT NOT NULL DEFAULT(0))
INSERT INTO list(id)
SELECT number
FROM [master]..spt_values WHERE TYPE='p' AND number BETWEEN 1 AND 100
SELECT * FROM list
using System;
using System.Data.SqlClient;
using System.Threading.Tasks;
namespace ConsoleApp3
{
class Program
{
static void Main(string[] args)
{
string sql1 =
@"declare @id int
SELECT TOP 1 @id=a.id from list AS a with(rowLOCK) where used=0
if @id is not null
begin
select @id
update list set used=1 where id=@id
end
";
string sql2 =
@"SET ROWCOUNT 1
UPDATE list SET [used] = 1
OUTPUT Deleted.id
WHERE [used]=0";
//测试两种方法,每次5个并发
Console.WriteLine("方法1.");
Parallel.For(0, 5,item=>{
ExecuteSQL(sql1);
});
Console.WriteLine("方法2.");
Parallel.For(0, 5, item => {
ExecuteSQL(sql2);
});
Console.Read();
}
private static void ExecuteSQL(string sql)
{
string connString = @"Data Source=(local)\sqlserver2014;Initial Catalog=AdventureWorks2014;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
int r = Convert.ToInt32(cmd.ExecuteScalar());
Console.WriteLine(r);
}
}
}
}
BEGIN TRANSACTION
Select top 1 * From List where used=0 and Readed = 0
update table set Readed = 1
COMMIT TRANSACTION