Database Reference
In-Depth Information
Blocked Merges, Updates, and Deletes
In an interactive application—one where you query some data out of the database, allow an end user to manipulate
it, and then put it back into the database—a blocked
UPDATE
or
DELETE
indicates that you probably have a lost update
problem in your code. (I'll call it a bug in your code if you do.) You are attempting to
UPDATE
a row that someone else
is already updating (in other words, one that someone else already has locked). You can avoid the blocking issue by
using the
SELECT FOR UPDATE NOWAIT
query to
•
Verify the data has not changed since you queried it out (preventing lost updates).
UPDATE
or
DELETE
from blocking).
•
Lock the row (preventing the
As discussed earlier, you can do this regardless of the locking approach you take. Both pessimistic and optimistic
locking may employ the
SELECT FOR UPDATE NOWAIT
query to verify the row has not changed. Pessimistic locking
would use that
SELECT FOR UPDATE NOWAIT
statement the instant the user indicated her intention to modify the data.
Optimistic locking would use that statement immediately prior to updating the data in the database. Not only will this
resolve the blocking issue in your application, but it'll also correct the data integrity issue.
Since a
MERGE
is simply an
INSERT
and
UPDATE
(and in 10
g
and above, with the enhanced
MERGE
syntax, it's a
DELETE
as well), you would use both techniques simultaneously.
Deadlocks
Deadlocks
occur when you have two sessions, each of which is holding a resource that the other wants. For example,
if I have two tables,
A
and
B
, in my database, and each has a single row in it, I can demonstrate a deadlock easily. All I
need to do is open two sessions (e.g., two SQL*Plus sessions). In session
A
, I update table
A
. In session
B
, I update table
B
. Now, if I attempt to update table
A
in session
B
, I will become blocked. Session
A
has this row locked already. This
is not a deadlock; it is just blocking. I have not yet deadlocked because there is a chance that session
A
will commit or
roll back, and session
B
will simply continue at that point.
If I go back to session
A
and then try to update table
B
, I will cause a deadlock. One of the two sessions will be
chosen as a victim and will have its statement rolled back. For example, the attempt by session
B
to update table
A
may
be rolled back, with an error such as the following:
update a set x = x+1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Session
A
's attempt to update table
B
will remain blocked—Oracle will not roll back the entire transaction. Only
one of the statements that contributed to the deadlock is rolled back. Session
B
still has the row in table
B
locked,
and session
A
is patiently waiting for the row to become available. After receiving the deadlock message, session
B
must decide whether to commit the outstanding work on table
B
, roll it back, or continue down an alternate path and
commit later. As soon as this session does commit or roll back, the other blocked session will continue on as if nothing
happened.
Oracle considers deadlocks to be so rare and unusual that it creates a trace file on the server each time one does
occur. The contents of the trace file will look something like this:
*** 2014-04-16 18:58:26.602
*** SESSION ID:(31.18321) 2014-04-16 18:58:26.603
*** CLIENT ID:() 2014-04-16 18:58:26.603
*** SERVICE NAME:(SYS$USERS) 2014-04-16 18:58:26.603
*** MODULE NAME:(SQL*Plus) 2014-04-16 18:58:26.603
