判断阻塞发生在某张表上

Blackrose

1. 使用sp_lock进行察看，如果 输出信息 locktype 中包含 -blk 字符，说明当前表 table_id 的 page 上的锁对其他进程造成了阻塞
2. 通过察看 master..syslocks 系统表
例
id          dbid   page        type   spid   class                          fid    context row    loid
----------- ------ ----------- ------ ------ ------------------------------ ------ ------- ------ -----------
  512004855      6           0      3     13 Non Cursor Lock                     0       0      0          26
  512004855      6           0      4     12 Non Cursor Lock                     0       0      0          24
  512004855      6           0      3     10 Non Cursor Lock                     0       0      0          20
  512004855      6         793    261     13 Non Cursor Lock                     0       8      0          26

从spid可知道当前阻塞及被阻塞的进程，并且从type字段可以知道锁的类型，见下表：
1 0x1 排它表锁
2 0x2 共享表锁
3 0x3 排它意图锁
4 0x4 共享意图锁
5 0x5 排它页锁
6 0x6 共享页锁
7 0x7 更新页锁
8 0x8 排它行锁
9 0x9 共享行锁
10 0xA 更新行锁
11 0xB 共享下一键锁
256 0x100 锁在阻塞另一个进程
512 0x200 请求锁

所以你可以通过下面的查询获得阻塞表信息
select b.name as dbname,a.id as table_id,page,spid from syslocks a,sysdatabases b
where b.dbid = a.dbid
and a.type & 256 = 256

你也可以写成一个存储过程，不过一定要在master中执行。

liuyucd

直接用SP_LOCK不就行了吗？

cctv

这样更好：

1. 使用sp_who进行察看，如果输出信息中blk字段不为0，则表明有点问题，问题与blk对应的值有关系。
fid    spid   status       loginame     origname     hostname   blk   dbname     cmd              
------ ------ ------------ ------------ ------------ ---------- ----- ---------- ----------------
......
0      32     recv sleep   c7mon        c7mon        e3500      0     c7db       BULK INSERT
......
0      37     recv sleep   c7mon        c7mon        e3500      0     c7db       AWAITING COMMAND
0      38     lock sleep   c7mon        c7mon        e3500      32    c7db       DROP TABLE      
0      40     recv sleep   c7mon        c7mon        e3500      0     c7db       BULK INSERT      

   或：使用sp_lock进行察看，如果 输出信息 locktype 中包含 -blk 字符，说明当前表 table_id 的 page 上的锁对其他进程造
成了阻塞
fid    spid   locktype                     table_id    page        row    dbname          class                          context                     
------ ------ ---------------------------- ----------- ----------- ------ --------------- ------------------------------ ----------------------------
......
0      31     Ex_intent                    1821965567  0           0      c7db            Non Cursor Lock                Fam dur                     
0      32     Ex_intent-blk              1569440665  0           0      c7db            Non Cursor Lock                Fam dur                     
0      33     Ex_intent                    42483230    0           0      c7db            Non Cursor Lock                Fam dur                     

2. 通过察看 master..syslocks 系统表
例
id dbid page type spid class fid context row loid
----------- ------ ----------- ------ ------ ------------------------------ ------ ------- ------ -----------
512004855 6 0 3 13 Non Cursor Lock 0 0 0 26
512004855 6 0 4 12 Non Cursor Lock 0 0 0 24
512004855 6 0 3 10 Non Cursor Lock 0 0 0 20
512004855 6 793 261 13 Non Cursor Lock 0 8 0 26

从spid可知道当前阻塞及被阻塞的进程，并且从type字段可以知道锁的类型，见下表：
1 0x1 排它表锁
2 0x2 共享表锁
3 0x3 排它意图锁
4 0x4 共享意图锁
5 0x5 排它页锁
6 0x6 共享页锁
7 0x7 更新页锁
8 0x8 排它行锁
9 0x9 共享行锁
10 0xA 更新行锁
11 0xB 共享下一键锁
256 0x100 锁在阻塞另一个进程
512 0x200 请求锁

所以你可以通过下面的查询获得阻塞表信息:
  
   select b.name as tablename,page,spid
   from master..syslocks a , sysobjects b
   where b.id=a.id and spid=blk_value （目前来看是32）
如：
tablename                      page        spid   
------------------------------ ----------- ------
t20030228                    0           32     

也就是说32 BULK INSERT 和 38 DROP TABLE都是在处理  t20030228 表，导致s锁。

流浪着的鱼

好东西,谢谢,俺要收藏,^-^

zhangyinhz

问题描述：
     数据库 sql server7.0 前台程序 powerbulider7.03。每当用户数一多，就会出现数据库阻塞现象，其中有些用户是用拨号上网的方式连接数据库，平均每天都会出现一到两次，一般需要手工把正在阻塞的spid kill掉才行，但是如果拨号用户不登陆的话，阻塞就不会出现，正在阻塞的spid 所做的事务有时为一条select 语句，有时为begin tran，有时为空。如果把数据库迁移到sql server2000，阻塞情况变的更加严重，系统根本无法运行。
         本人已经被这个问题困扰很久了，一直无法解决，恳请碰到
过的仁兄帮忙。

zhangyinhz

问题描述：
     数据库 sql server7.0 前台程序 powerbulider7.03。每当用户数一多，就会出现数据库阻塞现象，其中有些用户是用拨号上网的方式连接数据库，平均每天都会出现一到两次，一般需要手工把正在阻塞的spid kill掉才行，但是如果拨号用户不登陆的话，阻塞就不会出现，正在阻塞的spid 所做的事务有时为一条select 语句，有时为begin tran，有时为空。如果把数据库迁移到sql server2000，阻塞情况变的更加严重，系统根本无法运行。
         本人已经被这个问题困扰很久了，一直无法解决，恳请碰到
过的仁兄帮忙。

fuyongkun

执行下面这个过程，执行sp_block就可以看到了
use sybsystemprocs
go

if exists (select *
                   from   sysobjects
                   where  type = 'P'
                   and    name = "sp_block"
begin
    drop procedure sp_block
end
go

create procedure sp_block
@dbname varchar(30) = "%"
as

/*
* Name:   sp_block
* Date:   Jan 10 1994
* Author: Mike Chachich (mdchachi@vela.acs.oakland.edu)
*
* Modification History:
* Date        Who            Description
* ----------  ---             -------------------------------------------
* 01-10-1994  Mike Chachich  Initial Version
* 08-02-1995  Teresa Larson  modified ?? (larson@sled.gsfc.nasa.gov)
* 08-01-1995  TLarson               per suggestion from Sharkey, Doug"
*  .   Added isnull(o bject_name(id,syslock.dbid)
*  so user no longer have to be using the database to get the proper object
*  name.
* 09-01-1999  TBoss              modified; had to fully qualify the calls to
*  the "id" field upon installing in 11.5 for the first time.
*** *************
* There is no warranty expressed or implied and the use of this code
* is at your own risk.  Specifically, claims that this code causes SQL
* Server to generate obscene messages will be vehemently denied.
*
* This procedure is very simi lar to sp_lock except that it provides more
* information and prints out the name of locked tables that are within the
* current database.  It also lists any blocked processes and who they are
* blocked by.  The use of multiple 'select' and 'print' st atements was
* avoided in order to use this in the current version of PowerBuilder's DBA
* Painter (in PB, print is treated like raiserror and each select is treated
* as a separate result set)
*
* Note:  this procedure, as is, is not compatible with
*        versions of SQL Server older than 4.2
*
* Usage : sp_block [database-name]
*
*/

SELECT
   /* display all locks */
   syslocks.spid,
   pid=sysprocesses.hostprocess,
   userid=substring(sysusers.name,1,7),
   dbname=substring(db_name(syslocks.dbid), 1, 15),
   /* "table / blcked_by"=convert(varchar(17),isnull(object_name(id, syslocks.dbid),convert(varchar(30),id))), */
   "table / blcked_by"=convert(varchar(17),isnull(object_name(syslocks.id, syslocks.dbid),convert(varchar(30),syslocks.id))),
   lock_type=convert(varchar(14),spt_values.name),
   page=convert(varchar(10),syslocks.page),
   status=substring(sysprocesses.status, 1, 10),
   program=substring(program_name,1,15),
   command=substring(cmd,1,10)
   FROM master.dbo.syslocks  syslocks,
        master.dbo.spt_values spt_values,
        master.dbo.sysprocesses sysprocesses,
        master.dbo.syslogins sysusers
   WHERE syslocks.type = spt_values.number
        and spt_values.type = 'L'
        and syslocks.spid = sysprocesses.spid
        and sysusers.suid = sysprocesses.suid
        and db_name(syslocks.dbid) like @dbname
UNION
SELECT
    /* display all blocked processes */
    sp1.spid,
    pid=sp1.hostprocess,
    userid=substring(suser_name(sp1.suid),1,7),
    dbname= substring(db_name(sp1.dbid),1,15),
    blk_user=convert(char(3),sp1.blocked) + substring(suser_name(sp2.suid),1,14),
    locktype=null,
    page=null,
    status=substring(sp1.status, 1, 10),
    program=substring(sp1.program_name,1,15),
    command=substring(sp1.cmd,1,10)
FROM master.dbo.sysprocesses sp1, master.dbo.sysprocesses sp2
WHERE sp1.blocked = sp2.spid
go

grant execute on sp_block to public
go