为何insert操作引起的锁模式与delete 、update操作不一样

显示全部楼层 · 2011-1-4 10:36:17

实验环境：
OS: win2003
DB: oracle 10.2.0.1

实验目的：
为何 delete 操作产生的锁等待模式为6，insert操作产生的锁等待模式为 4

实验步骤：

delete 操作
session 1
SQL> select distinct sid from v$mystat;
SID
----------
130
SQL> select * from tt;
ID
----------
1
2
然后删除一行数据
SQL> delete from tt where id=1;
已删除 1 行。
此时在session 2中删除同一行数据，该会话 hang住
session 2
SQL> select distinct sid from v$mystat;
SID
----------
116
SQL> select * from tt;
ID
----------
1
2
然后再打开另外一个新的会话 session 3，做如下查询
SQL> selectsid,type,id1,id2,lmode ,request,ctime,block from v$lock where sid in(130,116);
SID TYID1ID2LMODEREQUESTCTIMEBLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
116 TX 262155950
0
6 69
0
130 TM56645
0
3
0 86
0
116 TM56645
0
3
0 69
0
130 TX 262155950
6
0 86
1
从中可以看到delete操作跟 update操作一样，由于session 130和116删除同一行数据，因此先获得资源的 130会话正在
阻塞 116 会话，这个结果也可以从等待事件中得到验证
SQL>
SID EVENT
---------- --------------------------------------------------------------
116 enq: TX - row lock contention
130 SQL*Net message from client

insert 操作

在模拟 insert 操作的时候，需要在tt表的id字段加约束，否则不会形成阻塞的情况
在 session 1中
SQL> create table tt(id int primary key);
表已创建。
SQL> insert into tt values(1);
已创建 1 行。
SQL> insert into tt values(2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from tt;
ID
----------
1
2
然后再插入一条数据，不提交
SQL> insert into tt values(3);
已创建 1 行。
此时在 session 2中插入相同一条数据，发现session 2 被hang住
SQL> select distinct sid from v$mystat;
SID
----------
116
SQL> insert into tt values(3);
重新打开一个新的会话窗口 session 3，做如下查询
SQL> selectsid,type,id1,id2,lmode ,request,ctime,block from v$lock where sid in(130,116) order by sid,type;
SID TYID1ID2LMODEREQUESTCTIMEBLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
116 TM56648
0
3
0648
0
116 TX 589824 1013
6
0648
0
116 TX 393241955
0
4648
0
130 TM56648
0
3
0675
0
130 TX 393241955
6
0675
1
从结果可以看到，会话130和116 同时 insert 两条相同的记录违反了主键约束，因而产生了阻塞。
会话 116 被会话130阻塞，且正在请求模式为 4 (S)的锁，这种锁比update、delete、select for update
的锁级别要小。模式越小，锁的级别越小。

疑问：
为何delete操作的时候，每个session 只有一个表锁 TM 和一个事务锁 TX，
而insert 操作的时候，每个session 除了一个TX、一个TM锁以外，为何被阻塞的会话 116 还要请求一个 4 （共享锁）？

千问 · 2011-1-4 10:36:17

但是为何这里会出现模式为 4的锁呢？是因为我们在tt表上创建了 primary key 后，系统自动在表tt上创建唯一索引，
可以通过如下视图查看到唯一索引 SYS_C008084。而当我们在表上创建索引或执行ock table t in share mode
的时候，对应的表会加模式为4的共享锁。

SQL> select index_name,index_type,table_name,uniqueness from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES
--------------------------------------------------- ----------
SYS_C008084NORMAL
TT
UNIQUE

千问 · 2011-1-4 10:36:17

sxitsxit 发表于 2012-12-19 17:04
但是为何这里会出现模式为 4的锁呢？是因为我们在tt表上创建了 primary key 后，系统自动在表tt上创建唯 ...
那么现在又出现了一个新的问题

创建索引会自动加 lmode为 4 的锁，而4 对应的锁类型应当为 TM 才对，为何结果中的4 对应的是 TX 呢？
SQL> selectsid,type,id1,id2,lmode ,request,ctime,block from v$lock where sid in(130,116) order by sid,type;
SID TYID1ID2LMODEREQUESTCTIMEBLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
116 TM56648
0
3
0
648
0
116 TX 589824 1013
6
0
648
0
116 TX 393241955
0 4
648
0
130 TM56648
0
3
0
675 0
130 TX 393241955
6 0
675
1

千问 · 2011-1-4 10:36:17

本帖最后由面条s 于 2012-12-19 17:20 编辑
sxitsxit 发表于 2012-12-19 17:06
那么现在又出现了一个新的问题
为什么TX不能有mode 4？这个就是因为有主键，需要在index上获得共享锁，但是显示还是在table上

千问 · 2011-1-4 10:36:17

面条s 发表于 2012-12-19 17:18
为什么TX不能有mode 4？
不晓得
我只知道 TM 锁有 2--6的模式

千问 · 2011-1-4 10:36:17

面条s 发表于 2012-12-19 17:18
为什么TX不能有mode 4？这个就是因为有主键，需要在index上获得共享锁，但是显示还是在table上
显示在table？
说清楚些呗

千问 · 2011-1-4 10:36:17

本帖最后由 guoyJoe 于 2012-12-19 21:14 编辑
sxitsxit 发表于 2012-12-19 17:31
显示在table？
说清楚些呗
这个是４号锁是索引上的
实验：
会话１上操作
SQL> select sid from v$mystat where rownum=1;
SID
----------
1
SQL> create table tt(id int primary key);
Table created.
SQL> insert into tt values(1);
1 row created.
会话２上操作
SQL> select sid from v$mystat where rownum=1;
SID
----------
49
SQL> insert into tt values(1);
这里被阻塞了
会话３：
SQL> select * from v$lock where sid in(1,49);
ADDR
KADDR
　　　　　SID TYID1ID2LMODEREQUESTCTIMEBLOCK
---------------- ---------------- 　　　　　　　　---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000007A512E60 000000007A512EB8 49 TX65548 2336
0
4
5
0
00002AF62EBFF408 00002AF62EBFF468 49 TM77367
0
3
0 83
0
00002AF62EBFF408 00002AF62EBFF468
1 TM77367
0
3
0
8
0
0000000078DEA600 0000000078DEA678 49 TX 655373 2357
6
0
5
0
0000000078E67490 0000000078E67508
1 TX65548 2336
6
0
8
1
这里确实有个请求４号锁:
把ＴＸ上的id1=65548转化成undo
SQL> select trunc(65548/power(2,16)) as undo_blk#,bitand(65548,to_number('ffff','xxxx')) + 0 as slot# from dual;
UNDO_BLK#SLOT#
---------- ----------
1 12
会话４：
SQL> select * from v$transaction;
ADDR
XIDUSNXIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDRFLAG SPA REC NOU PTX NAME
PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN DSCN-B DSCN-WUSED_UBLKUSED_URELOG_IOPHY_IO CR_GETCR_CHANGE START_DATDSCN_BASEDSCN_WRAPSTART_SCN DEPENDENT_SCN XID
PRV_XIDPTX_XID
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------ ------------ ------------ ------------ ---------------- ---------- --- --- --- --- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ---------- ---------- ---------- ------------- ---------------- ---------------- ----------------
0000000078DEA600 10 13 2357
3 2429229
1 ACTIVE 12/19/12 20:44:58 5631705
0
3
3 2429
229
1 000000007AF97D68 3587 NONONONO
0 0
0
0
0
0
0
0
1
1 188 2 19-DEC-12
0
05631705
0 0A000D0035090000 0000000000000000 0000000000000000
0000000078E67490
1 12 2336
3 3539448 43 ACTIVE 12/19/12 20:46:13 5631693
0
2
3 3539
448 42 000000007A85DB50 3587 NONONONO
0 0
0
0
0
0
0
0
1
2
71 0 19-DEC-12
0
05631693
0 01000C0020090000 0000000000000000 0000000000000000
有两个事务：找到undo段号为１的事物
会话５：对３号文件的第3539号undo块做dump
*-----------------------------
* Rec #0x2bslt: 0x0cobjn: 77368(0x00012e38)objd: 77368tblspc: 7(0x00000007)
* Layer:10 (Index) opc: 22 rci 0x2a
Undo type:Regular undo Last buffer split:No
Temp Object:No
Tablespace Undo:No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Litl: xid:0x0002.01f.000009dd uba: 0x00c00457.013d.18

flg: C---lkc:0 scn: 0x0000.0055eecd
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1800172 block=0x01800173
(kdxlpu): purge leaf row
key :(3):02 c1 02
看出objn: 77368(0x00012e38)
SQL> select object_name,object_type from dba_objects where object_id=77368;
OBJECT_NAME　　　　　　OBJECT_TYPE
---------------　　　-------------------
SYS_C0011441　　　　　　　INDEX
SQL> select index_name from dba_indexes where table_name='TT';
INDEX_NAME
------------------------------
SYS_C0011441
可以看出请求的４号锁是索引上的，索引的根到枝叶是共享锁。

千问 · 2011-1-4 10:36:17

guoyJoe 发表于 2012-12-19 21:11
这个是４号锁是索引上的
实验：
会话１上操作

索引行加的锁是 TX锁对应的lmode 为 4
是否意味着TX 事务所也有 2----6 这几种模式？

千问 · 2011-1-4 10:36:17

sxitsxit 发表于 2012-12-20 09:32
索引行加的锁是 TX锁对应的lmode 为 4
是否意味着TX 事务所也有 2----6 这几种模式...
TX 最常见的模式有4和 6
其他的貌似不多见

千问 · 2011-1-4 10:36:17

sxitsxit 发表于 2012-12-20 09:32
索引行加的锁是 TX锁对应的lmode 为 4
是否意味着TX 事务所也有 2----6 这几种模式...
TX就只有4，6