Question:
I can't understand, I read English, it seems that the undocumented skip locked feature in oracle 11g does not do anything additional.
Explain the differences with and without this feature.
Answer:
The difference between requests with for update , with and without the skip locked option is as follows:
- Without this option, the query will not return if there is at least one locked record in the selection. Those. it will wait until the transaction is completed in the session that requested the lock . If you add
nowait, it will return immediately with an exception that can be handled.
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
- With the
skip lockedoption, it will return immediately and return only unlocked records orno rows selectedif there are none. - And an important difference for working with cursors – a cursor with
for updateblock all records that fall under the cursor condition immediately uponopen, and if you addskip locked, then only those lines will be locked that were actually read infetch, which allows you to limit the number of locked ones. records, or return an open cursor as the result of a function with unlocked records.
The undocumented skip locked option was in the 9i and 10g versions.
It is documented since 11g, see here .
This answer implies row lock , for exclusive table lock see documentation.
A small example. In the first session:
SQL> select emp_id from emp where emp_id in (1, 2) for update;
EMP_ID
----------
1
2
2 rows selected.
In another session:
SQL> select emp_id from emp where emp_id in (1, 2, 3) for update nowait;
select emp_id from emp where emp_id in (1, 2, 3) for update nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> select emp_id from emp where emp_id in (1, 2, 3) for update skip locked;
EMP_ID
----------
3
1 row selected.
SQL> select emp_id from emp where emp_id in (1, 2, 3) for update;
-- ждёт, пока в первой сессии не будет произведён откат