删除一个表，为什么会锁其他的表

haiwen-1312

是不是做了触发器的原因呢？

newkid

在子表上应该对外键列建索引，否则删除父表会将子表全表锁住。

〇〇

newkid 发表于 2014-3-18 01:47
在子表上应该对外键列建索引，否则删除父表会将子表全表锁住。

我怎么忘记了

lhqmccn

lastwinner 发表于 2014-3-17 18:58
delete之前的insert有无commit？

都是在做完操作后，不commit，去查看是否锁表的

lhqmccn

newkid 发表于 2014-3-18 01:47
在子表上应该对外键列建索引，否则删除父表会将子表全表锁住。

我测试的是，外键列有没有索引，都会锁表

TX__wangbeng

1. 版本11.2.0.3,从实验来看，在外键不建立索引的情况下只有插入操作会对子表添加LMODE=3 的TM锁。
   
2. 而如果外键建立索引，则增删改都会添加LMODE=3的TM锁。
   
3. SQL> create table emp as select * from scott.emp;
   
4. 
   
5. Table created.
   
6. 
   
7. SQL> create table dept as select * from scott.dept;
   
8. 
   
9. Table created.
   
10. 
    
11. SQL> alter table dept add constraint pk_dept primary key (deptno);
    
12. 
    
13. Table altered.
    
14. 
    
15. SQL> alter table emp add constraint fk_emp foreign key (deptno) references dept(deptno);
    
16. 
    
17. Table altered.
    
18. 
    
19. SQL> select distinct sid from v$mystat;
    
20. 
    
21.        SID
    
22. ----------
    
23.         54
    
24. 
    
25. SQL> insert into dept values (50,'wangbeng','wangbeng');
    
26. 
    
27. 1 row created.
    
28. 
    
29. SQL> select * from v$lock where sid = '54';
    
30. 
    
31. ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
    
32. ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
    
33. 0000000077CD3528 0000000077CD3580         54 AE          100          0          4          0        198          0
    
34. 00002AED0C2B9198 00002AED0C2B91F8         54 TM        76910          0          3          0          9          0
    
35. 00002AED0C2B9198 00002AED0C2B91F8         54 TM        76911          0          3          0          9          0
    
36. 00000000777AE960 00000000777AE9D8         54 TX      2424844        298          6          0          9          0
    
37. 
    
38. SQL> commit;
    
39. 
    
40. Commit complete.
    
41. 
    
42. SQL> select * from v$lock where sid = '54';
    
43. 
    
44. ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
    
45. ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
    
46. 0000000077CD3528 0000000077CD3580         54 AE          100          0          4          0        214          0
    
47. 
    
48. SQL> delete from dept where deptno = 50;
    
49. 
    
50. 1 row deleted.
    
51. 
    
52. SQL> select * from v$lock where sid = '54';
    
53. 
    
54. ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
    
55. ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
    
56. 0000000077CD3528 0000000077CD3580         54 AE          100          0          4          0        239          0
    
57. 00002AED0C2B9198 00002AED0C2B91F8         54 TM        76911          0          3          0          3          0
    
58. 00000000777AE960 00000000777AE9D8         54 TX      2293787        300          6          0          3          0
    
59. 
    
60. SQL> rollback;
    
61. 
    
62. Rollback complete.
    
63. 
    
64. SQL> update dept set deptno = 70 where deptno = 50;
    
65. 
    
66. 1 row updated.
    
67. 
    
68. SQL> select * from v$lock where sid = '54';
    
69. 
    
70. ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
    
71. ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
    
72. 0000000077CD3528 0000000077CD3580         54 AE          100          0          4          0        276          0
    
73. 00002AED0C31EF88 00002AED0C31EFE8         54 TM        76911          0          3          0          3          0
    
74. 00000000777AE960 00000000777AE9D8         54 TX      2162691        292          6          0          3          0
    
75. 
    
76. SQL> create index ind_emp_deptno on emp(deptno);
    
77. 
    
78. Index created.
    
79. 
    
80. SQL> insert into dept values (50,'wangbeng','wangbeng');
    
81. 
    
82. 1 row created.
    
83. 
    
84. SQL> select * from v$lock where sid = '54';
    
85. 
    
86. ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
    
87. ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
    
88. 0000000077CD3528 0000000077CD3580         54 AE          100          0          4          0        321          0
    
89. 00002AED0C2B50B8 00002AED0C2B5118         54 TM        76910          0          3          0          5          0
    
90. 00002AED0C2B50B8 00002AED0C2B5118         54 TM        76911          0          3          0          5          0
    
91. 00000000777DB9E8 00000000777DBA60         54 TX      2555936        287          6          0          5          0
    
92. 
    
93. SQL> rollback;
    
94. 
    
95. Rollback complete.
    
96. 
    
97. SQL> delete from dept where deptno = 50;
    
98. 
    
99. 0 rows deleted.
    
100. 
     
101. SQL> delete from dept where deptno = 70;
     
102. 
     
103. 1 row deleted.
     
104. 
     
105. SQL> select * from v$lock where sid = '54';
     
106. 
     
107. ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
     
108. ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
     
109. 0000000077CD3528 0000000077CD3580         54 AE          100          0          4          0        449          0
     
110. 00002AED0C5B1E18 00002AED0C5B1E78         54 TM        76910          0          3          0         24          0
     
111. 00002AED0C5B1E18 00002AED0C5B1E78         54 TM        76911          0          3          0         24          0
     
112. 00000000777DB9E8 00000000777DBA60         54 TX      2228255        302          6          0          3          0
     
113. 
     
114. SQL> rollback;
     
115. 
     
116. Rollback complete.
     
117. 
     
118. SQL> delete from dept where deptno = 70;
     
119. 
     
120. 1 row deleted.
     
121. 
     
122. SQL> select * from v$lock where sid = '54';
     
123. 
     
124. ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
     
125. ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
     
126. 0000000077CD3528 0000000077CD3580         54 AE          100          0          4          0        461          0
     
127. 00002AED0C2B50B8 00002AED0C2B5118         54 TM        76910          0          3          0          2          0
     
128. 00002AED0C2B50B8 00002AED0C2B5118         54 TM        76911          0          3          0          2          0
     
129. 00000000777DB9E8 00000000777DBA60         54 TX      2621471        303          6          0          2          0
     
130. 
     
131. SQL> rollback;
     
132. 
     
133. Rollback complete.
     
134. 
     
135. SQL> update dept set deptno=80 where deptno = 70;
     
136. 
     
137. 1 row updated.
     
138. 
     
139. SQL> select * from v$lock where sid = '54';
     
140. 
     
141. ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
     
142. ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
     
143. 0000000077CD3528 0000000077CD3580         54 AE          100          0          4          0        516          0
     
144. 00002AED0C5B1E18 00002AED0C5B1E78         54 TM        76910          0          3          0          3          0
     
145. 00002AED0C5B1E18 00002AED0C5B1E78         54 TM        76911          0          3          0          3          0
     
146. 00000000777DB9E8 00000000777DBA60         54 TX      2293772        300          6          0          3          0
     
147. 
     
148. SQL> rollback;
     
149. 
     
150. Rollback complete.
     
151. 
     
152. SQL> select * from v$lock where sid = '54';
     
153. 
     
154. ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
     
155. ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
     
156. 0000000077CD3528 0000000077CD3580         54 AE          100          0          4          0        525          0

复制代码

你的是哪个版本。

lhqmccn

TX__wangbeng 发表于 2014-3-18 09:12
你的是哪个版本。

我们一样的，11.2.0.3.0

windtalker_cs

newkid 发表于 2014-3-18 01:47
在子表上应该对外键列建索引，否则删除父表会将子表全表锁住。

LZ测试的结果是DELETE子表，把父表锁住了。。。。。

windtalker_cs

这个加锁可以理解，因为子表是删除，父表如果是更新，这个锁应该不起作用，父表如果删除相关的记录(子表正在删除的记录引用了这个父键），那肯定是要锁住等待的。不要只看V$表的数据。

lastwinner

lhqmccn 发表于 2014-3-18 09:01
都是在做完操作后，不commit，去查看是否锁表的

你的意思是，对我的问题“delete之前的insert有无commit？”的回答是“无”？
那么主表上肯定会有锁存在啊