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
The select for update is not a
strategy because there are many things that can go wrong.
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
See my related notes on select for update clause:
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.