Lock Table Oddity

  • From: <alexandr.antonov@xxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 29 May 2013 14:53:09 +0100

I'm on Oracle on Linux x64, and I've got a simple not partitioned 
table T1. Here is the chronology of events I'm running which lead to a weird 
(IMO) behaviour:

T0 | Session #681 - lock table t1 in row share mode nowait; => Table(s) Locked.
T1 | Session #761 - lock table t1 in share row exclusive mode nowait; => 
table(s) Locked.
T2 | Session #761 - Rollback; => Rollback complete.
T3 | Session #761 - lock table t1 in share row exclusive mode nowait; => 
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

At this point I can't lock table T1 in any not-null lock mode from any session! 
Also if I run a lock statement without nowait and query the v$lock view I can 
see that (a) session #861 (the first RS locking session) still holds RS lock 
(Mode 2), and (b) the second session is asking for an exclusive (Mode 6) TM 
lock on T1 rather then the one I requested (which could be even the same RS 

I've also noticed another peculiarity about it - it goes like that only if I do 
rollback at T2. If I do commit everything works as expected (ie at T3 I'm able 
to acquire the lock).

Does anyone know why it's happening this way?


Alex Antonov


This message is for information purposes only, it is not a recommendation, 
advice, offer or solicitation to buy or sell a product or service nor an 
official confirmation of any transaction. It is directed at persons who are 
professionals and is not intended for retail customer use. Intended for 
recipient only. This message is subject to the terms at: 

For important disclosures, please see: 
www.barclays.com/salesandtradingdisclaimer regarding market commentary from 
Barclays Sales and/or Trading, who are active market participants; and in 
respect of Barclays Research, including disclosures relating to specific 
issuers, please see http://publicresearch.barclays.com.



Other related posts:

  • » Lock Table Oddity - alexandr.antonov