没有必胜的秘籍，没有方程式遵循~~ 要赢~~只有全身心的投入！

晶晶实验三DDL和DML操作都加什么样的TM锁篇

上一篇 / 下一篇 2008-02-16 17:41:50 / 个人分类：晶晶oracle实验系列

查看( 831 ) / 评论( 26 )

大家都知道TM锁共有6种模式,最常见的是3(插入,更新,删除);4(创建索引时);5(部分DDL操作如:删除列)等等,这些锁比较容易看到,不再详述实验经过.有些操作速度过块,如:Truncate和Drop等.这时不容易看到锁的状态,下面我做了个小实验来看清楚这些速度较快的命令持有什么样的锁. 例如:Truncate和Drop

因为命令执行速度快,只有靠脚本来快速观察.如下:
create or replace procedure my_cursor is
  cursor aa is select lmode from v$lock where type='TM' and sid=您的会话SID;
  x number(3):=9;
begin
  for i in 1..3000 loop
open aa;
fetch aa into x;
dbms_output.put_line(i||'-'||x);
close aa;
x:=9;
  end loop;
end;
/
**如果X为空的话,下次抓取值不变,所以每次抓取结束后为X附上一个锁中不可能出现的值9;

步一:脚本显示内容过多,会造成显示缓冲区溢出,先把缓冲区设大点;
SQL> set serveroutput on size 100000;

步二:为了更清楚的观察,把结果输送到文件中来查看结果;
SQL> spool e:\oracle\aa.txt

步三:执行脚本
SQL> exec my_cursor;

步四:在另一个会话中去Truncate表
SQL> truncate table jj_2;

表被截断。
----等待第一个会话中的执行完毕----
步五:把执行结果输送到文件中
SQL> spool off;

查看aa.txt 发现    　循环次数　　　锁状态
　　　　　　　　　　---------------    -------------
                     1--205    显示为9 无
   206--375    显示为6 传说中的6-X锁
   376--结束一直为9 6 锁已经释放

*****以上结果我是在10G中做的实验,同样的我又在9i中试了试,发现略有不同*****
结果如下:
         1--93    显示为9
                     94--126    显示为6
   127--217    显示为2
   218--249    显示为3
   250--266    显示为6
   267--结束一直为9

小结:在10G中只加了6锁,而9i中是236混杂出现,看来10G的截断操作比9i要简单明了,从算法上进步了不少哟~~  !^_^!

另附：    drop table                在10G中为 6锁，9i中是6 3 混合
   create table                在10G中为 3锁，
   create index             在10G中为 4锁，表共享锁，根据兼容矩阵表示，4号锁和4号锁是相兼容的，但是我同时开两个会话创建索引，还是有等待，发现是library cache lock在等待。因为创建索引时，会在表上加独占的library cache lock。
create index on line    在10G中为 2，4 锁其中2锁时间较长。2锁只和6锁不兼容，其他都兼容。其中4锁时间较短，只占41次循环。证明连机创建索引对DML操作影响不大,处
了41次 4锁之外,大部分时间是可以执行DML的.

其他执行速度比较快的操作,查看锁步骤类似,可以用上例一样的方式实验...TX锁相关事务,放到后面回滚段相关章节再发...

全部脚印 不留脚印留下脚印:

论坛模式推荐收藏分享给好友推荐到圈子管理

TAG:

shiri512003 发布于2008-02-16 17:43:09: first

eagle_fan 发布于2008-02-16 17:46:46: 你可以用10704 trace event，显示更为清楚准确ITPUB个人空间Zp*HA7M!i2C2T

.P7q_RF"D:M7Bpb:T看我以前写的文章：
2[0{;eC$b
NZ-I'wspace.itpub.net
http://www.dbafan.com/blog/?p=49

棉花糖ONE发布于2008-02-16 17:58:02: mm的想法很好

烟囱发布于2008-02-16 18:05:49: mm把这每天一课进行下去.
我等收获不小哦.h4|7kt zf0{]~%h"t%\

谢谢!

SingleLove 发布于2008-02-16 19:03:35: 不错，顶一个

oracle_li发布于2008-02-16 21:17:21: 大力支持下MM!

Alienovo 发布于2008-02-16 21:19:56: 学习一下

sydongsun发布于2008-02-17 09:35:55: 内容不错的。

stronghearted 发布于2008-02-17 10:51:04: good job.ITPUB个人空间)Nlvl,SW6~
ITPUB个人空间+gEf WLRY!_f
思路不错！

oradbHome 发布于2008-02-17 19:16:13: 附加上 lock modespace.itpub.net@&JdWxvJ
0 - none
1 - null (NULL)
NyXU
p+R1h
db\Y2 - row-S (SS)
!n1lzh,JD6o+T\p3 - row-X (SX) Fnk2k(z3F"U]et
4 - share (S)
5 - S/Row-X (SSX) ITPUB个人空间X#_4I\ `9|'p
6 - exclusive (X)
就不用在去查数据字典了^_^

晶晶小妹发布于2008-02-17 19:48:06: 在各种文档中，对于这６种模式的锁，英文称呼个不相同，我把他总结了一下，如下：
APH9`/|9C_fE0 - none
4r(U"K
g5?\+q1 - null (NULL)
m!y8ZU8g$}G4bO:Hspace.itpub.net2 - row-S (SS) 也简称rs锁
^)v#r\e'v8R3 - row-X (SX) 　　　rx
Sg;C0s!dspace.itpub.net4 - share (S) space.itpub.net|9@$E,Vx,v*du/~
5 - S/Row-X (SSX) srx
6 - exclusive (X)

其中２，３锁属于意向锁，关于这一点可以参考　萨师煊　和　王珊　的＜数据库系统概论＞这本书的并发控制那一张．
L;~?9J;{+G,jspace.itpub.net
[ 本帖最后由晶晶小妹于 2008-2-17 19:59 编辑 ]

foxmile 发布于2008-02-17 19:59:14: 看了一下，锁的一些概念还不太明白。

晶晶小妹发布于2008-02-17 20:03:20: QUOTE:
原帖由 foxmile 于 2008-2-17 19:59 发表 6i"G!J'g^+Ad/D:SL1~
看了一下，锁的一些概念还不太明白。
;Q)M_1_G9j2FdITPUB个人空间ITPUB个人空间[cRf
j
哪些不明白？？

foxmile 发布于2008-02-17 20:26:26: 什么情况下会死锁。如何找到引发死锁的应用。

晶晶小妹发布于2008-02-18 12:45:23: QUOTE:
原帖由 foxmile 于 2008-2-17 20:26 发表 'ReW3p6ko
K
什么情况下会死锁。如何找到引发死锁的应用。
pG$gfj
s4W/j
dn:g_^b"h?)B
死锁发生之后，通常有一个会话会被中断，在告警日志里面会记录一些信息．

foxmile 发布于2008-02-18 12:50:35: 恩。了解。多谢！

晶晶小妹发布于2008-02-18 13:04:22: QUOTE:
原帖由 foxmile 于 2008-2-17 20:26 发表
什么情况下会死锁。如何找到引发死锁的应用。
P4m*NTq5K$v

T8m]eEhob*V做了一个简单的实验：Rt4j8a%Q3|{
会话10：
|1N3QQ9{1I]sid=10 pid=11> update aa_1 set name=lower(name) where id=1;
;iC:q6XG已更新 1 行。Y5l tx,D,e+u
sid=10 pid=11> update aa_1 set name=lower(name) where id=2;

会话12
sid=12 pid=12> update aa_1 set name=lower(name) where id=2;iT)`|i |.^9V'b]8|
已更新 1 行。
sid=12 pid=12> update aa_1 set name=lower(name) where id=1;
@9~
N!p.|tm$uITPUB个人空间~d/`n:@X
会话10的命令先发布，因此，稍等之后，会话12会报出如下错误：
^%Z8G!m([0zn
?%Zdupdate aa_1 set name=lower(name) where id=1
g
lw C`p    *u2t#\ n-D)?^
第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁
'YRN3Uz+Ispace.itpub.net
\-e]%N*x&r-D~f这时，查找告警日志，可以发现如下死锁信息：
F9F^+O)y%IcVITPUB个人空间Mon Feb 18 12:43:51 2008space.itpub.net(_
E x6V#trfz
ORA-000060: Deadlock detected. More info in file e:\oracle\admin\mytwo\udump\mytwo_ora_348.trc.
(O3]FH0i$[5ph{VL6x1xz!][i
按照指示，查看跟踪文件：
v7pp:B}space.itpub.net                      ---------Blocker(s)--------  ---------Waiter(s)---------E8F)S5XZ4DQ]+k
Resource Name       process session holds waits  process session holds waitsE ok\;b3W \*d
TX-0003000b-000008e0       12    12    X          11    10          X
TX-00040009-00000991       11    10    X          12    12          X
YB^N`^
G#`!eITPUB个人空间session 12: DID 0001-000C-00000002 session 10: DID 0001-000B-00000002
session 10: DID 0001-000B-00000002 session 12: DID 0001-000C-00000002
?r|N2U{dspace.itpub.netRows waited on:
'I-KWmfHnrdITPUB个人空间Session 10: obj - rowid = 000019ED - AAAB7zAAFAAAV02AAA
kT,{NU
u(DuY3szITPUB个人空间  (dictionary objn - 6637, file - 5, block - 89398, slot - 0)space.itpub.net?3_'Il1?m
Session 12: obj - rowid = 000019ED - AAAB7zAAFAAAV00AAA
xF0pAX  (dictionary objn - 6637, file - 5, block - 89396, slot - 0)
8les%p5{+}$iTITPUB个人空间Information on the OTHER waiting sessions:
Ae
DJt^Session 10:x_B"Q?\
  pid=11 serial=71 audsid=5634 user: 30/SCOTT
  O/S info: user: JB\JJBOBO1, term: JB, ospid: 464:440, machine: WORKGROUP\JB

ix
W!@Lu          program: sqlplus.exe
  application name: SQL*Plus, hash value=3669949024IyUB:_']+ak2zaU
  Current SQL Statement:wh2N
a3[ S%NV
  update aa_1 set name=lower(name) where id=2
7t{3yS"?End of information on OTHER waiting sessions.space.itpub.netTF(S!T7ZfRCU,a
===================================================
下面还有PROCESS STATE，不再列出。

}~S#O+}6_'JasITPUB个人空间根据跟踪文件中的信息，应该足以找到死锁发生的位置。

Hero--008 发布于2008-02-18 13:29:58: 如此学习态度，还有什么东西学不会的呢~~榜样阿！看齐~~

foxmile 发布于2008-02-19 14:09:07: QUOTE:
原帖由 晶晶小妹 于 2008-2-18 13:04 发表
_%S4gDiNsQoy r~y
O"Ug

做了一个简单的实验：ITPUB个人空间@
k
H5nei|
会话10：
n$l#CgA'M/g.@~sid=10 pid=11> update aa_1 set name=lower(name) where id=1;#I?P0j7SRf7lb
已更新 1 行。space.itpub.net:D-^u7E!C&Y
sid=10 pid=11> update aa_1 set name=lower(name) where id=2;ITPUB个人空间WaR]6hsn5d

2lV8cPB+~oi]*TNITPUB个人空间会话12
`mAjv{ @xusid=12 pid=12> update aa_1 set name=lower(name) where id=2;
已更新 1 行。ITPUB个人空间K1oe;taw
sid=12 pid=12> update aa_1 set name=lower(name) where id=1;
q.T'}$R5\Q A
会话10的命令先发布，因此，稍等之后，会话12会报出如下错误：?2i&q;i+~q1D6Vs-CAU%F
update aa_1 set name=lower(name) where id=1
   *
qmRg9JITPUB个人空间第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁
k,sQL0lLITPUB个人空间ITPUB个人空间.S9i,|8P
gw
这时，查找告警日志，可以发现如下死锁信息：space.itpub.netG{)B3_WVp3X
Mon Feb 18 12:43:51 2008
e!ju[3Es#?6U1{ITPUB个人空间ORA-000060: Deadlock detected. More info in file e:\oracle\admin\mytwo\udump\mytwo_ora_348.trc.
/gd9T
uep#~Z)h
按照指示，查看跟踪文件：;X"v2~Fm
rD
                     ---------Blocker(s)--------  ---------Waiter(s)---------(sH.C_m,f
Resource Name       process session holds waits  process session holds waits
TX-0003000b-000008e0       12    12    X          11    10          X
+wZ7W1i3[space.itpub.netTX-00040009-00000991       11    10    X          12    12          X;G!MYI-y_
session 12: DID 0001-000C-00000002 session 10: DID 0001-000B-00000002ITPUB个人空间
f;p6Vyk
session 10: DID 0001-000B-00000002 session 12: DID 0001-000C-00000002
,`&x]J-ScE4[3gxRows waited on:JBKT.G
Session 10: obj - rowid = 000019ED - AAAB7zAAFAAAV02AAAspace.itpub.net)@SC
Vz&HA$Puks9g
  (dictionary objn - 6637, file - 5, block - 89398, slot - 0)space.itpub.net#ZG)|*Sv
Session 12: obj - rowid = 000019ED - AAAB7zAAFAAAV00AAADI^F$aX8P
  (dictionary objn - 6637, file - 5, block - 89396, slot - 0)
i[)X_Gd,]{][&njInformation on the OTHER waiting sessions:
Session 10:
  pid=11 serial=71 audsid=5634 user: 30/SCOTT-bso;R}8R
  O/S info: user: JB\JJBOBO1, term: JB, ospid: 464:440, machine: WORKGROUP\JB
|
[cC(d{          program: sqlplus.exe
  application name: SQL*Plus, hash value=3669949024Gsd]:^a
  Current SQL Statement:space.itpub.netd&H+FH8m2c+ArS
  update aa_1 set name=lower(name) where id=25Zf8[*dv
{;aRP
End of information on OTHER waiting sessions.
&ukP(~X[2e!s===================================================
U-Kb{-V'P@
下面还有PROCESS STATE，不再列出。ZGWjYJp*n

Y)Y5f5w-Wa"n3YITPUB个人空间根据跟踪文件中的信息，应该足以找到死锁发生的位置。
X(F3h/M%h8n0n!yITPUB个人空间5b[K!to4VJ5`eC
收到。

chenzhen232 发布于2008-02-19 14:20:40: 小妹的方法，很有创意嘛。

zergren发布于2008-02-19 15:29:07: 赫赫，学习了。

ice_bean发布于2008-02-20 12:58:39: 好东东

trigger_lau 发布于2008-02-21 11:58:29: 女孩子这样确实不错。

lkypy发布于2008-03-12 14:46:59: 不是一般人啊，学习中

foreverlee 发布于2009-01-05 19:10:23: you are awesome.
Conclusion as I tested in 10gR2.
*?ujI)G
C:E
3F;c5^w8{m1> create index az_obj_id. (4-TM)`
yz)U*]P x
-aPR8R7IMc
g8E
2>
Oyt8ITITPUB个人空间alter index az_obj_id rebuild online; (2-TM and 4-TM)
t/]&_o;Q/Rspace.itpub.netITPUB个人空间BJ'~,Eb
3>
alter index az_obj_id rebuild; (4-TM and 3-TM) |$hRT}#tR

DA5V)j8@oQ4>)N7tN.Ni6A
alter table az move tablespace test; (6-TM)
A#V1h9GlL
5>space.itpub.net8I
{NS,q:iPj V
alter table az shrink space; (3-TM and 6-TM But the amount of time for TM 6 is far more less than ALTER TABLE MOVE)
&eL7s#bh|+Xspace.itpub.net
6>ITPUB个人空间
\7~]fIz
alter table az shrink space COMPACT; (3-TM only)
D1A+O
Urand then alter table az shrink space; (6-TM only)0e%h"G7c+r Q:a [y
Please see shrinkSpaceIn10g.doc in the same direcotry to find the mechanism for SHRINK SPACE in Oracle 10g. U&[%yXr-k.Mi*PN
space.itpub.neti"lZ!y7`3|
7> truncate table az;(DDL) (6-TM)

foreverlee 发布于2009-01-05 19:12:03: QUOTE:
原帖由 eagle_fan 于 2008-2-16 17:46 发表 lRst+}M yY
你可以用10704 trace event，显示更为清楚准确)id7v M#JX8uL9\
ITPUB个人空间A
ZY
U~-ee
看我以前写的文章：space.itpub.net1g4OU[2V?7N9j

MGT/[!u6N-d7y&u-espace.itpub.nethttp://www.dbafan.com/blog/?p=49
0ae
@
u_Yw

Great