死锁---多进程调用存储过程产生死锁，求原理和完美解决方案

[z_iori2]

现有如下存储过程代码，多进程执行会产生死锁，用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

[rubynle]

你还是自己说下逻辑吧，你这里只有存储过程的内容，里面还调用了函数呢 简单从存储过程看肯定不需要使用游标，也不需要循环

[z_iori2]

函数里面没有表操作，只是对传入的参数进行了一系列判断然后返回boolean。 逻辑其实挺简单的，就是我有一张任务表，里面有一些预定义的任务属性，外部进程每次在某个条件下就会执行这个存储过程，先删除过期的任务， 然后根据当前的用户属性，更新TaskRecord表，看看是要添加一个新的任务记录，还是修改现有的任务记录（比如连续签到的任务，修改签到次数）。感觉逻辑也不复杂，咋会出现死锁实在是不明觉厉.

[z_iori2]

另外补充一点，这个存储过程的执行是由触发器触发的，当修改用户金币的表的时候会触发这个存储过程，不过修改金币的存储过程本身并没有操作TaskRecord表

[z_iori2]

简单建立了一个表 Id int, Name varchar(50) 试了一下，跟我现在提问的逻辑差不多, 确实会出现死锁. 1. 建立表

USE [TestDB]
GO
/****** Object:  Table [dbo].[Test]    Script Date: 2014/8/8 13:42:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Test](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

2. 开两个查询窗口分别执行如下语句.

USE [TestDB]
GO
declare @num int 
set @num=0 
while @num<10
begin 
BEGIN TRAN
		waitfor delay '00:00:03'
DELETE Test  WHERE Id>30
waitfor delay '00:00:03'
INSERT INTO [dbo].[Test] ([name]) VALUES ('a')
waitfor delay '00:00:03'
UPDATE [dbo].[Test]
   SET [name] = 'b' WHERE id > 20
set @num = @num+1
commit tran
end
go

[Tiger_Zhao]

1)是不是 TaskRecord 的聚集索引 TaskId 不是第一位的？删除的记录分散导致锁升级。建议用 (TaskId,[TimeStamp]) 聚集。

2)TaskRecord 的过期记录不要在 SP 中删。
用一个计算列 [b]过期 AS CASE WHEN [TimeStamp] < CURRENT_TIMESTAMP THEN 1 ELSE 0 END[/B]，查询时直接过滤。
然后用其他的任务定期清理过期记录。

[z_iori2]

把Delete移动到其他地方倒是个好建议，多谢，不过目前的问题还是不是很明白，TaskRecord表只有一个primary key作为索引，表创建语句如下。而且我在#4提出的这个问题能否帮忙解答一下，感觉和我现在的逻辑很相似~小弟也是新手，锁也是昨天才脑补了一下。。。

USE [Poke]
GO

/****** Object:  Table [dbo].[TaskRecord]    Script Date: 2014/8/8 14:45:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TaskRecord](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NOT NULL,
	[TaskId] [int] NOT NULL,
	[IsFinished] [bit] NOT NULL,
	[Bonus] [bigint] NOT NULL,
	[CurValue] [bigint] NOT NULL,
	[TimeStamp] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

[rubynle]

先建立taskid和userid的联合索引 然后把程序里面根据taskid和userid删除数据的方法都改下 先根据taskid和userid查出id，然后根据id删除 理论上应该不会出现死锁了

[Tiger_Zhao]

因为是按照主键(Id)聚集的，TaskRecord 是按插入先后次序存放的，那么你 DELETE 时删除的记录是分布在许多页上面，这很容易升级为表锁，而表锁不冲突才怪呢。

将主键的 CLUSTERED 去掉，建一个 (TaskID,UserId,[TimeStamp]) 的聚集索引，记录是按照聚集索引的次序存放的，这样更改的记录是连续的，一般页锁就够用了，不容易起冲突。
可能无需采用后期删除的方案。

4楼就是简化版，原理是一样的。

[Q315054403]

想想两个人，对着一个电话、电话本，要同时拿到手才能打电话 死锁原理就清楚了 解决办法，就是都按顺序如 必须先拿电话才能拿电话本就OK