Question: What does the 11g "skip
locked" do in a select for update statement?
The "select for update" statement has always been problematic
for large updates because it the default is to wait for locks and using
"select for update" other tasks can abort waiting on access
with the ORA-300036 error:
ORA-30006: resource busy; acquire
with WAIT timeout expired
In other cases using "select for update"
with the "nowait" clause you your own update may abort with
the ORA-00054 error:
ORA-00054 resource busy and NOWAIT
Even worse, if a select for update task
aborts, a zombie process may hold the row locks long term, requiring
Before the 11g "skip locked"
directive, long-running update transactions will either re-select a
row before updating it to ensure that it has not changed since the
last read, or add a date-time stamp column to the table to see if it
had been updated since the transaction began.
11g, using select for update with the skip locked directive
will tell the update to skip-over any rows that are already locked.
This is useful in high DML environments because it removes the
locking and concurrency issues, but data cohesion will become an
For example, assume that a select for update
requests 1,000 rows and 200 are already locked by other transactions
that are updating the credit column:
for update skipped locked;
As we see, those 200 rows that were being updated
may have experienced a change to credit='BAD', making our
update invalid, and causing logical data corruption.
some cases you may need to increase the value of a table's initrans
values to allow for more locking buckets:
alter table customer move initrans 200;
In sum, the select for update skipped locked is
useful in high concurrency environments to prevent locking errors,
but you run the risk of logical corruption where a changed value is
dependent upon another column value within the row. In general, the
re-read strategy is better, where you re-read each row before update
in to ensure that it did not change since the initial read.