Lock Table Oddity
- From: <alexandr.antonov@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 May 2013 14:53:09 +0100
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.
_______________________________________________
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Lock Table Oddity - alexandr.antonov
