Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Locking Question

Re: Oracle Locking Question

From: Alan Shein <alanshein_at_erols.com>
Date: Mon, 8 Nov 1999 10:07:41 -0500
Message-ID: <806ouh$dj2$1@autumn.news.rcn.net>


This is correct, and it makes sense if you look at it from #4's viewpoint. If the others in line behind were permitted to leapfrog, poor #4 may not ever execute, depending on what is waiting behind.

Steve Perry <sperry_at_sprynet.com> wrote in message news:805664$aui$1_at_nntp3.atl.mindspring.net...
> 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 - 09:07:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US