Re: v$lock columns, info about rollback slot??

Hi Manuela,

I think your interpretation of the locks and the time they exist is slightly wrong. TX locks only exists during the existance of a transaction. They are created on the very first DML statement and last until a commit or rollback is issued.

I believe that you see the TX lock while your PL/SQL did the update, but the commit wasn't issued yet.

Try your test with 2 sqlplus sessions (with "set autocommit off"). Do your update in session 1, check for TX lock in session 2 (should exist). Commit in session 1, check in session 2 (TX should be gone). There is one and only on TX lock for each transaction regardless the number of DML operations it performed.

Regards
Stephan

> Hello Stephan,
> thanks a lot for your reply, was very helpful.
> I set up another test situation: the user locked his table and made
> updates out of PL/SQL loop with commit and sleep() to slow things
> artificially down. In the meantime user sys run the query on v$lock.
> output:
> USERNA SID SERIAL# ID1 ID2 LMODE TY
> CTIME
> ------ ---------- ---------- ---------- ---------- ---------- --
> ----------
> ADDR KADDR
> -------- --------
> MANU 9 63 2960 0 5 TM
> 13 <--TM, before commit
> 51DC7E5C 51DC7E70
>
> MANU 9 63 131104 1232 6
> TX 0 <--TX, update is committed!
> 0958692C 095869F8
>
Received on Thu Dec 20 2001 - 02:49:37 CST