ORACLE 的一个行锁问题

Question

我用ASP写了一段代码，逻辑如下
1 select max(XXX) from tableA where ....
2 update tableA set XXX=max(XXX)+1 where ...
......资料处理逻辑......
3 调用 store Procedure
 store Procedure中会执行: select XXX from  tableA where ... for update
（执行到这时，Oracle会报出错误讯息：ORA-00060: 等候資源時偵測到一個死結）
update tableA set XXX=max(XXX)+1 where ...看到这可能有人会说，这种做法当然会报错了，
但我的问题是这段逻辑我在测试DB中执行并没有报错，直到部署到正式环境后才暴露出问题
所以请问高手们，这是否和DB的环境配置有关，还是什么其他的因素导致在一个环境中出错，而在另一个环境中没有问题谢谢啦

Answer 1

select XXX from tableA where ... for update
这条语句锁定的数据过多。
当多用户并发时同时执行同样这一条语句，如果tableA数据量大，
且where后条件不能很好的利用索引，会导致各自锁定一部分数据，
互相等待对方锁定的那部分数据行的锁释放，这样导致死锁的可能性大大
增加。你的测试环境没有出现的原因，可能是数据量较小。解决办法是，可以将最大值先存入另一张表，只有一行记录。
然后使用select * from update主动锁定然后+1更新、提交，这样就不
会造成死锁了。

Answer 2

谢谢tangren的回复，我开始没用表达清楚
select XXX from tableA where ...查询出来的数据保证是只有一条的
而且1,2,3是在一个事务中的
并且没有并发，是我晚上一个人跑的，确保线上只有我一个人

Answer 3

update tableA set XXX=max(XXX)+1 where ...
我觉得这个做法问题比较大，他会为max()要求锁住表中的所有行不让修改，直到事务提交。
假设这个时候同时有两个客户端。
a会话先select select XXX from tableA where ...for update已经锁住第6行
b会话进来也使用select select XXX from tableA where ...for update锁住第7行
a会话执行update tableA set XXX=max(XXX)+1 where ...把要求全表锁。但是被b锁住了第7行，所以a等待
b会话执行update tableA set XXX=max(XXX)+1 where ...把要求全表锁,但是被a锁住了第6行，所以也等待。
此时死锁就出现了。但是lz说确保只有一个会话，那是不可能的，一个会话绝无可能产生死锁毫无疑问的.

Answer 4

谢谢上面两位的回复，现在我准备验证一下，请问有什么sql能够查询到表中的哪条line被锁定么
由于连续发问，我会额外加分的
谢谢

Answer 5

哈哈，这个有点玄幻。
在update中使用统计函数没有测试过，我觉得可以。
update tableA set XXX=(select max(XXX)+1 from tableA where) where ..

Answer 6

首先，因为oracle的锁实现机制的原因，无法从现有任何一个表或视图知道锁住了具体的哪一行。唯一的办法是你用select ...for update not wait来测试。
所以得想其他办法来观察，
这里提供一段tom的sql,用量查看谁阻塞了谁：
select 
(select username from v$session where sid = a.sid) bloker,
a.sid,' is bloking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a ,v$lock b
where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2=b.id2;还有一断用来查看锁和相关对象的信息
select /*+ RULE */ ls.osuser os_user_name,   ls.username user_name,   
decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX', 
'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,   
o.object_name object,   decode(ls.lmode, 1, null, 2, 'Row Share', 3, 
'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null) 
lock_mode,    o.owner,   ls.sid,   ls.serial# serial_num,   ls.id1,   ls.id2    
from sys.dba_objects o, (   select s.osuser,    s.username,    l.type,     
l.lmode,    s.sid,    s.serial#,    l.id1,    l.id2   from v$session s,     
v$lock l   where s.sid = l.sid ) ls  where o.object_id = ls.id1 and    o.owner 
<> 'SYS'   order by o.owner, o.object_name

Answer 7

update tableA set XXX=max(XXX)+1 where ...
XXX字段是主键吗?如果是可能有问题,还有就是tableA有没有子表呢?