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;
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. |