Select for update knowledge

Select for update knowledge

An application that sends an email will fetch unsent emails from the database and send the emails each time slice. After the application succeeds, it will change the mail ID in the database to "sent.

This application is deployed on websphere. websphere adopts vertical cloning and has four servers. When all four servers are enabled, four identical emails are sent to the same user at the same time.

In this case, the for update lock can solve this problem!

1: status explanation
Statement: An SQL statement.
Session: A connection generated by an Oracle user. A user can generate multiple sessions, but they are independent of each other.
Transaction: All changes can be divided into transaction, which contains one or more SQL statements. When a SESSION is created, it is the start time of a TRANSACTION. After that, the start and end of a transaction are controlled by DCL. That is, each COMMIT/ROLLBACK indicates the end of a transaction.
Consistency: For the statement level rather than the transaction level. The data obtained by SQL statement is an IMAGE starting with SQL statement.

2: SQL explanation
Basic Conditions of LOCK: update, insert, delete, select... for update will LOCK the corresponding ROW.
Only one TRANSACTION can LOCK the corresponding ROW. That is to say, if one ROW has been LOCKED, it cannot be LOCKED by other transactions.
The LOCK is generated by statement but ended by TRANSACTION (commit, rollback). That is to say, after an SQL statement is completed, the LOCK still exists. The LOCK is RELEASE only after COMMIT/ROLLBACK.

SELECT... for update [OF cols] [NOWAIT];
OF cols: SELECT cols FROM tables [WHERE...] for update [OF cols] [NOWAIT];

3: SQL description
A: About
Transaction A run
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 can perform DML operations on the corresponding row of wwm3 in Table B, but cannot perform DML operations on the corresponding row of wwm2 in table.

Take a look.
Transaction A run
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 can perform DML operations on the corresponding row of wwm2 in Table a, but cannot perform DML operations on the corresponding row of wwm3 in table B. that is to say, the LOCK is a row, but if it is not added with OF, it will LOCK all involved tables. After adding OF, it will only LOCK the TABLE where the words OF are located.

B: About NOWAIT (If you must use for update, I suggest adding NOWAIT)
When there is a LOCK conflict, an error will be prompted and the STATEMENT will be ended instead of waiting there (for example, the row to be queried has been locked by other transactions, the current LOCK transaction conflicts with it, plus nowait, when the current transaction ends, an error will be prompted and the STATEMENT will be ended immediately without waiting ). the returned error is "ORA-00054: resource busy and acquire with NOWAIT pecified"

Note: The following usage is also recommended and should be considered as appropriate.
1: for update wait 5
ORA-30006: resource busy; acquire with WAIT timeout expired will be prompted in 5 seconds
2: for update nowait skip locked;
No rows selected will be displayed.
3: TABLE LOCKS
Lock table table (s) in exclusive mode [NOWAIT];
The lock is also released at the end of transaction.
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
That is to say, both transactions attempt to lock each other's locked ROW and are waiting for the other to release its lock. This will cause a deadlock. Deadlock also has a 600 prompt.