Question: I am having trouble with locking using
select for update, and I want to know how to prevent deadlocks while holding
row locks. Are there alternatives to select for update?
Answer:
The
select for update has many issues, and
select for update
is especially dangerous when a transaction aborts and a "zombie"
process continues to hold rows locks.
Oracle
allows you to choose the strategy for locking, either pessimistic or
optimistic, depending on your needs. This is the issue of pessimistic
vs. optimistic locking:
Pessimistic locking: The developer
must declare their intent to update the row set. This is done with the
SELECT xxx FOR UPDATE clause.
Optimistic locking:
You re-read data and only update it if it did not change since the
initial fetch.
The select for update is the bane of the DBA, and there are many
better alternatives to select for update.
The select for update is not a
good locking
strategy because there are many things that can go wrong.
When managing web-based Oracle databases, the traditional "select for
update" locking is inappropriate, and Oracle professionals have struggled
with alternative mechanisms to maintain data integrity using an "optimistic"
coding strategy:
Re-read - When an update comes in from
the internet, the Oracle code re-reads the data to ensure that there are no
changes since the data was originally delivered.
Timestamp
- Some Oracle shops add a timestamp column to enforce serial updates and
prevent accidental overlaying of data. Now in Oracle 10g and beyond we
see the new rowscn pseudo-column and the rowdependencies clause.
Instead
of select for update, savvy Oracle developers will adopt
alternatives mechanisms like:
1 - On initial read, save the row contents in RAM.
2 - Re-read upon update commit time. If the rows has
not changed since the initial read, update the row.
3 - If row has changed re-read again, process and update
the row
See my related notes on select for update clause:
-
-
-
-
-
-
-
 |
If
you like Oracle tuning, you may enjoy my bestselling book
Oracle Tuning: The Definitive Reference",
with over 900 pages of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
and include the URL for the page.
