Databases Reference
In-Depth Information
In step 6 we try to update, in
SESSION
2
, the first row that was updated earlier in
SESSION
1
.
The transaction in
SESSION
1
is still active, so
SESSION
2
should wait until the
SESSION
1
transaction ends, as shown in the following screenshots:
In this situation, the transaction in
SESSION
1
is waiting for a resource (the row in the
CUSTOMERS
table with
CUST_ID
=
30
) locked by
SESSION
2
which, in turn, is waiting for a
resource (the row in
CUSTOMERS
table with
CUST_ID
=
26
) locked by
SESSION
1
. This is a
deadlock, and due to its cyclic nature there is no chance for the transactions involved to end
regularly because they are blocking each other.
When the Oracle database detects a deadlock it automatically rolls back the statement
causing the deadlock. You can see this in the following screenshot, where we have
SESSION
1
on the left and
SESSION
2
on the right:
