sql 如何判断某个表是否被锁

hansuzhi 2009-09-17 11:14:10
请问大侠： 如何判断某个表是否被锁？


------------问题描述---------------

现在我有一个事务，执行下面操作：

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是否被锁定，锁定就不执行事务，直接给出锁定提示。

请指点如何实现呢 ？ 谢谢！！

...全文
3204 27 打赏 收藏 转发到动态 举报
写回复
用AI写文章
27 条回复
切换为时间正序
请发表友善的回复…
发表回复
hansuzhi 2009-09-18
  • 打赏
  • 举报
 回复
所以我考虑这个工作做成批处理比较好，在夜深人静的时候自己慢慢的按顺序执行得了。。。
hansuzhi 2009-09-18
  • 打赏
  • 举报
 回复
谢谢 楼上兄弟们的指点
我研究了下客户的逻辑：他们的要求应该是如果执行的话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。

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，看一下哪一步 占用时间最长。
hansuzhi 2009-09-17
  • 打赏
  • 举报
 回复
这2个存储过程我看了 不太适合啊(而且sp_lock需要管理员权限才可以调用)

我这里想判断的表被锁，不是指的死锁。 就是一个事务在执行过程中加的正常锁。 譬如holdlock
我判断如果某个表被锁，譬如table1， 表示事务已经在运行了，这样我就可以告诉用户需要等那个事务执行完毕，才可以再调用该事务。

不知道表达的是否清楚。 请指点。
soft_wsx 2009-09-17
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 hansuzhi 的回复:]
问题补充：
sp_lock,sp_who 这2个都不可以应该

因为客户让在程序中使用的数据库用户名/密码  没有管理员权限，也不会给管理员权限。所以需要访问系统表的方法应该都用不了
[/Quote]谁都可以看吗？客户也看不懂呀！
hansuzhi 2009-09-17
  • 打赏
  • 举报
 回复
问题补充：
sp_lock,sp_who 这2个都不可以应该

因为客户让在程序中使用的数据库用户名/密码 没有管理员权限，也不会给管理员权限。所以需要访问系统表的方法应该都用不了
--小F-- 2009-09-17
  • 打赏
  • 举报
 回复
sp_who,查看所有用户及sp_lock,查看所有当前打开的资源,应该会发出Lock是哪个进程,
用 kill 进程号 删除进程开锁,
wujinjian2008n 2009-09-17
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 soft_wsx 的回复:]
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]


不知道各位大侠有什么建议没？
hansuzhi 2009-09-17
  • 打赏
  • 举报
 回复
现在突然很郁闷了。。。
如果存储过程执行的业务很复杂，费时很长的话，貌似只能一次一次的执行了。 只要并发调用，哪怕你输入的条件不一样，如果存储过程执行的时候把对表的行锁升级到表锁，都必然挂掉啊



我们这边在做一个维护项目，客户在一个存储过程（有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%。
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
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
xman_78tom 2009-09-17
  • 打赏
  • 举报
 回复
在 SQL SERVER 2K 中，如果一个事务中锁的数目超过了 1250，或者一个表或索引上的锁的数目超过了 765，系统便会检查整个系统内锁占用的内存池的大小，如果锁占用的内存池的大小超过了内存池大小的 40%，系统便会尝试将行、索引码、页面锁升级为表锁。注意，锁只能升级为表锁，而不能升级为页锁。

wujinjian2008n 2009-09-17
  • 打赏
  • 举报
 回复
问楼主：exec sp_lock 怎么看？
wujinjian2008n 2009-09-17
  • 打赏
  • 举报
 回复
对的
加载更多回复（7）
SQlServer中的锁
介绍SQlServer中的锁的机制，其中包括一些例子
Oracle DBA 常用的一些SQL语句(50个)
changefilter.sql oracle的连接数查看和连接用户的查看.sql Sun30新建oracle用户.sql user_job.sql xaview.sql 修改分区索引.SQL 修改数据库核心字符集.txt 僵死进程查询.sql 创建ORACLE表空间.sql 回滚段等待.SQL 在数据库中建JOB.sql 外连接.SQL 外部例程.SQL 建6节点回滚段.SQL 建回滚段.SQL 建立一个与现存数据库相同但不含数据的空库.sql 扩展超过100M查询.sql 整理表空间碎片.sql 新建表.sql 显示数据文件信息.sql 查插入表的性能.sql 查看session正在执行什么SQL.sql 查看session正在等待何种系统资源.sql 查看哪些session正在使用哪些回滚段.sql 查看某个进程正在执行什么SQL语句.sql 查看用户表所占空间的大小.sql 查看系统SGA区状态.sql 查看系统中使用了哪些设备文件.sql 查看系统中每个表空间的使用情况.sql 查看系统中每个表空间的大小.sql 查看系统联接数.sql 查看系统锁.sql 查看表空间使用情况.sql 查看进程占用回滚段的情况.sql 查看那些数据库对象被修改过.sql 用oupput的ora过程.sql 用户命令查询.sql 用户进程查询.sql 监控数据库性能的SQL.sql 看user_job.sql 索引表清除sql生成.sql 索引表空间使用情况查询.sql 索引表空间整理.sql 统计.sql 获取数据库版本信息.sql 表空间使用情况.txt 表空间使用查询.sql 表空间剩余空间查看.sql 表空间回收.sql 过滤表清除sql生成.sql 进程使用回滚段查询.sql 锁表查询.sql
SQL SERVER 2008 R2 查看和杀死死锁进程
SQL SERVER 2008 R2 查看和杀死死锁进程。数据库进程死锁是一个很严重的问题，快速定位到死锁的进程尤为重要。
sqlserver 判断某个进程是否锁表
查询连接该库的进程中所否被锁定 select * from master..sysprocesses whereblocked>0 anddbid=db_id('数据库名'); kill被锁的spid exec ('kill spid') 转载于:https://www.cnblogs.com/Dot-Boy/archive/2009/03/1...
sqlserver查看表是否被锁
CREATE  procedure [dbo].[sp_who_lock] as begin declare @spid int,@bl int,         @intTransactionCountOnEntry  int,         @intRowcount    int,         @intCountProperties   int,         @intC
MS-SQL Server

34,594

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章