锁定和阻塞

　　锁是事务获取的一种控制资源，用于保护数据资源，防止其它事务对数据进行冲突的或不兼容的访问。我们目前只要学习两种基本的锁模式就可以，它们分别是共享锁和排他锁。

共享锁主要是在读操作时使用，读操作一旦完成，数据库就会立即释放资源上的共享锁，而且多个事务可以同时持有同一数据资源上的共享锁。而排他锁主要是在修改数据时使用，一旦授予，事务将一直持有排他锁，直到事务完成(提交或回滚)。对于同一数据资源，如果有其他事务已经获取了该资源的任何类型的锁，就不能再获取该资源的排他锁，如果有其他事务已经获得了该资源的排他锁，就不能再获取该资源的任何类型的锁。

　　下面用一个示例来对其进行演示。首先开启一个会话 Connection 1，打开一个事务，对Production.Products表的一行进行更新，为产品2的当前单价19.00增加1.00，为了修改这一行，会话必须先获得一个排他锁。如下代码。

USE TSQLFundamentals2008;
GO

-- Connection 1，修改數據獲取排他鎖
BEGIN TRAN --開啟事務，但沒有commit，所以該會話會一直持有排他鎖

UPDATE Production.Products SET unitprice+=1.00
WHERE productid=2;

因为这个事务没有完成(没有提交或回滚)，所以该会话会一直持有排他锁。此时再开启一个会话Connection 2，试图去查询这一行数据，为了读操作这个会话需要一个共享锁，但是这一行已经被前面那个会话的排他锁锁定，而且共享锁和排他锁是不兼容的，所以会话Connection 2会被阻塞，进入等待状态。如下代码。

USE TSQLFundamentals2008;
GO

-- Connection 2，查詢數據獲取共享鎖
-- 因為這一行已經被會話Connection 1所持有的排他鎖鎖定，而排他鎖和共享鎖是不兼容的，所以該會話會被阻塞
SELECT * FROM Production.Products
WHERE productid=2;

如果发生了这样的锁定和阻塞，我们就会想办法去分析和排除这种阻塞，为此，我们可以通过一些动态管理对象来得到关于锁的详细信息。如下代码。

USE TSQLFundamentals2008;
GO

-- Connection 3
-- 1，通過動態管理視圖sys.dm_tran_locks查看該數據庫阻塞鏈中進程的信息
SELECT request_session_id AS spid, --會話ID
        resource_type AS restype, -- 資源類型
        resource_database_id AS dbid, --數據庫ID
        DB_NAME(resource_database_id) AS dbname, --數據庫名稱
        resource_description AS res,--資源描述
        resource_associated_entity_id AS resid,-- 資源相關聯實體的ID
        request_mode AS mode,--鎖模式
        request_status AS STATUS --鎖狀態
FROM sys.dm_tran_locks;

-- 2,通過動態管理視圖sys.dm_exec_connections查看阻塞鏈中進程關聯的聯接信息
-- 比如联接建立的时间，最后一次发生读操作和写操作的时间以及最后执行的SQL代码
SELECT session_id AS spid,
        connect_time AS connecttime,
        last_read,
        last_write,
        text
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
WHERE session_id IN(52,54);

-- 3,通过动态管理视图sys.dm_exec_sessions找到更多有用的信息
-- 包括登录的用户名，主机名和登录时间，最后请求开始时间和最后请求结束时间
SELECT session_id AS spid,
        login_time,
        host_name,
        program_name,
        nt_user_name,
        last_request_start_time,
        last_request_end_time
FROM sys.dm_exec_sessions
WHERE session_id IN(52,54);

-- 4,可通過動態管理視圖sys.dm_exec_requests查詢到導致阻塞的進程的詳細信息
SELECT session_id AS spid,
        blocking_session_id,
        command,
        sql_handle,
        database_id,
        wait_type,
        wait_time,
        wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id>0;

-- 利用KILL<spid>命令終止導致阻塞的進程
-- 該操作會導致Connection 1中的事務回滾
KILL 51;