/*

所谓的「阻塞」，是指当一个数据库会话中的事务，正在锁定其他会话事务想要读取或修改的资源，

造成这些会话发出的请求进入等待的状态。SQL Server 默认会让被阻塞的请求无限期地一直等待，

直到原来的事务释放相关的锁，或直到它超时 (根据 SET LOCK_TIMEOUT )、服务器关闭、

进程被杀死。一般的系统中，偶尔有短时间的阻塞是正常且合理的；但若设计不良的程序，就可能导致长时间的阻塞，

这样就不必要地锁定了资源，而且阻塞了其他会话欲读取或更新的需求。遇到这种情况，可能就需要手工排除阻塞的状态。

*/

--目前会话中的 lock 超时时间

--执行结果默认为 -1，意即欲访问的对象或记录被锁定时，会无限期等待。

SELECT @@LOCK_TIMEOUT

--后面的 3000，其单位为毫秒，亦即会先等待被锁定的对象 3 秒钟

SET LOCK_TIMEOUT 3000

--SET LOCK_TIMEOUT -1

--在 SSMS 中，开两个会话 (查询窗口) 做测试，会话 A 创建会造成阻塞的事务进程，会话 B 去访问被锁定的记录。

--会话 A

BEGIN TRAN;

UPDATE Orders SET EmployeeID=7 WHERE OrderID=10248

--rollback; --故意不提交或回滚

--会话 B

SELECT * FROM Orders WHERE OrderID=10248

/*

消息 1222，级别 16，状态 51，第 3 行

已超过了锁请求超时时段。

*/

--发生阻塞时，透过以下命令，可看出是哪个进程 session id，阻塞了哪几个进程 session id，且期间经过了多少「毫秒 (ms)」

select blocking_session_id, wait_duration_ms, session_id from sys.dm_os_waiting_tasks

--透过以下两个命令，我们还能看到整个数据库的锁定和阻塞详细信息：

select * from sys.dm_tran_locks

exec sp_lock

--另透过 KILL 命令，可直接杀掉造成阻塞的 process，如下：

KILL 53

--欲解决无限期等待的问题，除了前述的 SET LOCK_TIMEOUT 命令外，还有更省事的做法，

--在表名称后面加上 WITH (NOLOCK) 关键字，表示要求  SQL Server，不必去考虑这个表的锁定状态为何，

--因此也可减少「死锁 (dead lock)」发生的机率。但 WITH (NOLOCK) 不适用 INSERT、UPDATE、DELETE。

SELECT * FROM Orders WITH (NOLOCK) WHERE OrderID=10248

--类似的功能，在 SQL 语句前，先设置「事务隔离级别」为可「脏读 (dirty read)」。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM Orders WHERE OrderID=10248

--要知道是否发生了阻塞，当然要看master库的sysprocess表,看看是否有什么进程堵住了别的进程,语句如下:

Select * from master..sysprocesses where blocked > 0

exec sp_lock

--有一个blocked = 51 堵住了很多进程(查看blocked列可见)，为了进一步找出发生阻塞的语句，使用如下的语句

dbcc inputbuffer(51);

--spid = 51 这行, mode = X 表示排它锁, status = WAIT表示正在等待（即被阻塞了），dbid = 14 是数据库的id，objid = 206623779 是被锁的对象id,我们可以通过下列函数得到数据库和表:spid = 51 这行, mode = X 表示排它锁, status = WAIT表示正在等待（即被阻塞了），dbid = 14 是数据库的id，objid = 206623779 是被锁的对象id,我们可以通过下列函数得到数据库和表:

Select db_name(@dbid) -----> book_db

select object_name(@objid) -------> t_book

--即book_db库的t_book表被锁住了,这时候再回投仔细检查 p_Book_content 存储过程

--相关资料在book online可以找到,

--关键字: sp_lock , sysprocesses , dbcc inputbuffer , db_name(), object_name()

本文出自 “畅想天空” 博客，请务必保留此出处http://kinwar.blog.51cto.com/3723399/1410842