Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.
So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.
Ready? Here it goes:
Skip Locked Syntax in SELECT FOR UPDATE
This is an 11g feature, and it’s a bit controversial. Why?
The SELECT FOR UPDATE statement is well known and responsible by several problematic operations, mainly in transactional databases. It’s not rare to face issues like errors below when trying to perform large updates on database:
ORA-30006: resource busy; acquire with WAIT timeout expired ORA-00054 resource busy and NOWAIT specified
Worse than this, if a select for update task aborts, a zombie process may hold the row locks long term, requiring DBA intervention.
In 11g, the clause SKIP LOCKED has been released, allowing to skip-over any rows that are already locked. Check below for a simple example:
select COLUMN1, COLUMN2 from MYTABLE where COLUMN1='DESIRED_VALUE' for update skipped locked;
This is very useful in transaction environments, specially when facing errors mentioned above, however can cause logical corruption.
The reason is obvious, if some rows be in lock, those are not being updated. In this case, if the table has 100 entries where COLUMN1=’DESIRED_VALUE’ but 10 of them are in lock, only 90 will be actually selected, making statement invalid in some circumstances.
This is very useful in transaction environments, specially when facing errors mentioned above, however can cause logical corruption.
The reason is obvious, if some rows be in lock, those are not being updated.
Additional Note: In some cases, increasing the table’s initrans allows more buckets for locking:
alter table MYTABLE move initrans xxxx;
Here is a very interesting post by Jonathan Lewis about it.
See you next week!
