Re: Oracle Locking Question

Hi Steve,

May I quote from my book, Oracle Internal Services, p42.

"If ... the first waiter requires access to the resource is exclusive mode, then other sessions that require shared access must queue for the resource behind the first waiter, despite that their requests are compatible with the mode in which the resource is currently locked."

Your transaction 4 is waiting for a TM lock in X mode. Transactions 5 and 6 need TM locks in SX mode on the same resource. They must wait behind transaction 4, despite that transaction 4 does not yet hold any locks. Not much help, I know, but that is how enqueue locking is intended to operate.

Regards,
Steve Adams

http://www.ixora.com.au/

http://www.oreilly.com/catalog/orinternals/

http://www.christianity.com.au/

On Sun, 7 Nov 1999 18:43:59 -0600, "Steve Perry" <sperry_at_sprynet.com> wrote:

>I support an application (CA product) that uses the command "lock table
>xxxxx in exclusive mode" as part of its transaction.
>I'm having a blocking problem, but couldn't find anything in Oracle's books
>about.
>I understand how restrictive the command is when it gets a lock, but I'm not
>sure when it starts attaining the locks.
>Here's the scenario:
>
>transaction 1 does the lock table A... then commits and then starts another
>transaction and updates table A (row-share exclusive) and continues a very
>long transaction.
>
>tran2 and 3 come behind it and do inserts into table A and commit their work
>and are done. everything just fine.
>
>tran 4 comes in and runs the same type of trans action as transaction1 does,
>but can't get the lock on table A because transaction 1 still holds the
>rowshare-exclusive lock. Transaction 4 now is forced to wait until
>transaction 1 is done. Not good, but it makes sense. I look at top sessions
>and transaction 4 is NOT locking anything. He has a request to lock the
>table, but does NOT hold any lock.
>
>trans 5 and 6 come in and run the same type of transaction that 2 and 3 did
>from above (i.e. simple 1 row insert or update into table A). They are now
>blocked by transaction 4 who doesn't hold any locks... Now I'm confused. It
>looks to me as if the lock table command in exclusive by transaction 4
>decided to grab all the blocks that it could get and block transactions 5
>and 6 and anybody else from updating table A.
>
>If I kill transaction 4, then trans 5 and 6 complete just fine.
>
>Can anyone explain it tran 4 has no locks (v$lock view), why it's able to
>block 5 and 6???
>
>thanks for any help,
>Steve
>
>Here's some output that kind of shows, that may help.
>
>SQL>
>SQL> --query to find sessions waiting
>SQL> select sid
> 2 from v$lock
> 3 where request is not null
> 4 /
>
> SID
>----------
> 24 (tran 1)
> 23 (tran 5)
> 22 (tran 6)
> 15 (tran 4)
>
>SQL>
>SQL> -- Sessions holding/requesting locks with locked objects
>SQL> break on id1 on sid
>SQL> select lpad(' ',decode(a.request,0,0,3)) || a.sid ssid,a.id1
>,a.lmode,a.request,c.name
> 2 from sys.obj$ c,v$lock a
> 3 where a.id1 in (select id1
> 4 from v$lock
> 5 where lmode = 0 )
> 6 and c.obj# = a.id1
> 7 and a.type = 'TM'
> 8 order by a.id1,a.request, a.sid,c.name
> 9 /
>
>SSID ID1 LMODE REQUEST NAME
>---------- ---------- ---------- ---------- ------------------------------
>24 1987 3 0 HARPACKAGE (tran 1)
> 22 0 3 HARPACKAGE (tran
>5)
> 23 0 3 HARPACKAGE (tran
>6)
> 15 0 6 HARPACKAGE (tran
>4 blocker of 5 and 6) (lmode is 0 ???)
>
>
>
>
>
Received on Mon Nov 08 1999 - 00:05:43 CST