Locks in Oracle

copied from (http://www.adp-gmbh.ch/ora/concepts/lock.html)

Locks in Oracle

create table lck (a number, b number);

insert into lck values (1,2);
insert into lck values (2,4);
insert into lck values (3,6);
insert into lck values (4,8);
insert into lck values (5,3);
insert into lck values (6,5);
insert into lck values (7,7);

commit;

We use two sessions (distinguishable by two colors and being on the left or right side) to investigate Statement-level read consistency, and a third to select from v$lock

First, we find the session id of the two participating sessions:

SQL> select sid from v$session where audsid=userenv('SESSIONID');

       SID
----------
        14

SQL>select sid from v$session where audsid=userenv('SESSIONID');

       SID
----------
        10

Now, we're inserting a row in the first session (sid=14).

SQL> insert into lck values (1000,1001);

1 row created.

SQL> select * from lck;

         A          B
---------- ----------
         1          2
         2          4
         3          6
         4          8
         5          3
         6          5
         7          7
      1000       1001

8 rows selected.

How does that influence v$lock?

SQL> select sid,type,id1,lmode,request from v$lock where sid in (10,14);

       SID TY        ID1      LMODE    REQUEST
---------- -- ---------- ---------- ----------
        14 TX     262153          6          0
        14 TM       4145          3          0

Session 14 (the one that inserted a row) has obviously aquired two locks (request = 0). One of these locks is a Transaction Lock (type=TX), the other is a DML or Table Lock (type=TM). Mode 3 means: Row Exclusive which acutally makes sense. Now, we can use obj$ to verify if the TM Lock is indeed put on the table LCK:

SQL> select name from sys.obj$ where obj# = 4145 ;

NAME
------------------------------
LCK

What does the 2nd session see if it queries LCK?

SQL>select * from lck;

         A          B
---------- ----------
         1          2
         2          4
         3          6
         4          8
         5          3
         6          5
         7          7

7 rows selected.

The 1st session has not yet commited (or rollbacked) its session, so the changes are not visible to other sessions.

Now, let's have the first session insert another row.

SQL> insert into lck values (1001,1000);

1 row created.

We'd expect v$lock to have an row more (for this 2nd inserted row). But never believe your feelings...

SQL> select sid,type,id1,lmode,request from v$lock where sid in (10,14);

       SID TY        ID1      LMODE    REQUEST
---------- -- ---------- ---------- ----------
        14 TX     262153          6          0
        14 TM       4145          3          0

Didn't much change, did it? Now, the 2nd session updates a row:

SQL>update lck set a=2000,b=2001 where a=1;

1 row updated.

SQL>select * from lck;

         A          B
---------- ----------
      2000       2001
         2          4
         3          6
         4          8
         5          3
         6          5
         7          7

7 rows selected.

And v$lock?

SQL> select sid,type,id1,lmode,request from v$lock where sid in (10,14);

       SID TY        ID1      LMODE    REQUEST
---------- -- ---------- ---------- ----------
        10 TX     327698          6          0
        10 TM       4145          3          0
        14 TX     262153          6          0
        14 TM       4145          3          0

SQL>insert into lck values (2001,2000);

1 row created.

SQL>select * from lck;

         A          B
---------- ----------
      2000       2001
         2          4
         3          6
         4          8
         5          3
         6          5
         7          7
      2001       2000

8 rows selected.

What happens, if the first session wants to update a row that was already updated (but not yet commited) by another session? The first session tries to do exactly that (the row in which a=1)

SQL> select * from lck;

         A          B
---------- ----------
         1          2
         2          4
         3          6
         4          8
         5          3
         6          5
         7          7
      1000       1001
      1001       1000

9 rows selected.

SQL> update lck set a=1002,b=1003 where a=1;

The session hangs until the session that has put a lock on the row in question commits (or rollbacks). This waiting is recorded in v$session_wait:

SQL> select event, seconds_in_wait, sid from v$session_wait where sid in (10,14);

EVENT                                                            SECONDS_IN_WAIT        SID
---------------------------------------------------------------- --------------- ----------
enqueue                                                                     1593         14
SQL*Net message from client                                                 2862         10

v$session_wait tells even how long (in secondes) the session waited. Now, let the 2nd session commit:

0 rows updated.

SQL>

SQL>commit;

Commit complete.

Note, this can be confusing. When the 1st session selected * from lck, it defenitively saw a row where a=1 but after seemingly updating it, it wasn't actually updated.

What do these sessions now see, if they both do a select *?

SQL> select * from lck;

         A          B
---------- ----------
      2000       2001
         2          4
         3          6
         4          8
         5          3
         6          5
         7          7
      1000       1001
      1001       1000
      2001       2000

SQL>select * from lck;

         A          B
---------- ----------
      2000       2001
         2          4
         3          6
         4          8
         5          3
         6          5
         7          7
      2001       2000

While the 1st session sees, what the 2nd session commited, the 2nd session does not see the uncommited changes of the first session. If the 1st session had been Transaction-level read consistent, it would not see any changes until it commits.