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
----------
10Now, 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.
posted by Bo's blog @ 9:22 AM
0 comments
