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

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

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

查看( 1533 ) / 评论( 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，显示更为清楚准确space.itpub.netpg7DS#fzb{*x

看我以前写的文章：
~l8B{aXD%t7W
http://www.dbafan.com/blog/?p=49

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

烟囱发布于2008-02-16 18:05:49: mm把这每天一课进行下去.
我等收获不小哦.

8p"F)C+eafv5`谢谢!

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.
_S8t0C#V*[4{t0_g-m0Xp
思路不错！

oradbHome 发布于2008-02-17 19:16:13: 附加上 lock mode's-tjz6U&i8g-yZ
0 - none .E*Z5^SFE@-?
1 - null (NULL)
^@1X[8c8M{2 - row-S (SS)
.i9p6be9@
w[5ek3 - row-X (SX) ;V(l|7da'F
4 - share (S) 'Z{X2X
_
5 - S/Row-X (SSX) ITPUB个人空间-ji"z9S
N&i3VV
6 - exclusive (X)
o%H~
y
wE0?'Il4tn就不用在去查数据字典了^_^

晶晶小妹发布于2008-02-17 19:48:06: 在各种文档中，对于这６种模式的锁，英文称呼个不相同，我把他总结了一下，如下：f
~(hgM1P
zi}k
0 - none
1 - null (NULL)
i^Py;I
D6pITPUB个人空间2 - row-S (SS) 也简称rs锁
5b7Q(F5OVuo5L%r3 - row-X (SX) 　　　rx
MlT"\-r,B4 - share (S) 6zo{~9E-d+bD4q!k
5 - S/Row-X (SSX) srxspace.itpub.net~LT~I+d&`U
Yf
6 - exclusive (X)

]~/j;Ly其中２，３锁属于意向锁，关于这一点可以参考　萨师煊　和　王珊　的＜数据库系统概论＞这本书的并发控制那一张． WdR
L&hwbJ!G
H
k@;Je8Z5_:Ca
[ 本帖最后由晶晶小妹于 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 发表 p3}
^#\9}
看了一下，锁的一些概念还不太明白。
ITPUB个人空间!fFy!hiF

哪些不明白？？

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

晶晶小妹发布于2008-02-18 12:45:23: QUOTE:
原帖由 foxmile 于 2008-2-17 20:26 发表 ?'?h'vn:Bi[T
什么情况下会死锁。如何找到引发死锁的应用。
&V&v3q L}

死锁发生之后，通常有一个会话会被中断，在告警日志里面会记录一些信息．

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

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

做了一个简单的实验：dF1a4w"P0GD)kd-?6f.Z
会话10：
sid=10 pid=11> update aa_1 set name=lower(name) where id=1;ITPUB个人空间J2ki5~:Q
已更新 1 行。x&v|:bD
sid=10 pid=11> update aa_1 set name=lower(name) where id=2;;K,y&KTW#XE\uru&q

M@C&R9@!yS!O/F-G会话12
9u2H/i3A|Q
Nsid=12 pid=12> update aa_1 set name=lower(name) where id=2;
&y+p%es.N9`M/mKXespace.itpub.net已更新 1 行。
w B8]U!f!OpYR"@,_sid=12 pid=12> update aa_1 set name=lower(name) where id=1;

会话10的命令先发布，因此，稍等之后，会话12会报出如下错误：
p7J9SL!yU1S@E}~$lupdate aa_1 set name=lower(name) where id=1
   *
第 1 行出现错误:space.itpub.net2],O*@!k$D2y.Wo
ORA-00060: 等待资源时检测到死锁space.itpub.netN7u+Gy@|
}+mX
space.itpub.netES%V#\0e;Y
lde
这时，查找告警日志，可以发现如下死锁信息：
4i%a
^t0P2D_NMon Feb 18 12:43:51 2008space.itpub.net6EQu ?H
ORA-000060: Deadlock detected. More info in file e:\oracle\admin\mytwo\udump\mytwo_ora_348.trc./kCT B2oE#VO
`9^wh#r8gpRTx
按照指示，查看跟踪文件：space.itpub.netZf
E{uJ2{v
G
                     ---------Blocker(s)--------  ---------Waiter(s)---------
Kug:['a3ZMspace.itpub.netResource Name       process session holds waits  process session holds waits
TX-0003000b-000008e0       12    12    X          11    10          Xm*l0F4I'[\A#^w
TX-00040009-00000991       11    10    X          12    12          X
bq1Lalq8p4o*~ITPUB个人空间session 12: DID 0001-000C-00000002 session 10: DID 0001-000B-00000002
session 10: DID 0001-000B-00000002 session 12: DID 0001-000C-00000002T)KkC2m
Rows waited on:
S
]3cW:_
mh-sISession 10: obj - rowid = 000019ED - AAAB7zAAFAAAV02AAA
}-s$t`N#A8c  (dictionary objn - 6637, file - 5, block - 89398, slot - 0)O-{0E)[S Oz
Session 12: obj - rowid = 000019ED - AAAB7zAAFAAAV00AAAa{OI8k[4]
  (dictionary objn - 6637, file - 5, block - 89396, slot - 0)
)Bupv!Nc!u/qspace.itpub.netInformation on the OTHER waiting sessions:
H0G4x0qA.rspace.itpub.netSession 10:l5e;D`D9dg
  pid=11 serial=71 audsid=5634 user: 30/SCOTT
  O/S info: user: JB\JJBOBO1, term: JB, ospid: 464:440, machine: WORKGROUP\JB
s&@!YoiS3?6U;[ UITPUB个人空间          program: sqlplus.exe
  application name: SQL*Plus, hash value=36699490246en&a)o0uv-G$pH
  Current SQL Statement:
  update aa_1 set name=lower(name) where id=2
-xPlx$J?,wITPUB个人空间End of information on OTHER waiting sessions.
===================================================
下面还有PROCESS STATE，不再列出。-M[zxgFO^-^!{
space.itpub.net/\[p#Z-l,V*S
根据跟踪文件中的信息，应该足以找到死锁发生的位置。

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

foxmile 发布于2008-02-19 14:09:07: QUOTE:
原帖由 晶晶小妹 于 2008-2-18 13:04 发表
A%^e8Xv#F{2L Ds

h2A*Wl7K!Mkv$MKOITPUB个人空间做了一个简单的实验：
!N!E2mZ"v^会话10：
'_d3O_nr^sid=10 pid=11> update aa_1 set name=lower(name) where id=1;space.itpub.net-dQb;a*GRC
已更新 1 行。.b3hzWK.S8pt
sid=10 pid=11> update aa_1 set name=lower(name) where id=2;
Q`u:O8}0~^B6Y
QS}({4t$f会话12
HR+|n}cspace.itpub.netsid=12 pid=12> update aa_1 set name=lower(name) where id=2;S!m"v9P
WzNKC1aa R
已更新 1 行。ITPUB个人空间(` Q!J
Gl/m+[
sid=12 pid=12> update aa_1 set name=lower(name) where id=1;
)?*K6HX/o
giC+AI会话10的命令先发布，因此，稍等之后，会话12会报出如下错误：
update aa_1 set name=lower(name) where id=1
'qpnZ9k9z,l7aOspace.itpub.net    *
第 1 行出现错误: f!v$[HG qpm
ORA-00060: 等待资源时检测到死锁+ld,?B)pV7l

,rVY3tvry
Y%dG-rp这时，查找告警日志，可以发现如下死锁信息：e:z"Gr@ F2U`+A
Mon Feb 18 12:43:51 2008
XvRya/QE4ObITPUB个人空间ORA-000060: Deadlock detected. More info in file e:\oracle\admin\mytwo\udump\mytwo_ora_348.trc.
%KX5C&^N"j C&u1?
,t1h Qd:M9},S w$Hu按照指示，查看跟踪文件：
d&i6bh~Dspace.itpub.net                      ---------Blocker(s)--------  ---------Waiter(s)---------
Q.M2_9?vo+_e GResource Name       process session holds waits  process session holds waits
gJ:X7u5k6r(Zk5kspace.itpub.netTX-0003000b-000008e0       12    12    X          11    10          Xspace.itpub.net#\EOCc4R2vV+}
TX-00040009-00000991       11    10    X          12    12          X
&[ns%`2]YQspace.itpub.netsession 12: DID 0001-000C-00000002 session 10: DID 0001-000B-00000002
session 10: DID 0001-000B-00000002 session 12: DID 0001-000C-00000002
5Y[vXo0j}onRows waited on:
Session 10: obj - rowid = 000019ED - AAAB7zAAFAAAV02AAA:a][6}@;{
  (dictionary objn - 6637, file - 5, block - 89398, slot - 0)[ Psg&~K]Lw
Session 12: obj - rowid = 000019ED - AAAB7zAAFAAAV00AAA
'G)x(z
f.Ih2J0ZL  (dictionary objn - 6637, file - 5, block - 89396, slot - 0)
QC*J7{{fCInformation on the OTHER waiting sessions:
7}-M.^uto
].N.uITPUB个人空间Session 10:;Wl1B$JTG3[0p
  pid=11 serial=71 audsid=5634 user: 30/SCOTT
Rhs9\5ku$yf,Bspace.itpub.net  O/S info: user: JB\JJBOBO1, term: JB, ospid: 464:440, machine: WORKGROUP\JBITPUB个人空间^\ f/\*_e!ev
         program: sqlplus.exe
3Q,}$mX$S[wspace.itpub.net  application name: SQL*Plus, hash value=3669949024ITPUB个人空间^{d
B+R"Z
  Current SQL Statement:ITPUB个人空间,I5g1la.tdly*[
  update aa_1 set name=lower(name) where id=2ITPUB个人空间~4RzI {
End of information on OTHER waiting sessions.
8A^X#^G-ACm@o#|===================================================
BfKx%Im2F下面还有PROCESS STATE，不再列出。
`1um9N!TtNq(rPspace.itpub.netHXt#aP&VC$rIs
根据跟踪文件中的信息，应该足以找到死锁发生的位置。
ITPUB个人空间5i1wS;x(_

x3s G,{0Xa收到。

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.
)Ne,jX"cbv
1> create index az_obj_id. (4-TM)space.itpub.net5g
}8ss/Rz

8Uk
u7Eg-_P(H!|3y2>
FOXe.}1{@(?&HITPUB个人空间alter index az_obj_id rebuild online; (2-TM and 4-TM)

Vr7C#^du
guVH+jc8^:PJ3> ITPUB个人空间;y"K(wxV-x%^
alter index az_obj_id rebuild; (4-TM and 3-TM)

|1M3c$_A]]BnITPUB个人空间4>
CO{+W;P?JHo#HeDalter table az move tablespace test; (6-TM)
h7tiX,{ L9woITPUB个人空间
5>
u6Txp:B(F
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)
R*gF(h1^epK2p^%z`G
6>
alter table az shrink space COMPACT; (3-TM only)
and then alter table az shrink space; (6-TM only)-`#{_"a"C)R
Please see shrinkSpaceIn10g.doc in the same direcotry to find the mechanism for SHRINK SPACE in Oracle 10g. \joYkg6\m

S}:s}u"u7> truncate table az;(DDL) (6-TM)

foreverlee 发布于2009-01-05 19:12:03: QUOTE:
原帖由 eagle_fan 于 2008-2-16 17:46 发表
;Bzu2P0V'G你可以用10704 trace event，显示更为清楚准确*K4z:hG#cd5MP}Z

){Ll*IH
yt+RS看我以前写的文章：
!p?'p{-FNC
#iV@Z-chttp://www.dbafan.com/blog/?p=49
k;YJA S*XXTfO1}J

(\f1Iz#lo0T$KZITPUB个人空间Great