现有如下存储过程代码，多进程执行会产生死锁，用profiler看了一下，估计是delete和insert之间产生了死锁，于是试着在delete的时候加上了 with (tablockx),这样的话进程2刚进来delete的时候一定会等到进程1的tablockx释放才会执行, 这样做果然没有产生死锁了，但是问题是，发现进程2会等到进程1里面的整个while执行完（或者说命令执行完）才会拿到锁继续往下执行，而不是说每次while里面的事务结束就能拿到锁，从而交替执行.这样如果我n个进程同时执行就只能一个一个串行的来，本来如果能交替执行的话，while循环次数少的进程就能先执行完。
所以我想问一下‘
1. 我的这个解决死锁的方式是否正确,说实话我都不太确定死锁出现的原因...望大神指点.
2. tablockx的生命周期是怎么样的，不是应该是事务完成就能释放么？
profiler 死锁图：
伪代码如下：
BEGIN
SET NOCOUNT ON;
Declare c Cursor For <select * from Task>
Open c
Fetch next From c into <params....>
While <condition..> Begin
BEGIN TRAN
--删除过期任务记录
DELETE TaskRecord where ....
--记录任务进程
SELECT xxxx FROM TaskRecord WHERE .....
IF @@ROWCOUNT=0
BEGIN
.....
INSERT INTO TaskRecord .....
END
END
ELSE IF xxxxx
BEGIN
UPDATE TaskRecord .....
END
COMMIT TRAN
Fetch next From c into ....
End
Close c
Deallocate c
End
-----------------------------------------------------------------------------------------------------------------------------------------
原始代码：
CREATE PROCEDURE [dbo].[TaskProcess]
@userId INT,
@fieldId INT,
@processValue BIGINT,
@isAddValue BIT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Id INT,@Operation INT,@Value INT,@Cycle INT,@IsAutoAccept BIT,@IsRepeatable BIT,@Bonus BIGINT
Declare c Cursor For (Select Id,Operation,Value,Cycle,IsAutoAccept,IsRepeatable,Bonus
From Task WHERE FieldId=@fieldId AND IsPublished=1)
Open c
Fetch next From c into @Id,@Operation,@Value,@Cycle,@IsAutoAccept,@IsRepeatable,@Bonus
While @@Fetch_Status=0 Begin
--删除过期任务记录
DELETE TaskRecord WHERE TaskId=@Id AND UserId=@userId
AND [TimeStamp] < CURRENT_TIMESTAMP
DECLARE @IsTaskFinish BIT
DECLARE @newValue BIGINT
DECLARE @expiredTime DATETIME
SET @expiredTime=dbo.GetExpiredTime(@Cycle)
--记录任务进程
BEGIN TRAN
DECLARE @recordId INT,@IsFinished BIT,@curValue BIGINT
SELECT @recordId=Id,@IsFinished=IsFinished,@curValue=CurValue
FROM TaskRecord WHERE TaskId=@Id AND UserId=@userId
IF @@ROWCOUNT=0
BEGIN
IF @IsAutoAccept=0
BEGIN
PRINT 'Task not accept'
END
ELSE
BEGIN
SET @newValue=@processValue
SET @IsTaskFinish=dbo.IsTaskFinish(@newValue,@Operation,@Value)
IF @IsTaskFinish=0
SET @Bonus = 0
INSERT INTO TaskRecord (UserId,TaskId,IsFinished,Bonus,CurValue,[TimeStamp])
VALUES (@userId,@Id,@IsTaskFinish,@Bonus,@newValue,@expiredTime)
SET @recordId=@@IDENTITY
END
END
ELSE IF @IsFinished = 0 OR @IsRepeatable = 1
BEGIN
IF @isAddValue=1
SET @newValue=@curValue + @processValue
ELSE
SET @newValue=@processValue
SET @IsTaskFinish=dbo.IsTaskFinish(@newValue,@Operation,@Value)
IF @IsTaskFinish=0
SET @Bonus = 0
UPDATE TaskRecord SET IsFinished=@IsTaskFinish,Bonus=Bonus+@Bonus,CurValue=@newValue
WHERE Id=@recordId
END
COMMIT TRAN
Fetch next From c into @Id,@Operation,@Value,@Cycle,@IsAutoAccept,@IsRepeatable,@Bonus
End
Close c
Deallocate c
END
GO
------解决方案--------------------
因为是按照主键(Id)聚集的，TaskRecord 是按插入先后次序存放的，那么你 DELETE 时删除的记录是分布在许多页上面，这很容易升级为表锁，而表锁不冲突才怪呢。
将主键的 CLUSTERED 去掉，建一个 (TaskID,UserId,[TimeStamp]) 的聚集索引，记录是按照聚集索引的次序存放的，这样更改的记录是连续的，一般页锁就够用了，不容易起冲突。
可能无需采用后期删除的方案。
4楼就是简化版，原理是一样的。