Strange thing
From: Mladen Gogala <gogala.REMOVETHISmladen_at_google.com>
Date: Tue, 12 Jun 2012 18:17:34 +0000 (UTC)
Message-ID: <jr813u$6oo$1_at_solani.org>
I have recently re-read the following Tom Kyte's article and something has struck me as very odd:
if you update the parent table or delete from it, the child table will still be locked, but just for the duration of the UPDATE or DELETE. The lock is released after the statement is processed—not when you commit. This is better than in 1993, but the lock still exists.
Date: Tue, 12 Jun 2012 18:17:34 +0000 (UTC)
Message-ID: <jr813u$6oo$1_at_solani.org>
I have recently re-read the following Tom Kyte's article and something has struck me as very odd:
http://www.oracle.com/technetwork/issue-archive/2010/10-nov/ o60asktom-176254.html
if you update the parent table or delete from it, the child table will still be locked, but just for the duration of the UPDATE or DELETE. The lock is released after the statement is processed—not when you commit. This is better than in 1993, but the lock still exists.
I didn't notice this the first time around, but now it struck me: how can Oracle locks be released before the commit is issued? All manuals say that Oracle locks have the same lifespan as the transaction that issued them. If I assume that Tom Kyte's description is factually correct and that it wasn't a case of WUI (Writing Under Influence), then there are two possible ways of doing it:
- Making process have 2 contexts, just like on the OS level. A kernel
context would have its own "commit" which doesn't affect the user
context. That would be a bit hard to do, because the whole philosophy
of Oracle RDBMS is that "commit" is a global event which initiates many
things (LGWR write, flushing log buffer, releasing locks). An inteirnal
"lightweight commit" could really be considered an anomaly, not unlike
"the dark matter" in astronomy. It would also be the first step toward
fully multi-threaded oracle session. - The internal quick and dirty "unlock" mechanism which doesn't necessarily have anything to do with transactions. If that is the case, where else is this mechanism used? Can it be used by the users? Is there any way that user could initiate "unlock" of a resource? This "unlock" call is obviously being called as a part of the commit processing.
Does anyone know anything about this? BTW, I verified that this wasn't a case of WUI on my 11G instance. Tom's description is accurate. Unfortunately, he left out the explanation.
-- http://mgogala.freehostia.comReceived on Tue Jun 12 2012 - 13:17:34 CDT