for update nowait tips
In this example we select a student row and nowait up to 15 seconds for another session to release their lock:
select
student_last_name
from
student
where
student_id = 12345
FOR UPDATE nowait 15;
Conversely, in this example we use "for update nowait" and Oracle will immediately fail the transactions if it is waiting on any shared resources (locks or latches):
select
student_last_name
from
student
where
student_id = 12345
FOR UPDATE nowait;
SELECT select_clause
FROM from_clause
WHERE where_clause
FOR UPDATE OF column_name;
Things to know about row locking in Oracle:
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.
You can instruct Oracle to not wait for the locks to be released, by adding a NOWAIT to SELECT…FOR UPDATE.
To release the locked rows, a COMMIT or ROLLBACK must be issued in the session holding the lock.
SELECT…FOR UPDATE is restricted from use in SQLs with the DISTINCT operator, set operators, CURSOR expression, GROUP BY or aggregate functions.
Nested table rows are not locked when the parent table rows are locked. You must lock nested table rows explicitly if needed.
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.
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.]
Comments
Post a Comment