Lock Table Oddity

From: <alexandr.antonov_at_barclays.com>
Date: Wed, 29 May 2013 14:53:09 +0100
Message-ID: <BB54F5B84B72C841B4D4CB2C1D98C20E019E36E928_at_LDNPCMMGMB03.INTRANET.BARCAPINT.COM>

Hi
I'm on Oracle 11.2.0.3 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 mode).

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?

Thanks

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: www.barclays.com/emaildisclaimer.

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.

---

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 29 2013 - 15:53:09 CEST