sqlserver里新增修改删除记录insert,update,delete时加的行锁还是表锁的问题

2015-8-12 14:27

参加的一个项目，开发的过程中，表结构变来变去的。碰到一诡异问题，客户要求，他们自己设计的一表中的删除标志字段IsDeleted去掉。原因居然是说Update效率低，Insert和delete效率高。Update时候会锁表，Insert和delete则不会。大神就是大神，思考的问题常常为人所不思，头一次听说不是从业务考虑，从执行效率考虑的。即时从维护效率上考虑也行哇。为了这个问题，还是特别的去查了下，要想知道具体insert,update,delete都加了什么锁，可执行如下脚本


--   开事务,   以保持锁 
BEGIN   TRAN 

--   更新 
update  Class1
set   Name   =   '123'   
where   Id  =   2 

--   列出锁信息 
EXEC   sp_lock   @@spid 

--   提交或者回滚事务 
COMMIT   TRAN

输出结果如下：

spid   dbid   ObjId   IndId   Type   Resource   Mode   Status
52   7   0   0   DB       S   GRANT
52   7   2105058535   1   PAG   1:79     IX   GRANT
52   1   1131151075   0   TAB       IS   GRANT
52   7   2105058535   0   TAB       IX   GRANT
52   7   2105058535   1   KEY   (61a06abd401c)     X   GRANT

各列说明如下：

列名	数据类型	说明
spid	smallint	请求锁的进程的数据库引擎会话 ID 号。
dbid	smallint	保留锁的数据库的标识号。可以使用 DB_NAME() 函数来标识数据库。
ObjId	int	持有锁的对象的标识号。可以在相关数据库中使用 OBJECT_NAME() 函数来标识对象。值为 99 时是一种特殊情况，表示用于记录数据库中页分配的其中一个系统页的锁。
IndId	smallint	持有锁的索引的标识号。
Type	nchar(4)	锁的类型： RID = 表中单个行的锁，由行标识符 (RID) 标识。 KEY = 索引内保护可串行事务中一系列键的锁。 PAG = 数据页或索引页的锁。 EXT = 对某区的锁。 TAB = 整个表（包括所有数据和索引）的锁。 DB = 数据库的锁。 FIL = 数据库文件的锁。 APP = 指定的应用程序资源的锁。 MD = 元数据或目录信息的锁。 HBT = 堆或 B 树索引的锁。在 SQL Server 中此信息不完整。 AU = 分配单元的锁。在 SQL Server 中此信息不完整。
Resource	nchar(32)	标识被锁定资源的值。值的格式取决于 Type 列标识的资源类型： Type 值：Resource 值 RID：格式为 fileid:pagenumber:rid 的标识符，其中 fileid 标识包含页的文件，pagenumber 标识包含行的页，rid 标识页上的特定行。fileid 与 sys.database_files 目录视图中的 file_id 列相匹配。 KEY：数据库引擎内部使用的十六进制数。 PAG：格式为 fileid:pagenumber 的数字，其中 fileid 标识包含页的文件，pagenumber 标识页。 EXT：标识区中的第一页的数字。该数字的格式为 fileid:pagenumber。 TAB：没有提供信息，因为已在 ObjId 列中标识了表。 DB：没有提供信息，因为已在 dbid 列中标识了数据库。 FIL：文件的标识符，与 sys.database_files 目录视图中的 file_id 列相匹配。 APP：被锁定的应用程序资源的唯一标识符。格式为 DbPrincipleId:<资源字符串的前 2 个到 16 个字符><哈希运算值>。 MD：随资源类型而变化。有关详细信息，请参阅 sys.dm_tran_locks (Transact-SQL) 中 resource_description 列的说明。 HBT：没有提供任何信息。请改用 sys.dm_tran_locks 动态管理视图。 AU：没有提供任何信息。请改用 sys.dm_tran_locks 动态管理视图。
模式	nvarchar(8)	所请求的锁模式。可以是： NULL = 不授予对资源的访问权限。用作占位符。 Sch-S = 架构稳定性。确保在任何会话持有对架构元素（例如表或索引）的架构稳定性锁时，不删除该架构元素。 Sch-M = 架构修改。必须由要更改指定资源架构的任何会话持有。确保没有其他会话正在引用所指示的对象。 S = 共享。授予持有锁的会话对资源的共享访问权限。 U = 更新。指示对最终可能更新的资源获取的更新锁。用于防止一种常见的死锁，这种死锁在多个会话锁定资源以便稍后对资源进行更新时发生。 X = 排他。授予持有锁的会话对资源的独占访问权限。 IS = 意向共享。指示有意将 S 锁放置在锁层次结构中的某个从属资源上。 IU = 意向更新。指示有意将 U 锁放置在锁层次结构中的某个从属资源上。 IX = 意向排他。指示有意将 X 锁放置在锁层次结构中的某个从属资源上。 SIU = 共享意向更新。指示对有意在锁层次结构中的从属资源上获取更新锁的资源进行共享访问。 SIX = 共享意向排他。指示对有意在锁层次结构中的从属资源上获取排他锁的资源进行共享访问。 UIX = 更新意向排他。指示对有意在锁层次结构中的从属资源上获取排他锁的资源持有的更新锁。 BU = 大容量更新。用于大容量操作。 RangeS_S = 共享键范围和共享资源锁。指示可串行范围扫描。 RangeS_U = 共享键范围和更新资源锁。指示可串行更新扫描。 RangeI_N = 插入键范围和 Null 资源锁。用于在将新键插入索引前测试范围。 RangeI_S = 键范围转换锁。由 RangeI_N 和 S 锁的重叠创建。 RangeI_U = 由 RangeI_N 和 U 锁的重叠创建的键范围转换锁。 RangeI_X = 由 RangeI_N 和 X 锁的重叠创建的键范围转换锁。 RangeX_S = 由 RangeI_N 和 RangeS_S 锁的重叠创建的键范围转换锁。 RangeX_U = 由 RangeI_N 和 RangeS_U 锁的重叠创建的键范围转换锁。 RangeX_X = 排他键范围和排他资源锁。这是在更新范围中的键时使用的转换锁。
Status	nvarchar(5)	锁的请求状态： CNVRT：锁正在从另一种模式进行转换，但是转换被另一个持有锁（模式相冲突）的进程阻塞。 GRANT：已获取锁。 WAIT：锁被另一个持有锁（模式相冲突）的进程阻塞。

以上sql可以分别改为insert，update，delete试下,结果基本相同

从上面结果中可以看出，sqlserver插入，修改和删除记录时候会对表加IX锁

sqlserver对锁的兼容定义我们可以参考如下表

	现有授予模式
请求模式	IS	S	U	IX	SIX	X
意向共享 (IS)	是	是	是	是	是	否
共享 (S)	是	是	是	否	否	否
更新 (U)	是	是	否	否	否	否
意向排他 (IX)	是	否	否	是	否	否
意向排他共享 (SIX)	是	否	否	否	否	否
排他 (X)	否	否	否	否	否	否

从上面表格可以看出，IX与IX锁是不冲突的，最后结论如下

sqlserver增删改数据时候，对表加IX锁，会阻止非脏读查询，但是不会阻止其他的不同记录的增删改

为了验证这个结论，我再做个如下测试

开个查询窗口运行如下脚本

--   开事务,   以保持锁 
BEGIN   TRAN 

--   更新 
update  Class1
set   Name   =   '123'   
where   Id  =   2 

--   列出锁信息 
EXEC   sp_lock   @@spid 

DECLARE @i INT = 0;
WHILE @i < 100000000
	SET @i = @i + 1;

--   提交或者回滚事务 
COMMIT   TRAN

上面脚本运行过程中，新开查询窗口运行如下脚本

INSERT      TOP (1000)
INTO            Class1(Name, ContentText)
VALUES     (N'345', NULL)

发现执行的插入操作完全不受影响

但是如果执行select则会被堵塞住，喜欢研究的亲们可以自己动手测试下。。本文原创，转载请注明出处，谢谢

点击：loading..

收藏到：中国收客网

最新日志

sqlserver里新增修改删除记录insert,update,delete时加的行锁还是表锁的问题

评论