锁分区提升并发,以及锁等待实例

码字介绍

http://msdn.microsoft.com/en-us/library/ms187504(v=SQL.100).aspx

锁分区技术对提高并发有一定帮助,但对对象操作又有了新的定义,需要DBA&开发人员在设计,维护应用中做更细致的考量.

 

锁分区提升并发,以及锁等待实例

 

--测试数据库tempdb 

--创建测试表 create table testlkp ( id int )

--step1 spid 64中执行事务insert 

BEGIN TRAN ttt 

INSERT INTO testlkp VALUES(1)

说明:创建测试环境,第一步开启事务执行insert 成功

 

---随意不相关session spid70中查看操作的数据库上下文中查看锁的情况

select request_session_id,* from sys.dm_tran_locks where resource_database_id=DB_ID()

order by request_mode

说明:观察锁情况发现锁中分区号15有对象级IX锁

 

 

--step 2新开启session spid 67 执行sql 被阻塞

ALTER TABLE testlkp ALTER COLUMN id smallint

说明:由于无法获得整体对象SCH-M锁,等待

 

---随意不相关session spid70中查看操作的数据库上下文中查看锁的情况

select request_session_id,* from sys.dm_tran_locks where resource_database_id=DB_ID()

order by request_mode

说明:其他锁分区(0-14)均获得了SCH-M锁,但由于15由于IX锁无法获得

 

--Step3 新开启session 执行事务 插入数据 spid 71 被阻塞

BEGIN TRAN ttt
INSERT INTO testlkp VALUES(2)     ---------blocked

说明:开启新session查询被阻塞

---随意不相关session spid70中查看操作的数据库上下文中查看锁的情况

select request_session_id,* from sys.dm_tran_locks where resource_database_id=DB_ID()

order by request_mode

说明:insert需要申请表的IX锁,但正好落在15这个分区上,所以等待

--Step4 新开启session 执行事务 插入数据 spid 65 执行成功!

BEGIN TRAN ttt
INSERT INTO testlkp VALUES(3)

说明:开启新的session insert 插入成功！

---随意不相关session spid70中查看操作的数据库上下文中查看锁的情况

select request_session_id,* from sys.dm_tran_locks where resource_database_id=DB_ID()

order by request_mode

说明:新的session插入操作时在其他的锁分区上(16)获得IX锁所以执行成功！

 

思考:锁分区技术的出现提高了并发,但在做表级操作如果需所有分区锁如SCH-M将有可能对此类操作造成更"长"等待

如索引维护操作（需SCH-M锁）,一旦有长时间事务甚至孤立事务占据着分区级的某个锁,则可能一直陷入等待状态造成阻塞从而影响全局.

在诸如此类操作或应用设计中,大家需要全面仔细考量.