RE: Still the case that rolling back to savepoint does not restart blocked sessions?
- From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- To: "vxsmimmcp@xxxxxxxxxx" <vxsmimmcp@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 May 2014 15:02:09 -0700
As Jonathan pointed out, Transaction C will be able to acquire a lock on the
row that Transaction A has unlocked. Upon the rollback to savepoint,
Transaction A unlocks the row by updating the data block again. However,
transaction B continues to wait because it is waiting for the TX enqueue
corresponding to Transaction A; it is not checking the data block itself and is
unaware that Transaction A has unlocked the row.
Iggy
From: iggy_fernandez@xxxxxxxxxxx
To: vxsmimmcp@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Still the case that rolling back to savepoint does not restart
blocked sessions?
Date: Thu, 29 May 2014 14:11:36 -0700
That behavior will never change because Oracle Database does not have an
in-memory "lock manager" that keeps track of row locks. Instead, transactions
lock rows by updating the data blocks. In other words, there is no "enqueue"
corresponding to each row that Session A has updated. When Transaction A rolls
back to a save point, no enqueues are involved either.
Therefore Session B waits, not an an enqueue corresponding to a row that
Session A has updated, but on an enqueue representing Transaction A itself,
that is, the TX enqueue of Transaction A.
Other database management systems behave differently because they have
in-memory lock managers. Therefore, they promote row locks to table locks when
certain thresholds are crossed which is a recipe for deadlocks. Oracle Database
is not subject to this kind of deadlock but transactions must write to data
blocks in order to lock rows.
Iggy
From: vxsmimmcp@xxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Subject: Still the case that rolling back to savepoint does not restart blocked
sessions?
Date: Thu, 29 May 2014 18:02:24 +0000
If transaction A is waiting on a lock held by transaction B and transaction B
rolls back to a savepoint (releasing the lock), transaction A remains blocked
until transaction B ends completely (with either a full rollback or a commit).
Does anyone know if this is still the case in 12c? I really hate this behavior.
Thanks,
Matt
Other related posts:
