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.
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.
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. |
