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

From: Manuela Mueller <520040906697-0001_at_t-online.de>
Date: Thu, 20 Dec 2001 14:11:46 +0100
Message-ID: <3C21E392.6847BACE@t-online.de>

Hi Stephan,
followed your advice, snippet of PL/SQL, only lock and the loop:

set autocommit off...
LOCK TABLE manu.archive_test1 IN SHARE ROW EXCLUSIVE MODE NOWAIT;         

		FOR loop_count IN 1..10
		LOOP
		DBMS_LOCK.SLEEP(3); 		
			UPDATE archive_test1
			   SET time = SYSDATE - 30
			 WHERE time = c_time; 
		END LOOP;
		CLOSE arc_cur;
		COMMIT;

...

user sys on second sqlplus session during the updates: select s.username, s.sid, s.serial#, l.id1, l.id2, l.lmode, l.type, l.ctime
  from v$session s, v$lock l
 where s.sid = l.sid
   and s.username is not null;

USERNA SID SERIAL# ID1 ID2 LMODE TY CTIME
------ ---------- ---------- ---------- ---------- ---------- --

---

MANU 9 63 2960 0 5 TM 18
MANU 9 63 131103 1185 6 TX 15

In row with TX entry the values for ID1, ID2 are always the same during the updates.
You are perfectly right, I can only identify one TX lock during the transaction.
After the commit, there are no locks held, query returns no rows. I made a mistake with the first test, the user acquired the lock inside the loop and made an explicit commit inside the loop. So I ended with multiple transactions, each for one update.                                        

Best regards
Manuela Mueller

Stephan Bressler wrote:
>
> 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 - 07:11:46 CST