dml with lmode=6 ?
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: dml with lmode=6 ?
今天跟09年的一个同事聊天，他去年去原厂了，问他在干吗，他说在处理一个客户的故障，
说是dml操作产生TM锁，而且lmode是6.
我们都知道正常情况下，dml产生的tm lmode应该是2或3，当然老版本的db有一些差异，详见下文：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
SQL> show user USER is "ROGER" SQL> create table DEPT (deptno number constraint pk_dept primary key,dname varchar2(10)); Table created. SQL> create table EMP (deptno number(2) constraint fk_deptno references dept(deptno), ename varchar2(20)); Table created. SQL> insert into DEPT values (1, 'killdb1'); 1 row created. SQL> insert into EMP values (1, 'killdb2'); 1 row created. SQL> commit; Commit complete. SQL> insert into DEPT values (2, 'killdb3'); 1 row created. SQL> insert into EMP values (2, 'killdb4'); 1 row created. SQL> commit; Commit complete. SQL> column owner_name format a10 SQL> column table_name format a25 SQL> column key_name format a15 SQL> column referencing_table format a15 SQL> column foreign_key_name format a15 SQL> SELECT 2 A.OWNER owner_name, 3 A.TABLE_NAME table_name, 4 A.CONSTRAINT_NAME key_name, 5 B.TABLE_NAME referencing_table, 6 B.CONSTRAINT_NAME foreign_key_name 7 FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B 8 WHERE 9 A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME and 10 B.CONSTRAINT_TYPE = 'R' 11 and a.table_name in('DEPT','EMP') 12 ORDER BY 1, 2, 3, 4; OWNER_NAME TABLE_NAME KEY_NAME REFERENCING_TAB FOREIGN_KEY_NAM ---------- ------------------------- --------------- --------------- --------------- ROGER DEPT PK_DEPT EMP FK_DEPTNO |
——– no index test
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
SQL> col object_name for a20 SQL> select object_id, object_name 2 from dba_objects 3 where object_name in ('EMP', 'DEPT') 4 and owner = 'ROGER'; OBJECT_ID OBJECT_NAME ---------- -------------------- 56005 DEPT 56007 EMP ---insert SQL> insert into DEPT values (3, 'killdb.com'); 1 row created. SQL> commit; Commit complete. SQL> insert into EMP values (3, 'killdb.com'); 1 row created. -----no commit SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK 3 where sid in 4 (select sid from v$session where audsid = userenv('SESSIONID')); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 138 TM 56005 0 2 0 0 138 TM 56007 0 3 0 0 138 TX 786441 245 6 0 0 ---update SQL> update dept set dname ='killdb2' where deptno=1; 1 row updated. SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK 3 where sid in 4 (select sid from v$session where audsid = userenv('SESSIONID')); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 138 TM 56005 0 3 0 0 138 TX 983049 254 6 0 0 SQL> rollback; Rollback complete. SQL> insert into DEPT values (4, 'baidu.com'); 1 row created. SQL> insert into EMP values (4, 'google.cn'); 1 row created. SQL> update dept set dname ='killdb2' where deptno=1; 1 row updated. SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK 3 where sid in 4 (select sid from v$session where audsid = userenv('SESSIONID')); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 138 TM 56005 0 3 0 0 138 TM 56007 0 3 0 0 138 TX 851977 239 6 0 0 ---delete SQL> rollback; Rollback complete. SQL> select * from dept; DEPTNO DNAME ---------- ---------- 1 killdb1 2 killdb3 3 killdb.com SQL> select * from emp; DEPTNO ENAME ---------- -------------------- 1 killdb2 2 killdb4 3 killdb.com SQL> delete from emp where DEPTNO=1; 1 row deleted. SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK 3 where sid in 4 (select sid from v$session where audsid = userenv('SESSIONID')); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 138 TM 56005 0 3 0 0 138 TM 56007 0 3 0 0 138 TX 1179670 249 6 0 0 |
针对子表外键列上无index的情况针对dml操作，产生的TM lmode为3的lock,
而且是子表和父表都会产生，这就非常不好了，容易产生死锁，这就是为什么
针对外键列需要建index的原因，下面来看看有index的情况。
———with index
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
---insert SQL> rollback; Rollback complete. SQL> create index ind_emp on emp (deptno, ename); Index created. SQL> insert into DEPT values (4, 'baidu.com'); 1 row created. SQL> commit; Commit complete. SQL> insert into EMP values (4, 'google.cn'); 1 row created. SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK 3 where sid in 4 (select sid from v$session where audsid = userenv('SESSIONID')); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 138 TM 56005 0 2 0 0 138 TM 56007 0 3 0 0 138 TX 917547 249 6 0 0 ----update SQL> rollback; Rollback complete. SQL> update dept set dname ='killdb2' where deptno=1; 1 row updated. SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK 3 where sid in 4 (select sid from v$session where audsid = userenv('SESSIONID')); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 138 TM 56005 0 3 0 0 138 TX 851990 239 6 0 0 ---delete SQL> rollback; Rollback complete. SQL> delete from emp where DEPTNO=1; 1 row deleted. SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK 3 where sid in 4 (select sid from v$session where audsid = userenv('SESSIONID')); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 138 TM 56005 0 2 0 0 138 TM 56007 0 3 0 0 138 TX 1114138 232 6 0 0 |
可以看到，针对父表的操作，不会在子表上产生锁，针对子表的操作会在
子表和父表都产生锁，但是有点不同，父表是lmode 2，子表是lmode 3，而无index的情况是：
子表和父表都是tm lmode 3.
mos上一篇文档描述了针对该情况的改变：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Changes in behaviour in different versions When your application has referential integrity and attempts to modify the child/parent table, Oracle will get additional locking on the parent/child table when there is no index on the foreign key. It is therefore recommended to add indexes for all foreign key defined in the database. When indexes are added on foreign keys columns of the child table, Oracle will only require Mode 3 Row-X (SX) locks on the modified table (parent or child) in Oracle 8.1.7 and 9.0.1. In Oracle9.2 onwards, 'mode 2 Row-S (SS)' locks are required on the linked table (child table when modifying the parent table, or parent table when modifying the child table). When indexes are not present on child table foreign keys columns, on top of the previous locking situation Oracle will require: a) In 8.1.7, 'mode 4 Share' locks on the child table when updating/deleting from the parent table. The lock mode even becomes a 'mode 5 S/Row-X (SSX)' lock when deleting from the parent table with a 'delete cascade' foreign key constraint.Those locks can't be disabled (ORA-00069) and are held during the full transaction time. b) In 9.0.1, Oracle only need those additional locks during the execution time of the UPDATE or DELETE. Those locks are downgraded to 'mode 3 Row-X (SX)' locks when the execution is finished. It is thus an improvement compared to Oracle 8.1.7. c.In 9.2.0, the downgraded 'mode 3 Row-X (SX)' locks are no longer required except when deleting from a parent table with a 'delete cascade' constraint. In higher versions, as the locks are not help for the entire duration of the transaction, but only during the actual execution of the UPDATE/DELETE, you may not be able to "catch" them. |
上面我还是还是没有模拟出产生TM lmode=6的情况，下面继续：
正常情况下，对于TM 级别产生lmode 6的锁，那么肯定是针对该表有DDL比如alter table或其他，
这里我模拟另外一种：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 |
SQL> create table test_pdml 2 storage (initial 1M next 1M pctincrease 0) 3 nologging 4 as select * from dba_objects where 1=2; Table created. SQL> alter table test_pdml parallel (degree 4); Table altered. SQL> SQL> insert /*+ append parallel(test_pdml,4) */ 2 into test_pdml 3 select * from dba_objects; 51136 rows created. SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK 3 where sid in 4 (select sid from v$session where audsid = userenv('SESSIONID')); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 138 PS 1 0 4 0 0 138 PS 1 1 4 0 0 138 PS 1 2 4 0 0 138 PS 1 3 4 0 0 134 PS 1 0 4 0 0 145 PS 1 1 4 0 0 159 PS 1 2 4 0 0 135 PS 1 3 4 0 0 138 TM 56084 0 6 0 0 134 TS 6 25166477 4 0 0 145 TS 6 25170189 4 0 0 SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 135 TS 6 25170317 4 0 0 159 TS 6 25170445 4 0 0 138 TS 6 25170445 4 0 0 138 TS 6 25170189 4 0 0 138 TS 6 25166477 4 0 0 138 TS 6 25170317 4 0 0 135 TX 786455 245 6 0 0 145 TX 1179664 249 6 0 0 138 TX 917540 249 6 0 0 134 TX 851987 239 6 0 0 159 TX 1310723 248 6 0 0 22 rows selected. SQL> select object_id,owner,object_name from dba_objects where object_name=upper('test_pdml'); OBJECT_ID OWNER OBJECT_NAME ---------- ------------------------------ -------------------- 56084 ROGER TEST_PDML SQL> commit; Commit complete. SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK 3 where sid in 4 (select sid from v$session where audsid = userenv('SESSIONID')); no rows selected SQL> update /*+ parallel(test_pdml,4) */ test_pdml set owner='killdb.com'; 51136 rows updated. SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK where sid in 3 4 (select sid from v$session where audsid = userenv('SESSIONID')); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 138 PS 1 0 4 0 0 138 PS 1 1 4 0 0 138 PS 1 2 4 0 0 138 PS 1 3 4 0 0 136 PS 1 0 4 0 0 135 PS 1 1 4 0 0 159 PS 1 2 4 0 0 134 PS 1 3 4 0 0 138 TM 56084 0 6 0 0 136 TM 56084 0 1 0 0 136 TM 56084 1 4 0 0 SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 135 TM 56084 0 1 0 0 135 TM 56084 1 4 0 0 159 TM 56084 0 1 0 0 159 TM 56084 1 4 0 0 134 TM 56084 0 1 0 0 134 TM 56084 1 4 0 0 136 TX 983061 254 6 0 0 134 TX 1376273 247 6 0 0 135 TX 1048590 244 6 0 0 138 TX 1245196 252 6 0 0 159 TX 1114154 232 6 0 0 22 rows selected. SQL> commit; Commit complete. SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK 3 where sid in 4 (select sid from v$session where audsid = userenv('SESSIONID')); no rows selected SQL> delete /*+ parallel(test_pdml,4) */ from test_pdml where object_id <20001; 19454 rows deleted. SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK 2 FROM v$LOCK 3 where sid in 4 (select sid from v$session where audsid = userenv('SESSIONID')); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 138 PS 1 0 4 0 0 138 PS 1 1 4 0 0 138 PS 1 2 4 0 0 138 PS 1 3 4 0 0 145 PS 1 0 4 0 0 134 PS 1 1 4 0 0 159 PS 1 2 4 0 0 135 PS 1 3 4 0 0 138 TM 56084 0 6 0 0 145 TM 56084 0 1 0 0 145 TM 56084 1 4 0 0 SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 134 TM 56084 0 1 0 0 134 TM 56084 1 4 0 0 159 TM 56084 0 1 0 0 159 TM 56084 1 4 0 0 135 TM 56084 0 1 0 0 135 TM 56084 1 4 0 0 135 TX 917535 249 6 0 0 134 TX 1310729 248 6 0 0 138 TX 851997 239 6 0 0 145 TX 1179667 249 6 0 0 159 TX 786442 245 6 0 0 22 rows selected. |
我们可以看到针对parallel dml 会产生TM lmode 为6的lock。
Leave a Reply
You must be logged in to post a comment.