SQL SERVER 锁定的实例
---实例DB:AdventureWorks2014
--- 创建view DBLocks
USE [AdventureWorks2014] GO /****** Object: View [dbo].[DBlocks] Script Date: 8/17/2016 6:38:16 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[DBlocks] AS SELECT request_session_id as spid, db_name(resource_database_id) as dbname, CASE WHEN resource_type = 'OBJECT' THEN object_name(resource_associated_entity_id) WHEN resource_associated_entity_id = 0 THEN 'n/a' ELSE object_name(p.object_id) END as entity_name, index_id, resource_type as resource, resource_description as description, request_mode as mode, request_status as status FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p ON p.partition_id = t.resource_associated_entity_id WHERE resource_database_id = db_id(); GO
实例1： Read Commit(SQL SERVER默认隔离级别) 隔离级别下的SELECT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRAN SELECT * FROM [Production].[Product] WHERE Name ='Reflector'; SELECT * FROM [dbo].[DBlocks] WHERE spid= @@SPID COMMIT TRAN
DBLocks 的输出结果
| spid | dbname | entity_name | index_id | resource | mode | status |
| 58 | AdventureWorks2014 | sysrowsets | NULL | OBJECT | Sch-S | GRANT |
| 58 | AdventureWorks2014 | n/a | NULL | DATABASE | S | GRANT |
| 58 | AdventureWorks2014 | DBlocks | NULL | OBJECT | IS | GRANT |
Product 表的数据没有LOCK，所以批处理执行select 操作获得了共享锁。SQL SEVER一旦读完数据之后立即释放共享锁。因此执行DBLoks试图时，上面提到的共享锁不存在了。
实例2: 可重复读隔离级别下的SELECT
SQL 批处理
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN TRAN SELECT * FROM [Production].[Product] WHERE Name ='Reflector'; SELECT * FROM [dbo].[DBlocks] WHERE spid= @@SPID COMMIT TRAN
DBLocks 的输出结果
| spid | dbname | entity_name | index_id | resource | description | mode | status |
| 58 | AdventureWorks2014 | sysrowsets | NULL | OBJECT | Sch-S | GRANT | |
| 58 | AdventureWorks2014 | n/a | NULL | DATABASE | S | GRANT | |
| 58 | AdventureWorks2014 | Product | 1 | PAGE | 0.590972222 | IS | GRANT |
| 58 | AdventureWorks2014 | DBlocks | NULL | OBJECT | IS | GRANT | |
| 58 | AdventureWorks2014 | Product | 1 | KEY | (b031e2166063) | S | GRANT |
| 58 | AdventureWorks2014 | Product | 3 | KEY | (489415cc61a4) | S | GRANT |
| 58 | AdventureWorks2014 | Product | NULL | OBJECT | IS | GRANT | |
| 58 | AdventureWorks2014 | Product | 3 | PAGE | 3.626388889 | IS | GRANT |
由于Product表上有聚集索引，数据行就是页级别的全部索引行，返回两个数据行上的锁是键锁，用来寻找相关数据。在Product表上存在Name上的非聚集索引，可以通过Index_ID字段的值来区分聚集所以和非聚集索引，Index_ID=1表示数据行和聚集索引，Index_ID=3表示非聚集索引。由于事务的隔离级别是可重复读，共享锁要等到事务结束为止。NOTE:索引行拥有共享锁（S）而数据分页，索引分页以及表本身拥有意向共享锁（IS）。
实例3：可串行隔离级别下的SELECT
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRAN SELECT * FROM [Production].[Product] WHERE Name LIKE 'Reflect%'; SELECT * FROM [dbo].[DBlocks] WHERE spid= @@SPID COMMIT TRAN
DBLocks的输出结果
| spid | dbname | entity_name | index_id | resource | description | mode | status |
| 58 | AdventureWorks2014 | sysrowsets | NULL | OBJECT | Sch-S | GRANT | |
| 58 | AdventureWorks2014 | n/a | NULL | DATABASE | S | GRANT | |
| 58 | AdventureWorks2014 | Product | 1 | PAGE | 0.590972222 | IS | GRANT |
| 58 | AdventureWorks2014 | n/a | NULL | METADATA | schema_id = 7 | Sch-S | GRANT |
| 58 | AdventureWorks2014 | DBlocks | NULL | OBJECT | IS | GRANT | |
| 58 | AdventureWorks2014 | Product | 1 | KEY | (b031e2166063) | S | GRANT |
| 58 | AdventureWorks2014 | Product | 3 | KEY | (489415cc61a4) | RangeS-S | GRANT |
| 58 | AdventureWorks2014 | Product | NULL | OBJECT | IS | GRANT | |
| 58 | AdventureWorks2014 | Product | 3 | PAGE | 3.626388889 | IS | GRANT |
| 58 | AdventureWorks2014 | Product | 3 | KEY | (53bdd09497c4) | RangeS-S | GRANT |
| 58 | AdventureWorks2014 | NULL | NULL | OBJECT | IS | GRANT |
