34,837
社区成员
发帖
与我相关
我的任务
分享sql 如何判断某个表是否被锁
请问大侠： 如何判断某个表是否被锁？
------------问题描述---------------
现在我有一个事务，执行下面操作：
begin tran
step 1： delete table1(会根据条件删掉几万数据)
step 2: insert table1（先从其他表选数据，然后插入到table1）
step 3: update table1(对某些数据进行初始化)
step 4: update table2(使用table1的相应数据对table2进行某些更新)
commit tran
这个事务在差点的机器上可能需要2 到3 分钟才可以执行完毕。如果让sql自己判断，sql好似只是在commit的时候才判断表是不是被锁定。 这时候可能就是用户等了3分钟后，程序给个提示： 资源锁定，请稍候再试。 我希望在事务开始前，主动判断用到的表table1是否被锁定，锁定就不执行事务，直接给出锁定提示。
请指点如何实现呢 ？ 谢谢！！
------------问题描述---------------
现在我有一个事务，执行下面操作：
begin tran
step 1： delete table1(会根据条件删掉几万数据)
step 2: insert table1（先从其他表选数据，然后插入到table1）
step 3: update table1(对某些数据进行初始化)
step 4: update table2(使用table1的相应数据对table2进行某些更新)
commit tran
这个事务在差点的机器上可能需要2 到3 分钟才可以执行完毕。如果让sql自己判断，sql好似只是在commit的时候才判断表是不是被锁定。 这时候可能就是用户等了3分钟后，程序给个提示： 资源锁定，请稍候再试。 我希望在事务开始前，主动判断用到的表table1是否被锁定，锁定就不执行事务，直接给出锁定提示。
请指点如何实现呢 ？ 谢谢！！
...全文
请发表友善的回复…
发表回复
hansuzhi 2009-09-18
- 打赏
- 举报
所以我考虑这个工作做成批处理比较好，在夜深人静的时候自己慢慢的按顺序执行得了。。。
hansuzhi 2009-09-18
- 打赏
- 举报
谢谢 楼上兄弟们的指点
我研究了下客户的逻辑：他们的要求应该是如果执行的话step 1到4都完成， 否则都不执行。 严格保证不出现执行了一半停掉的情况。
我研究了下客户的逻辑：他们的要求应该是如果执行的话step 1到4都完成， 否则都不执行。 严格保证不出现执行了一半停掉的情况。
xman_78tom 2009-09-18
- 打赏
- 举报
[Quote=引用 22 楼 hansuzhi 的回复:]
现在突然很郁闷了。。。
如果存储过程执行的业务很复杂，费时很长的话，貌似只能一次一次的执行了。 只要并发调用，哪怕你输入的条件不一样，如果存储过程执行的时候把对表的行锁升级到表锁，都必然挂掉啊
我们这边在做一个维护项目，客户在一个存储过程（有900多行，关键这个存储过程的调用是在web页面中。。。）里进行了很多操作：
首先，从一个oracle link-server里取出一些数据（1万条左右）插入到系统里的一个表里，譬如table1(table1现在有将近100万条记录，每行71列，god,我也不明白为什么一个表里放这么多字段)
其次，根据条件对table1里刚导入的那批数据的某些列进行初始化，
根据条件更改某些字段的格式，
根据条件更改某些字段，譬如a,b,c的数值，
根据条件更改某些字段，譬如e,d,f的数值，
...执行数次
再次，从另外一个表table2里选出一堆数据,使用游标更新table1里导入的那堆数据
[/Quote]
我的建议，与其将这些数据都在 table1 中操作，不如建一个临时表，在临时表中操作完后，再将符合要求的数据插入 table1。
现在突然很郁闷了。。。
如果存储过程执行的业务很复杂，费时很长的话，貌似只能一次一次的执行了。 只要并发调用，哪怕你输入的条件不一样，如果存储过程执行的时候把对表的行锁升级到表锁，都必然挂掉啊
我们这边在做一个维护项目，客户在一个存储过程（有900多行，关键这个存储过程的调用是在web页面中。。。）里进行了很多操作：
首先，从一个oracle link-server里取出一些数据（1万条左右）插入到系统里的一个表里，譬如table1(table1现在有将近100万条记录，每行71列，god,我也不明白为什么一个表里放这么多字段)
其次，根据条件对table1里刚导入的那批数据的某些列进行初始化，
根据条件更改某些字段的格式，
根据条件更改某些字段，譬如a,b,c的数值，
根据条件更改某些字段，譬如e,d,f的数值，
...执行数次
再次，从另外一个表table2里选出一堆数据,使用游标更新table1里导入的那堆数据
[/Quote]
我的建议，与其将这些数据都在 table1 中操作，不如建一个临时表，在临时表中操作完后，再将符合要求的数据插入 table1。
zhengzeng 2009-09-18
- 打赏
- 举报
step 1： delete table1(会根据条件删掉几万数据)
step 2: insert table1（先从其他表选数据，然后插入到table1）
step 3: update table1(对某些数据进行初始化)
step 4: update table2(使用table1的相应数据对table2进行某些更新)
1. step 1 到 step 4 做成一个事务，资源占用太大。可以考虑分成多个事务。
2. 优化sql，看一下哪一步 占用时间最长。
step 2: insert table1（先从其他表选数据，然后插入到table1）
step 3: update table1(对某些数据进行初始化)
step 4: update table2(使用table1的相应数据对table2进行某些更新)
1. step 1 到 step 4 做成一个事务，资源占用太大。可以考虑分成多个事务。
2. 优化sql，看一下哪一步 占用时间最长。
hansuzhi 2009-09-17
- 打赏
- 举报
这2个存储过程我看了 不太适合啊(而且sp_lock需要管理员权限才可以调用)
我这里想判断的表被锁，不是指的死锁。 就是一个事务在执行过程中加的正常锁。 譬如holdlock
我判断如果某个表被锁，譬如table1， 表示事务已经在运行了，这样我就可以告诉用户需要等那个事务执行完毕，才可以再调用该事务。
不知道表达的是否清楚。 请指点。
我这里想判断的表被锁，不是指的死锁。 就是一个事务在执行过程中加的正常锁。 譬如holdlock
我判断如果某个表被锁，譬如table1， 表示事务已经在运行了，这样我就可以告诉用户需要等那个事务执行完毕，才可以再调用该事务。
不知道表达的是否清楚。 请指点。
soft_wsx 2009-09-17
- 打赏
- 举报
[Quote=引用 5 楼 hansuzhi 的回复:]
问题补充：
sp_lock,sp_who 这2个都不可以应该
因为客户让在程序中使用的数据库用户名/密码 没有管理员权限，也不会给管理员权限。所以需要访问系统表的方法应该都用不了
[/Quote]谁都可以看吗？客户也看不懂呀！
问题补充：
sp_lock,sp_who 这2个都不可以应该
因为客户让在程序中使用的数据库用户名/密码 没有管理员权限，也不会给管理员权限。所以需要访问系统表的方法应该都用不了
[/Quote]谁都可以看吗？客户也看不懂呀！
hansuzhi 2009-09-17
- 打赏
- 举报
问题补充：
sp_lock,sp_who 这2个都不可以应该
因为客户让在程序中使用的数据库用户名/密码 没有管理员权限，也不会给管理员权限。所以需要访问系统表的方法应该都用不了
sp_lock,sp_who 这2个都不可以应该
因为客户让在程序中使用的数据库用户名/密码 没有管理员权限，也不会给管理员权限。所以需要访问系统表的方法应该都用不了
--小F-- 2009-09-17
- 打赏
- 举报
sp_who,查看所有用户及sp_lock,查看所有当前打开的资源,应该会发出Lock是哪个进程,
用 kill 进程号 删除进程开锁,
用 kill 进程号 删除进程开锁,
wujinjian2008n 2009-09-17
- 打赏
- 举报
[Quote=引用 1 楼 soft_wsx 的回复:]
exec sp_lock
[/Quote]
这个怎么看
exec sp_lock
[/Quote]
这个怎么看
wujinjian2008n 2009-09-17
- 打赏
- 举报
我也想知道
soft_wsx 2009-09-17
- 打赏
- 举报
exec sp_lock
hansuzhi 2009-09-17
- 打赏
- 举报
[Quote=引用 22 楼 hansuzhi 的回复:]
现在突然很郁闷了。。。
如果存储过程执行的业务很复杂，费时很长的话，貌似只能一次一次的执行了。 只要并发调用，哪怕你输入的条件不一样，如果存储过程执行的时候把对表的行锁升级到表锁，都必然挂掉啊
我们这边在做一个维护项目，客户在一个存储过程（有900多行，关键这个存储过程的调用是在web页面中。。。）里进行了很多操作：
首先，从一个oracle link-server里取出一些数据（1万条左右）插入到系统里的一个表里，譬如table1(table1现在有将近100万条记录，每行71列，god,我也不明白为什么一个表里放这么多字段)
其次，根据条件对table1里刚导入的那批数据的某些列进行初始化，
根据条件更改某些字段的格式，
根据条件更改某些字段，譬如a,b,c的数值，
根据条件更改某些字段，譬如e,d,f的数值，
...执行数次
再次，从另外一个表table2里选出一堆数据,使用游标更新table1里导入的那堆数据
[/Quote]
不知道各位大侠有什么建议没？
现在突然很郁闷了。。。
如果存储过程执行的业务很复杂，费时很长的话，貌似只能一次一次的执行了。 只要并发调用，哪怕你输入的条件不一样，如果存储过程执行的时候把对表的行锁升级到表锁，都必然挂掉啊
我们这边在做一个维护项目，客户在一个存储过程（有900多行，关键这个存储过程的调用是在web页面中。。。）里进行了很多操作：
首先，从一个oracle link-server里取出一些数据（1万条左右）插入到系统里的一个表里，譬如table1(table1现在有将近100万条记录，每行71列，god,我也不明白为什么一个表里放这么多字段)
其次，根据条件对table1里刚导入的那批数据的某些列进行初始化，
根据条件更改某些字段的格式，
根据条件更改某些字段，譬如a,b,c的数值，
根据条件更改某些字段，譬如e,d,f的数值，
...执行数次
再次，从另外一个表table2里选出一堆数据,使用游标更新table1里导入的那堆数据
[/Quote]
不知道各位大侠有什么建议没？
hansuzhi 2009-09-17
- 打赏
- 举报
现在突然很郁闷了。。。
如果存储过程执行的业务很复杂，费时很长的话，貌似只能一次一次的执行了。 只要并发调用，哪怕你输入的条件不一样，如果存储过程执行的时候把对表的行锁升级到表锁，都必然挂掉啊
我们这边在做一个维护项目，客户在一个存储过程（有900多行，关键这个存储过程的调用是在web页面中。。。）里进行了很多操作：
首先，从一个oracle link-server里取出一些数据（1万条左右）插入到系统里的一个表里，譬如table1(table1现在有将近100万条记录，每行71列，god,我也不明白为什么一个表里放这么多字段)
其次，根据条件对table1里刚导入的那批数据的某些列进行初始化，
根据条件更改某些字段的格式，
根据条件更改某些字段，譬如a,b,c的数值，
根据条件更改某些字段，譬如e,d,f的数值，
...执行数次
再次，从另外一个表table2里选出一堆数据,使用游标更新table1里导入的那堆数据
如果存储过程执行的业务很复杂，费时很长的话，貌似只能一次一次的执行了。 只要并发调用，哪怕你输入的条件不一样，如果存储过程执行的时候把对表的行锁升级到表锁，都必然挂掉啊
我们这边在做一个维护项目，客户在一个存储过程（有900多行，关键这个存储过程的调用是在web页面中。。。）里进行了很多操作：
首先，从一个oracle link-server里取出一些数据（1万条左右）插入到系统里的一个表里，譬如table1(table1现在有将近100万条记录，每行71列，god,我也不明白为什么一个表里放这么多字段)
其次，根据条件对table1里刚导入的那批数据的某些列进行初始化，
根据条件更改某些字段的格式，
根据条件更改某些字段，譬如a,b,c的数值，
根据条件更改某些字段，譬如e,d,f的数值，
...执行数次
再次，从另外一个表table2里选出一堆数据,使用游标更新table1里导入的那堆数据
hansuzhi 2009-09-17
- 打赏
- 举报
好的 谢谢啦
xman_78tom 2009-09-17
- 打赏
- 举报
上面说的明白。数据库引擎不是单凭表上行锁的数目决定是否升级为表锁；还要根据系统中所有锁占用的内存资源决定是否将表上的行锁升级为表锁。
也就是说，一表上有 2000 行锁，系统并不一定将其升级为表锁。还要检查当前系统中所有锁占用的内存池的大小超过了 40%。
也就是说，一表上有 2000 行锁，系统并不一定将其升级为表锁。还要检查当前系统中所有锁占用的内存池的大小超过了 40%。
hansuzhi 2009-09-17
- 打赏
- 举报
[Quote=引用 17 楼 xman_78tom 的回复:]
在 SQL SERVER 2K 中，如果一个事务中锁的数目超过了 1250，或者一个表或索引上的锁的数目超过了 765，系统便会检查整个系统内锁占用的内存池的大小，如果锁占用的内存池的大小超过了内存池大小的 40%，系统便会尝试将行、索引码、页面锁升级为表锁。注意，锁只能升级为表锁，而不能升级为页锁。
[/Quote]
谢谢啦，关于锁的个数界定，不知道下面理解是否正确？
假如下面的where 条件后有2000条符合条件的记录，那么常规来说应该是一个锁，锁定了这2000条记录。
或者2000个行锁？
update table1
set aa=@aa, bb=@bb (,...)
where column1=@condition1
and column2=@condition2
在 SQL SERVER 2K 中，如果一个事务中锁的数目超过了 1250，或者一个表或索引上的锁的数目超过了 765，系统便会检查整个系统内锁占用的内存池的大小，如果锁占用的内存池的大小超过了内存池大小的 40%，系统便会尝试将行、索引码、页面锁升级为表锁。注意，锁只能升级为表锁，而不能升级为页锁。
[/Quote]
谢谢啦，关于锁的个数界定，不知道下面理解是否正确？
假如下面的where 条件后有2000条符合条件的记录，那么常规来说应该是一个锁，锁定了这2000条记录。
或者2000个行锁？
update table1
set aa=@aa, bb=@bb (,...)
where column1=@condition1
and column2=@condition2
hansuzhi 2009-09-17
- 打赏
- 举报
转贴：
sp_lock报告有关锁的信息。
1）语法 sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']
2）参数
[@spid1 =] 'spid1'
master.dbo.sysprocesses 的 Microsoft® SQL Server 进程 ID 号。spid1 的数据类型为 int，默认值为 NULL。执行 sp_who 可获取有关该锁的进程信息。如果没有指定 spid1，则显示所有锁的信息。
[@spid2 =] 'spid2'
spid2 的数据类型为 int，默认设置为 NULL。spid2 为可以与 spid1 同时拥有锁的另一个 spid，用户还可获取有关它的信息。
说明 sp_who 可含有 0 个、1 个或 2 个参数。这些参数确定存储过程是显示全部、1 个还是 2 个 spid 进程的锁定信息。
3）返回代码值
0（成功）
结果集
列名 数据类型 描述
spid smallint SQL Server 进程 ID 号。
dbid smallint 请求锁的数据库标识号。
ObjId int 请求锁的对象的对象标识号。
IndId smallint 索引标识号。
type nchar(4) 锁的类型，有下面几个值：
DB：数据库
FIL：文件
IDX：索引
PG：页
KEY：键
TAB：表
EXT：扩展盘区
RID：行标识符
Resource nchar(16) 与 syslockinfo.restext 中的值对应的锁资源。
Mode nvarchar(8) 锁请求者的锁模式。该锁模式代表已授予模式、转换模式或等待模式。
Status int 锁的请求状态，有下面几个值：
GRANT
WAIT
CNVRT
注释
用户可以通过向 SELECT 语句的 FROM 子句中添加优化程序提示或设置 SET TRANSACTION ISOLATION LEVEL 选项来控制锁定。有关语法和限制的信息，请参见 http://www.yesky.com/imagesnew/software/tsql/ts_sa-ses_9sfo.htm 和 http://www.yesky.com/imagesnew/software/tsql/ts_set-set_74bw.htm。
通常，读操作获取共享锁，写操作获取排它锁。在更新操作的初始阶段读取数据时，会获取更新锁。更新锁与共享锁兼容。此后，如果更改了数据，更新锁会提升为排它锁。有时在更改数据时，会在获取排它锁之前暂时获取更新锁。此后，该更新锁会自动提升为排它锁。
可以锁定各种级别的数据，包括整个表、表中的一页或多页以及表的一行或多行。粒度级别较高的意向锁表示正在或试图以较低的锁粒度级别获取锁。例如，表意向锁表示获取共享页级锁或排它页级锁的意向。意向锁可以阻止另一个事务获取该表的表锁。
当分配或释放由 8 个数据库页组成的组时，将持有扩展盘区锁。扩展盘区锁在运行 CREATE 或 DROP 语句时或在运行需要新数据或索引页的 INSERT 或 UPDATE 语句时设置。
在读取 sp_lock 信息时，使用 OBJECT_NAME( ) 函数通过表的 ID 号获取表的名称，例如：
SELECT object_name(16003088)
所有与 SPID 值不关联的分布式事务都是孤立事务。SQL Server 2000 给所有孤立的分布式事务赋予 SPID 值"-2"
，使得用户更容易识别阻塞的分布式事务。有关更多信息，请参见 KILL。
有关使用 Windows NT 性能监视器查看特定进程 ID 信息的更多信息，请参见 DBCC。
权限
执行权限默认授予 public 角色。
示例
A. 列出所有锁
下面的示例显示 SQL Server 中当前持有的所有锁的信息。
USE master
EXEC sp_lock
B. 列出单个服务器进程的锁
下例显示进程 ID 53 的信息（其中包括锁信息）。
USE master
EXEC sp_lock 53
sp_lock报告有关锁的信息。
1）语法 sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']
2）参数
[@spid1 =] 'spid1'
master.dbo.sysprocesses 的 Microsoft® SQL Server 进程 ID 号。spid1 的数据类型为 int，默认值为 NULL。执行 sp_who 可获取有关该锁的进程信息。如果没有指定 spid1，则显示所有锁的信息。
[@spid2 =] 'spid2'
spid2 的数据类型为 int，默认设置为 NULL。spid2 为可以与 spid1 同时拥有锁的另一个 spid，用户还可获取有关它的信息。
说明 sp_who 可含有 0 个、1 个或 2 个参数。这些参数确定存储过程是显示全部、1 个还是 2 个 spid 进程的锁定信息。
3）返回代码值
0（成功）
结果集
列名 数据类型 描述
spid smallint SQL Server 进程 ID 号。
dbid smallint 请求锁的数据库标识号。
ObjId int 请求锁的对象的对象标识号。
IndId smallint 索引标识号。
type nchar(4) 锁的类型，有下面几个值：
DB：数据库
FIL：文件
IDX：索引
PG：页
KEY：键
TAB：表
EXT：扩展盘区
RID：行标识符
Resource nchar(16) 与 syslockinfo.restext 中的值对应的锁资源。
Mode nvarchar(8) 锁请求者的锁模式。该锁模式代表已授予模式、转换模式或等待模式。
Status int 锁的请求状态，有下面几个值：
GRANT
WAIT
CNVRT
注释
用户可以通过向 SELECT 语句的 FROM 子句中添加优化程序提示或设置 SET TRANSACTION ISOLATION LEVEL 选项来控制锁定。有关语法和限制的信息，请参见 http://www.yesky.com/imagesnew/software/tsql/ts_sa-ses_9sfo.htm 和 http://www.yesky.com/imagesnew/software/tsql/ts_set-set_74bw.htm。
通常，读操作获取共享锁，写操作获取排它锁。在更新操作的初始阶段读取数据时，会获取更新锁。更新锁与共享锁兼容。此后，如果更改了数据，更新锁会提升为排它锁。有时在更改数据时，会在获取排它锁之前暂时获取更新锁。此后，该更新锁会自动提升为排它锁。
可以锁定各种级别的数据，包括整个表、表中的一页或多页以及表的一行或多行。粒度级别较高的意向锁表示正在或试图以较低的锁粒度级别获取锁。例如，表意向锁表示获取共享页级锁或排它页级锁的意向。意向锁可以阻止另一个事务获取该表的表锁。
当分配或释放由 8 个数据库页组成的组时，将持有扩展盘区锁。扩展盘区锁在运行 CREATE 或 DROP 语句时或在运行需要新数据或索引页的 INSERT 或 UPDATE 语句时设置。
在读取 sp_lock 信息时，使用 OBJECT_NAME( ) 函数通过表的 ID 号获取表的名称，例如：
SELECT object_name(16003088)
所有与 SPID 值不关联的分布式事务都是孤立事务。SQL Server 2000 给所有孤立的分布式事务赋予 SPID 值"-2"
，使得用户更容易识别阻塞的分布式事务。有关更多信息，请参见 KILL。
有关使用 Windows NT 性能监视器查看特定进程 ID 信息的更多信息，请参见 DBCC。
权限
执行权限默认授予 public 角色。
示例
A. 列出所有锁
下面的示例显示 SQL Server 中当前持有的所有锁的信息。
USE master
EXEC sp_lock
B. 列出单个服务器进程的锁
下例显示进程 ID 53 的信息（其中包括锁信息）。
USE master
EXEC sp_lock 53
xman_78tom 2009-09-17
- 打赏
- 举报
在 SQL SERVER 2K 中，如果一个事务中锁的数目超过了 1250，或者一个表或索引上的锁的数目超过了 765，系统便会检查整个系统内锁占用的内存池的大小，如果锁占用的内存池的大小超过了内存池大小的 40%，系统便会尝试将行、索引码、页面锁升级为表锁。注意，锁只能升级为表锁，而不能升级为页锁。
wujinjian2008n 2009-09-17
- 打赏
- 举报
问楼主：exec sp_lock 怎么看？
wujinjian2008n 2009-09-17
- 打赏
- 举报
对的
加载更多回复（7）
