Before updating a row in a database table, you might want to check for concurrency conflict – another transaction should not be updating the same row simultaneously.
This can be achieved by locking the row in Oracle before update. Here’s how.
Lock Rows with SELECT…FOR UPDATE
In Oracle, adding a FOR UPDATE clause to a SELECT statement lets you lock the selected rows. Other users cannot lock or update those rows until you end your transaction.
Things to know about row locking in Oracle:
1.SELECT…FOR UPDATE will not be able to lock rows if they are already locked by another transaction. By default, the session will wait for the locks to be released by the other transaction.
2.You can instruct Oracle to not wait for the locks to be released, by adding a NOWAIT to SELECT…FOR UPDATE.
3.To release the locked rows, a COMMIT or ROLLBACK must be issued in the session holding the lock.
4.SELECT…FOR UPDATE is restricted from use in SQLs with the DISTINCT operator, set operators, CURSOR expression, GROUP BY or aggregate functions.
5.Nested table rows are not locked when the parent table rows are locked. You must lock nested table rows explicitly if needed.
6.In a SQL with joins of multiple tables, if you want to lock rows from only a particular table, specify the OF … column clause to indicate which table to lock. Without this clause, Oracle locks the selected rows from all the tables in the join.
7.When selecting multiple rows for update, you can add the SKIP LOCKED clause to lock rows if not already locked by another transaction, else skip rows if they are already locked. [Note: Oracle recommends that you use the Oracle Streams Advanced Queuing APIs instead of the SKIP LOCKED functionality.]
