Oracle For Update row lock

Posted on 2009-06-24 filed under: Oracle (RSS) 18 views

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

SELECT FOR UPDATE-related knowledge of the application of an e-mail every piece had a certain amount of time to check the database is not issued by mail and send the message, after the success of e-mail databases logo has been replaced by non-fat fat.
This application deployed in the websphere on, websphere using vertical cloning, there are four server, when all four server when open, the four appeared at the same time made the same message to the same user.
At this time, for update lock will be able to solve this problem!
1: the state of the interpretation
statement: a SQL statement.
session: a user-generated ORACLE connection, a user can have multiple SESSION, but is independent of each other.
transaction: all of the changes can be divided in the transaction, a transaction contains one or more SQL. SESSION created when a time is a time to start TRANSACTION since the beginning and end of the transaction by the DCL control, that is, each COMMIT / ROLLBACK have marked the end of a transaction.
consistency: is the statement level rather than in terms of transaction-level. sql statement the data is the beginning of the sql statement to IMAGE.

2: sql explained
LOCK of the basic situation: update, insert, delete, select ... for update will LOCK the ROW.
There is only one TRANSACTION can LOCK the corresponding line, that is to say if a ROW has been LOCKED, it can not be a LOCK by other TRANSACTION.
LOCK generated by the statement but by the TRANSACTION (commit, rollback) at the end, that is a SQL will continue to exist after the completion of LOCK only when the COMMIT / ROLLBACK before LOCK after RELEASE.

SELECT .... FOR UPDATE [OF cols] [NOWAIT];
OF cols: SELECT cols FROM tables [WHERE ...] FOR UPDATE [OF cols] [NOWAIT];
3: sql notes
a: on the OF
running 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
While transaction B can wwm3 corresponding b table DML lines, but not on a table wwm2 lines corresponding DML operation.

Anti-click to see
running 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
While transaction B can wwm2 on a table row corresponding to DML, but can not b the corresponding table wwm3 line DML operation.
LOCK is the line that is to say, but if not, then OF will LOCK all of the table, only after the addition of OF where LOCK OF the words TABLE.

b: on the NOWAIT (If you must use FOR UPDATE, I also suggest that with NOWAIT)
When there is a conflict between the LOCK will be prompted STATEMENT errors and end there rather than wait for (for example: To check the line has been locked by other services, the current conflict with lock affairs, together with the nowait, the end of the current affairs will be prompted to error STATEMENT immediately rather than waiting for the end). the return of error is "ORA-00054: resource busy and acquire with NOWAIT pecified"

Also note the following usage should also be recommended, should consider the use of discretion.
1: FOR UPDATE WAIT 5
After five seconds will be prompted to ORA-30006: resource busy; acquire with WAIT timeout expired
2: FOR UPDATE NOWAIT SKIP LOCKED;
No rows selected will be prompted to
3: TABLE LOCKS
LOCK TABLE table (s) IN EXCLUSIVE MODE [NOWAIT];
Also in the transaction before the end of the release 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 that is to say the two are mutually lock each other has been to try to lock the ROW, the other party has been waiting for the release of their own lock, thus deadlock.
600 tips will deadlock.