sp;更新行锁
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锁。
流浪着的鱼 回复于：2003-03-08 13:05:31
好东西,谢谢,俺要收藏,^-^
zhangyinhz 回复于：2003-04-11 11:34:12
问题描述：
数据库 sql server7.0 前台程序 powerbulider7.03。每当用户数一多，就会出现数据库阻塞现象，其中有些用户是用拨号上网的方式连接数据库，平均每天都会出现一到两次，一般需要手工把正在阻塞的spid kill掉才行，但是如果拨号用户不登陆的话，阻塞就不会出现，正在阻塞的spid 所做的事务有时为一条select 语句，有时为begin tran，有时为空。如果把数据库迁移到sql server2000，阻塞情况变的更加严重，系统根本无法运行。
本人已经被这个问题困扰很久了，一直无法解决，恳请碰到
过的仁兄帮忙。
zhangyinhz 回复于：2003-04-11 11:35:45
问题描述：
数据库 sql server7.0 前台程序 powerbulider7.03。每当用户数一多，就会出现数据库阻塞现象，其中有些用户是用拨号上网的方式连接数据库，平均每天都会出现一到两次，一般需要手工把正在阻塞的spid kill掉才行，但是如果拨号用户不登陆的话，阻塞就不会出现，正在阻塞的spid 所做的事务有时为一条select 语句，有时为begin tran，有时为空。如果把数据库迁移到sql server2000，阻塞情况变的更加严重，系统根本无法运行。
本人已经被这个问题困扰很久了，一直无法解决，恳请碰到
过的仁兄帮忙。
fuyongkun 回复于：2004-01-14 10:11:56
执行下面这个过程，执行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
上一页 [1] [2] |
