DML会产生行锁，只有该行的锁解开后才可继续对改行进行其他的DML，若新的DML和旧的DML操作的行相同，且旧DML以把行改变，则新DML不起作用
准备工作：
SQL>create table lck (a number, b number);
SQL>insert into lck values (1,1);
SQL>insert into lck values (2,2);
SQL>insert into lck values (3,3);
SQL>insert into lck values (4,4);
SQL>insert into lck values (5,5);
SQL>insert into lck values (6,6);
SQL>insert into lck values (7,7);
SQL>commit;
实验过程：
| 步 骤 | Session 14 | Session 10 | 说明 |
| 1 | SQL> select sid from v$session
where audsid=userenv('SESSIONID');
SID
----------
14 | SQL> select sid from v$session
where audsid=userenv('SESSIONID');
SID
----------
10 | 获得当前session的SID |
| 2 | SQL> insert into lck values (1000,1001);
1 row created. | | 未提交 |
| 3 | 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 | | 因为Session14的DML未提交，因此在v$lock里产生2个锁，一个为：transaction lock(TX)，另一个为:DML/table lock(TM).LMODE=3代表：行排它.LMODE=6代表：对象排它 |
| 4 | SQL> insert into lck values (1001,1000);
1 row created. | | 未提交 |
| 5 | 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 | SQL> select * from lck;
A B
---------- ----------
1 2
2 4
3 6
4 8
5 3
6 5
7 7
7 rows selected. | v$lock中并不会因为对该表的再一次DML且未提交而新生成锁 |
| 6 | | SQL>update lck set a=2000,b=2001
where a=1;
1 row updated. | 未提交 |
| 7 | 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> 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 | v$lock中对另外session10的DML产生了锁 |
| 8 | SQL>update lck set a=4000,b=2001
where a=1; | | session10发出更新a=1行的SQL后，session14也发出更新a=1行的SQL，则后者的SQL悬在那里，无法结束 |
| 9 | SQL> select event, seconds_in_wait, sid from v$session_wait where sid in (10,14);
EVENT SECONDS_IN_WAIT SID
---------------------------------------------------------------- --------------- ----------
enq: TX - row lock contention 1593 14
SQL*Net message from client 2862 10 | v$session_wait中可以看到哪个session在等待，等待原因和已经等待的时间 |
| 10 | 0 rows updated.
| SQL>commit;
Commit complete. | 只有在session10提交该DML后,session14才执行完，但更新为0行 |
| 11 | 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> 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. | |
笔记来源：
http://www.adp-gmbh.ch/ora/concepts/lock.html
posted on 2006-08-24 13:14
七匹狼 阅读(1843)
评论(2) 编辑 收藏 所属分类:
oracle