[转]Oracle For update研究

http://www.cnblogs.com/zping/archive/2009/01/06/1370245.html

 

 

 写道

1 select * from hjh for update [of hjh.a]; 锁定表的所有行
   可以select,insert,不可以update,Delete Commit提交后，解除锁定

2 SELECT * FROM hjh WHERE a = 1 FOR UPDATE [of hjh.a]; 只锁定a=1的行

3 SELECT a.a, t1.d FROM hjh a, t1 WHERE a.a = t1.c [and a.a = 2] FOR UPDATE;锁定两个表的中满足条件的行

4 SELECT a.a, t1.d FROM hjh a,t1 WHERE a.a = t1.c [and a.a = 2] FOR UPDATE OF a.a; 只锁定hjh中满足条件的行

加行级共享锁，然后以独占方式进行修改数据(update,delete)，
你FOR UPDATE 的时候 ，其他人不能更改你查询出来的记录
commit和rollback都可以释放锁

DML锁包括table-level locks(TM)和Row-level locks(TX)
说明row-level lock(TX type)是为了确保其它用户不可以在相同的时间修改相同的行
TM 是对象锁，表示可能在这个对象上做了什么操作，还没有结束，所以不允许ddl
TX 是事务锁，表示发起了一个事务，发起事务的判别标准

 

 

Oracle 的for update行锁

http://hi.baidu.com/mcj0127/blog/item/111a900777db06c87b89473c.html

 

SELECT FOR UPDATE 相关的知识

一个邮件发送的应用会每过一定的时间片去数据库中取未发的邮件然后发送邮件，成功后将数据库中邮件标识未发改为已发。

这个应用部署在websphere上，websphere采用是垂直克隆，有4个server,当4个server都开启的时候，就出现同时发4封相同的邮件给同一个用户。

这时，for update锁就可以解决这个问题!

1:状态解释

statement: 一个SQL语句。

session:     一个由ORACLE用户产生的连接，一个用户可以产生多个SESSION ，但相互之间是独立的。

transaction:所有的改变都可以划分到transaction里，一个transaction包含一个或多个SQL。当一个SESSION建立的时候就是一个TRANSACTION开始的时刻，此后   transaction的开始和结束由DCL控制，也就是每个COMMIT/ROLLBACK都标示着一个transaction的结束。

consistency：是对于statement级别而不是transaction级别来说的。sql statement 得到的数据都是以sql statement开始的IMAGE。

 

2:sql解释

LOCK的基本情况: update, insert ,delete, select ... for update会LOCK相应的ROW 。

只有一个TRANSACTION可以LOCK相应的行，也就是说如果一个ROW已经LOCKED了，那就不能被其他TRANSACTION所LOCK了。

LOCK由statement产生但却由TRANSACTION(commit，rollback)结尾，也就是说一个SQL完成后LOCK还会存在，只有在COMMIT/ROLLBACK后LOCK才会RELEASE。

 

        SELECT.... FOR UPDATE [OF cols] [NOWAIT];

OF cols: SELECT cols FROM tables [WHERE...] FOR UPDATE [OF cols] [NOWAIT];

3：sql说明

a:关于OF

transaction A运行

select a.object_name,a.object_id from wwm2 a,wwm3 b where b.status='VALID' and a.object_id=b.object_id for update of a.status

则transaction B可以对b表wwm3的相应行进行DML操作,但不能对a表wwm2相应行进行DML操作.

 

反一下看看

transaction A运行

select a.object_name,a.object_id from wwm2 a,wwm3 b where b.status='VALID' and a.object_id=b.object_id for update of b.status

则transaction B可以对a表wwm2的相应行进行DML操作,但不能对b表wwm3相应行进行DML操作.

也就是说LOCK的是行,只是如果不加OF的话会对所有涉及的表LOCK的,加了OF后只会LOCK OF 字句所在的TABLE.

 

b:关于NOWAIT(如果一定要用FOR UPDATE，我更建议加上NOWAIT)

当有LOCK冲突时会提示错误并结束STATEMENT而不是在那里等待(比如:要查的行已经被其它事务锁了,当前的锁事务与之冲突,加上nowait,当前的事务会结束会提示错误并 立即结束 STATEMENT而不再等待).返回错误是"ORA-00054: resource busy and acquire with NOWAIT pecified"

 

 

注另外如下用法也值得推荐，应该酌情考虑使用。

1:FOR UPDATE WAIT 5

5秒后会提示ORA-30006: resource busy; acquire with WAIT timeout expired

2:FOR UPDATE NOWAIT SKIP LOCKED;

会提示no rows selected 

3:TABLE LOCKS   

LOCK TABLE table(s) IN EXCLUSIVE MODE [NOWAIT];

同样也是在transaction结束时才会释放lock。

4:DEADLOCK

transaction a lock rowA     , then transaction b lock rowB

then transaction a tries to lock rowB, and transaction b tries to lock rowA

也就是说两个transaction都相互试图去lock对方已经lock的ROW，都在等待对方释放自己的lock，这样就使死锁。

deadlock也会有600提示。