SELECT FOR UPDATE 相關的知識

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提示。