SQL Server数据库表锁定原理以及如何解除表的锁定--示例演示

有几个朋友留言建议结合例子来演示一下, 上篇已经说过锁的几种类型, 可以利用系统动态视图sys.dm_tran_locks查看到,重要的栏位如下:

一: 下面以AdventureWorks2008为示例数据库做简要的说明,过滤掉一般的数据库的共享锁, 作为示例必须要看到锁, 所以用WITH(HOLDLOCK)来保持锁.

1. Shared locks (S) 共享锁

USE AdventureWorks2008

BEGIN TRAN
select * from Sales.SalesOrderHeader WITH(HOLDLOCK)
where SalesOrderID='43662' 

    
SELECT resource_type, request_mode, resource_description,request_session_id, DB_NAME(resource_database_id)as resource_database
FROM   sys.dm_tran_locks
WHERE  resource_type <> 'DATABASE'

--ROLLBACK TRAN

在事务回滚之前, 查看锁的类型:

其他session对Table只读, 不能更新, 在开一个新的session测试:

select * from Sales.SalesOrderHeader  where SalesOrderID='43662'

go
update Sales.SalesOrderHeader set OrderDate=GETDATE() where SalesOrderID='43662'

select可以正常执行, update语句一直处于等待状态, 等待上面的session释放锁.

2. Update locks (U): 更新锁是共享锁和独占锁的组合.用UPDLOCK保持更新锁

USE AdventureWorks2008

BEGIN TRAN
select * from Sales.SalesOrderHeader WITH(UPDLOCK)
where SalesOrderID='43662' 
    
SELECT resource_type, request_mode, resource_description,request_session_id,DB_NAME(resource_database_id)as resource_database
FROM   sys.dm_tran_locks
WHERE  resource_type <> 'DATABASE'


ROLLBACK TRAN

查看到锁的信息:

3.Exclusive locks (X): 独占锁是为了锁定数据被一个session修改的数据, 而不能够被另外的session修改. 只能指定NOLOCK来读取.

USE AdventureWorks2008

BEGIN TRAN

update Sales.SalesOrderHeader set ShipDate=GETDATE() where SalesOrderID='43662'
    
SELECT resource_type, request_mode, resource_description,request_session_id,DB_NAME(resource_database_id)as resource_database--,*
FROM   sys.dm_tran_locks
WHERE  resource_type <> 'DATABASE'

ROLLBACK TRAN

查看锁:

4.Intent locks (I): 意向锁用于建立锁的层次结构. 意向锁包含三种类型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。

数据库引擎使用意向锁来保护共享锁（S 锁）或排他锁（X 锁）放置在锁层次结构的底层资源上。 意向锁之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。

意向锁有两种用途：

• 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。
• 提高数据库引擎在较高的粒度级别检测锁冲突的效率。

5. Schema locks (Sch): 架构锁

• Schema stability lock(Sch-S): 保持架构稳定性,用在生成执行计划时,不会阻止对数据的访问.
• Schema modification lock (Sch-M):用在DDL操作时.当架构正在被改变时, 阻止对对象数据的访问.

USE AdventureWorks2008

BEGIN TRAN
CREATE TABLE MyTable (ID INT, NAME VARCHAR(20),COUNTRY VARCHAR(15))

SELECT resource_type, request_mode, resource_description
FROM   sys.dm_tran_locks
WHERE  resource_type <> 'DATABASE' order by request_mode

ROLLBACK TRAN

6. Bulk Update locks (BU)

数据库引擎在将数据大容量复制到表中时使用了大容量更新 (BU) 锁, 并指定了 TABLOCK 提示或使用 sp_tableoption 设置了 table lock on bulk load 表选项. 大容量更新锁（BU 锁）允许多个线程将数据并发地大容量加载到同一表, 同时防止其他不进行大容量加载数据的进程访问该表.

7. Key - Range locks

在使用可序列化事务隔离级别时, 对于 Transact-SQL 语句读取的记录集, 键范围锁可以隐式保护该记录集中包含的行范围. 键范围锁可防止幻读. 通过保护行之间键的范围, 它还防止对事务访问的记录集进行幻像插入或删除.

二: 死锁与死锁解除

1. 死锁

使用或管理数据库都不可避免的涉及到死锁. 一旦发生死锁, 数据相互等待对方资源的释放,会阻止对数据的访问, 严重会造成DB挂掉. 当资源被锁定, 无法被访问时, 可以终止访问DB的那个session来达到解锁的目的(即 Kill掉造成锁的那个进程).

在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。 例如：

• 事务 A 获取了行 1 的共享锁。
• 事务 B 获取了行 2 的共享锁。
• 现在，事务 A 请求行 2 的排他锁，但在事务 B 完成并释放其对行 2 持有的共享锁之前被阻塞。
• 现在，事务 B 请求行 1 的排他锁，但在事务 A 完成并释放其对行 1 持有的共享锁之前被阻塞。

事务 B 完成之后事务 A 才能完成，但是事务 B 由事务 A 阻塞。该条件也称为循环依赖关系： 事务 A 依赖于事务 B，事务 B 通过对事务 A 的依赖关系关闭循环。

除非某个外部进程断开死锁，否则死锁中的两个事务都将无限期等待下去。 Microsoft SQL Server 数据库引擎死锁监视器定期检查陷入死锁的任务。 如果监视器检测到循环依赖关系，将选择其中一个任务作为牺牲品，然后终止其事务并提示错误。 这样，其他任务就可以完成其事务。 对于事务以错误终止的应用程序，它还可以重试该事务，但通常要等到与它一起陷入死锁的其他事务完成后执行。

2. 死锁检测

2.1 SQL Server 数据库引擎自动检测 SQL Server 中的死锁循环。数据库引擎选择一个会话作为死锁牺牲品，然后终止当前事务（出现错误）来打断死锁。

2.2 查看DMV: sys.dm_tran_locks

2.3 SQL Server Profiler能够直观的显示死锁的图形事件.

三: 锁兼容性

锁兼容性控制多个事务能否同时获取同一资源上的锁。 如果资源已被另一事务锁定，则仅当请求锁的模式与现有锁的模式相兼容时，才会授予新的锁请求。 如果请求锁的模式与现有锁的模式不兼容，则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。 例如，没有与排他锁兼容的锁模式。 如果具有排他锁（X 锁），则在释放排他锁（X 锁）之前，其他事务均无法获取该资源的任何类型（共享、更新或排他）的锁。 另一种情况是，如果共享锁（S 锁）已应用到资源，则即使第一个事务尚未完成，其他事务也可以获取该项的共享锁或更新锁（U 锁）。 但是，在释放共享锁之前，其他事务无法获取排他锁。

四: 总结

锁的原理比较抽象,对用户来说是透明的,不用过多的关注. 应用程序一般不直接请求锁. 锁由数据库引擎的一个部件（称为“锁管理器”）在内部管理. 当数据库引擎实例处理Transact-SQL 语句时, 数据库引擎查询处理器会决定将要访问哪些资源. 查询处理器根据访问类型和事务隔离级别设置来确定保护每一资源所需的锁的类型. 然后, 查询处理器将向锁管理器请求适当的锁. 如果与其他事务所持有的锁不会发生冲突, 锁管理器将授予该锁.