RE: Still the case that rolling back to savepoint does not restart blocked sessions?
Date: Fri, 30 May 2014 14:49:18 +0000
Message-ID: <D7864FA3E7830B428CB2A5A5301B63EE017231BEFF_at_S7041VA005.soa.soaad.com>
It seems that "rollback to savepoint" could signal the waiting session(s) to proceed. If they immediately block again on the same lock, so be it. I'm sure there are good technical reasons why it's hard. I'm just irked because it's messing up my design right now... :)
Thanks for your feedback. I love this group.
Matt
From: Iggy Fernandez [mailto:iggy_fernandez_at_hotmail.com]
Sent: Thursday, May 29, 2014 5:12 PM
To: McPeak, Matt; oracle-l_at_freelists.org
Subject: RE: Still the case that rolling back to savepoint does not restart blocked sessions?
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_at_subaru.com<mailto:vxsmimmcp_at_subaru.com> To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri May 30 2014 - 16:49:18 CEST