RE: Still the case that rolling back to savepoint does not restart blocked sessions?
From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Thu, 29 May 2014 14:11:36 -0700
Message-ID: <BLU179-W60BED0043E036A4E06D71DEB240_at_phx.gbl>
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
Date: Thu, 29 May 2014 14:11:36 -0700
Message-ID: <BLU179-W60BED0043E036A4E06D71DEB240_at_phx.gbl>
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
To: 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 Thu May 29 2014 - 23:11:36 CEST