updlock 与 holdlock 区别

begin tran begin tran select * from a with(updlock) where [id] in (2,3,4)   waitfor delay '00:00:04' commit tran

在另外一个sql连接中这样写： select * from a with(updlock) where [id] =4

发现第二个连接里的sql语句必须等到第一个连接里的事务完成才执行完成，

这是因为第二个连接的更新锁认为第一个连接里的更新锁可能会进行修改

转换为排它锁，所以要等第一个连接的事务执行完成才执行。

如果第二个连接里的sql语句这样写：

select * from a with(holdlock) where [id]

=4,则不不用等第一个连接事务执行完毕才执行。

首先执行下面的建库脚本

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table2](
 [A] [nvarchar](10) NULL,
 [B] [nvarchar](10) NOT NULL,
 [C] [nvarchar](10) NULL,
 CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED
(
 [B] 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
INSERT [dbo].[table2] ([A], [B], [C]) VALUES (N'A', N'B', N'C')
INSERT [dbo].[table2] ([A], [B], [C]) VALUES (N'aa1', N'b1', N'11')
INSERT [dbo].[table2] ([A], [B], [C]) VALUES (N'aa1', N'b2', N'11')
INSERT [dbo].[table2] ([A], [B], [C]) VALUES (N'aa1', N'b3', N'11')
INSERT [dbo].[table2] ([A], [B], [C]) VALUES (N'11', N'b4', N'11')
INSERT [dbo].[table2] ([A], [B], [C]) VALUES (N'aa1', N'b5', N'11')

创建查询一：

 SET TRANSACTION ISOLATION level READ UNCOMMITTED
 begin tran
 select * from table2 with(HOLDLOCK)    where B = 'b4'
 waitfor delay '00:00:5'
  update table2  set A ='11' where B = 'b4'
 EXEC sp_lock @@spid
commit tran

创建查询二：

SET TRANSACTION ISOLATION level READ UNCOMMITTED
begin tran
    select * from table2 with(HOLDLOCK)  where B = 'b4'
    waitfor delay '00:00:10'
    update table2  set A ='11' where B = 'b4'
    EXEC sp_lock @@spid
 commit tran

此时执行完查询一，再执行查询二会出现死锁，会出现对于锁定同一行记录都在请求，但是彼此又不释放资源的情况。

这时候就体现出 UPDLock的作用了，将上面的HoldLock替换成UPDLock就不会出现死锁的情况。

我们来分析一下UPDLock的执行过程，UPDLock是HOLDLOCK和XLOCK的混合体，在没有更新操作的时候，

它就是一个共享锁。当前事务有更新锁的时候，另一个事务可以使用共享锁读取数据。但是如果我们事务中还有更新操作，

那么为了避免死锁，我们在需要更新的事务中都使用更新锁。当两个都拥有更新锁的事务在执行的时候，只会有一个事务获得了

更新锁，另一个需要等待，其中原因我们看一下下面的锁兼容矩阵：

我们发现更新锁和更新锁是冲突的，所以必须要等待。

当执行update语句时候就会把共享锁转换为排他锁，直至事务结束。其实我对更新锁的理解就是它在共享锁上设置了一个标记，

当有这个标记的时候，共享锁不能兼容必须等待，从而避免了死锁的发生。

 

PS：查看死锁信息的几个方法：

1.select * from sys.dm_tran_locks
 where
 request_session_id in(63,64)

2.

dbcc traceon (1204, 3605, -1)

dbcc tracestatus(-1)

IF OBJECT_ID('tempdb.dbo.#ErrorLog') IS Not Null  DROP TABLE #ErrorLog  CREATE TABLE #ErrorLog (Id int IDENTITY (1, 1) NOT NULL, a DATETIME, b VARCHAR(10), c VARCHAR(MAX))  --将当前日志记录插入临时表  INSERT INTO #ErrorLog EXEC master.dbo.sp_readerrorlog  --将死锁信息插入用户表  select *  from #ErrorLog   where id >= (select MAX(id) from #ErrorLog WHERE c Like '%Deadlock%') 

DROP TABLE #ErrorLog

3.exec sp_lock
 exec sp_who

4.SQL Server Profile

 DBCC 详解