用事务为防止并发时多次更新同一记录（锁）

--准备测试表及测试数据
USE tempdb
GO
IF OBJECT_ID('task') IS NOT NULL DROP TABLE task
GO
CREATE TABLE task (
	taskId INT IDENTITY(1,1) PRIMARY KEY
	,taskName NVARCHAR(50) NOT NULL
	,d DATETIME NOT NULL DEFAULT(GETDATE())
	,flag INT NOT NULL DEFAULT(0)
	,mytimestamp TIMESTAMP NOT NULL
)
GO
--插入10000条数据
;WITH cte AS(
	SELECT sv.number FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND 1000
)
INSERT INTO task(taskName)
SELECT '任务'+ltrim(ROW_NUMBER()over(order BY(select 1)))
FROM cte AS a CROSS APPLY (SELECT TOP 10 * FROM cte) AS b

注：下面用 4 种不同的方式来测试， 每种方式测试完之后都要执行上面的代码以便重新开始新的测试。

一、不用事务。

--1. 不用事务的代码
DECLARE @taskId INT
SELECT TOP 1 @taskId=taskId FROM task AS t WITH (XLOCK,ROWLOCK) WHERE flag=0 ORDER BY t.taskId
UPDATE task
SET
	flag = flag+1,
	d = GETDATE()
WHERE taskId=@taskId

验证（不合符要求）：

SELECT flag,COUNT(1) AS cnt 
FROM task
GROUP BY flag
/*
flag	cnt
0	9090
3	5
1	827
4	1
2	77
*/

二、用事务（悲观锁）：

--2. 悲观锁
BEGIN TRAN
BEGIN TRY
	DECLARE @taskId INT	
	SELECT TOP 1 @taskId=taskId FROM task AS t WITH (XLOCK,ROWLOCK) WHERE flag=0 ORDER BY t.taskId
	UPDATE task
	SET
		flag = flag+1,
		d = GETDATE()
	WHERE taskId=@taskId
	COMMIT TRAN;
END TRY
BEGIN CATCH
	DECLARE @errMsg NVARCHAR(MAX)
	SET @errMsg=ERROR_MESSAGE()
	RAISERROR(16,1,@errMsg)
	ROLLBACK TRAN;
END CATCH

验证 (正确)：

SELECT flag,COUNT(1) AS cnt 
FROM task
GROUP BY flag
/*
flag	cnt
0	9000
1	1000
*/

三、乐观锁

--3. 乐观锁(循环的目的是为了可能的情况下必须更新一条记录)
DECLARE @taskId INT,@timestamp TIMESTAMP
WHILE EXISTS(SELECT * FROM task AS t WHERE flag=0)
BEGIN
	SELECT TOP 1 @taskId=taskId,@timestamp=t.mytimestamp 
	FROM task AS t WHERE flag=0 ORDER BY t.taskId
	
	UPDATE task
	SET
		flag = flag+1,
		d = GETDATE()
	WHERE taskId=@taskId AND mytimestamp=@timestamp 
	
	IF @@ROWCOUNT>0
	BEGIN
		BREAK;
	END
END

 

验证：

 

SELECT flag,COUNT(1) AS cnt 
FROM task
GROUP BY flag
/*
flag	cnt
0	9000
1	1000
*/ 

四、用 sp_releaseapplock

1. 先增加备用的存储过程：

IF OBJECT_ID('proc_update') IS NOT NULL DROP PROC proc_update
GO
CREATE PROC proc_update
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @taskId INT
	SELECT TOP 1 @taskId=taskId FROM task AS t WITH (XLOCK,ROWLOCK) WHERE flag=0 ORDER BY t.taskId
	UPDATE task
	SET
		flag = flag+1,
		d = GETDATE()
	WHERE taskId=@taskId
END
GO
IF OBJECT_ID('proc_lock_update') IS NOT NULL DROP PROC proc_lock_update
GO
CREATE PROC proc_lock_update
AS
BEGIN
	DECLARE @result int;
	BEGIN TRY
	BEGIN TRANSACTION;
		EXEC @result = sp_getapplock @Resource = 'proc_update', @LockMode = 'Exclusive';
		IF @result<0
		BEGIN
			RAISERROR ('wait' ,16,1)
			ROLLBACK TRANSACTION
		END
		EXEC proc_update
		EXEC @result =sp_releaseapplock @Resource = 'proc_update';
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		EXEC @result =sp_releaseapplock @Resource = 'proc_update';
		ROLLBACK TRANSACTION
	END CATCH
END
GO

2. 并发测试代码：

--4. sp_releaseapplock
EXEC proc_lock_update 

验证 (正确)：

SELECT flag,COUNT(1) AS cnt 
FROM task
GROUP BY flag
/*
flag	cnt
0	9000
1	1000
*/ 

 

 

序号	说明	消耗秒数	是否正确完成
1	无事务，不作特殊处理	0.5153	N
2	用事务，悲观锁	0.6704	Y
3	不用事务，乐观锁	3.9225	Y
4	sp_releaseapplock	0.9961	Y

 

由上可知：2,3,4 三种方式都可以实现， 具体用哪一种就看你的喜好了。