查看: 3964|回复: 20

【讨论】关于锁的一个实验和思考

[复制链接]
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
发表于 2014-12-19 10:50 | 显示全部楼层 |阅读模式
关于锁的一个实验:
Step 1:
Session1:Sid=402
SQL> SELECT COUNT(*) FROM test_pk t WHERE subobject_name is not null and t.owner = 'SYS';
  COUNT(*)                                                                     
----------                                                                     
         0                                                                     
此时符合条件的数据是0条。根据条件更新10条记录:
SQL> update test_pk t set t.subobject_name = '1' WHERE t.owner = 'SYS' and t.subobject_name is null and rownum <=10;
已更新10行。
Step 2:
Session2:Sid=592
SQL> update test_pk t set t.subobject_name = '2' WHERE t.owner = 'SYS' and t.subobject_name is null and rownum <=10;
此时Session2处于等待状态,因为Session1锁住了其要更新的记录。
此时锁的情况:
SQL> SELECT * FROM v$lock WHERE sid in (402,592);
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000086963460 00000000869634B8        402 AE          100          0          4          0        268          0
00007F02F4256E10 00007F02F4256E70        402 TM        33998          0          3          0        107          0
00000000826280B0 0000000082628128        402 TX       131073      68751          6          0        107          1
0000000086964198 00000000869641F0        592 AE          100          0          4          0        254          0
00007F02F4256E10 00007F02F4256E70        592 TM        33998          0          3          0         78          0
0000000086963380 00000000869633D8        592 TX       131073      68751          0          6         78          0
Step 3:
Session1:
进行提交:
SQL> commit;
提交完成。
Step 4:
Session2:
由于Session1解锁,因此update可以继续执行:猜猜会更新多少条数据?原本我以为由于要更新的数据已经被Session1更新掉,所以更新的数据应该是0条,结果呢:
已更新10行。
SQL> commit;
提交完成。
SQL> SELECT subobject_name,COUNT(*) FROM test_pk t WHERE t.owner = 'SYS' GROUP BY subobject_name;
SUBOBJECT_NAME                   COUNT(*)
------------------------------ ----------
1                                      10
2                                      10
从执行结果可以看到,Session2又另外找了10条满足条件的数据进行了更新。
备注:我原本猜测是因为在同一个数据块中,但是将“rownum <=10”扩大到“rownum <=10000”以上结果也成立。

那么问题来了:
执行到Step2的时候,在Session2中应该已经执行了SQL语句扫描表(FTS)的部分,才能找到需要锁的那10条数据,根据后面的执行计划知道:根据STOPKEY扫描到10条数据以后就停止了扫描。到了Step4的时候,Session2中原本要锁的10条数据由于被Session Update了导致数据已经不符合条件。这时Session2中的SQL会再次对表扫描一次另外找出符合条件的10条记录吗?从执行的结果看,其又找到了10条记录进行Update,但是从10046中又没有发现SQL被执行两次。
那么在Step4中到底Session做了什么?或者说,其中执行细节到底是如何的?

附上Session2的10046:

  1. Trace file
  2. /u01/app/diag/rdbms/mytest/mytest/trace/mytest_ora_24728.trc
  3. Oracle Database
  4. 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  5. With the
  6. Partitioning, OLAP, Data Mining and Real Application Testing
  7. options
  8. ORACLE_HOME = /u01/app/product/11.2.0
  9. System name: Linux
  10. Node
  11. name: mytest
  12. Release: 2.6.32-431.el6.x86_64
  13. Version: #1 SMP Fri Nov 22
  14. 03:15:09 UTC 2013
  15. Machine: x86_64
  16. VM name: VMWare Version: 6
  17. Instance
  18. name: mytest
  19. Redo thread mounted by this instance: 1
  20. Oracle process
  21. number: 50
  22. Unix process pid: 24728, image: oracle@mytest

  23. *** 2014-12-19 01:18:57.536
  24. ***
  25. SESSION ID:(405.569) 2014-12-19 01:18:57.536
  26. *** CLIENT ID:() 2014-12-19
  27. 01:18:57.536
  28. *** SERVICE NAME:(mytest) 2014-12-19 01:18:57.536
  29. *** MODULE
  30. NAME:(sqlplus.exe) 2014-12-19 01:18:57.536
  31. *** ACTION NAME:() 2014-12-19
  32. 01:18:57.536

  33. Processing Oradebug command 'setmypid'
  34. *** 2014-12-19
  35. 01:18:57.536
  36. Oradebug command 'setmypid' console output: <none>
  37. ***
  38. 2014-12-19 01:20:40.897
  39. Processing Oradebug command 'event 10046 trace name
  40. context forever,level 8'
  41. *** 2014-12-19 01:20:40.902
  42. Oradebug command
  43. 'event 10046 trace name context forever,level 8' console output:
  44. <none>
  45. WAIT #0: nam='SQL*Net message to client' ela= 5 driver
  46. id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1418923240902351
  47. *** 2014-12-19
  48. 01:20:49.458
  49. WAIT #0: nam='SQL*Net message from client' ela= 8555847 driver
  50. id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1418923249458215
  51. *** 2014-12-19
  52. 01:20:49.458
  53. Processing Oradebug command 'tracefile_name'
  54. *** 2014-12-19
  55. 01:20:49.458
  56. Oradebug command 'tracefile_name' console output:

  57. /u01/app/diag/rdbms/mytest/mytest/trace/mytest_ora_24728.trc
  58. WAIT #0:
  59. nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0
  60. obj#=-1 tim=1418923249458418
  61. *** 2014-12-19 01:21:49.739
  62. WAIT #0:
  63. nam='SQL*Net message from client' ela= 60281493 driver id=1413697536 #bytes=1
  64. p3=0 obj#=-1 tim=1418923309739930
  65. =====================
  66. PARSING IN CURSOR
  67. #140713126676496 len=115 dep=0 uid=0 oct=6 lid=0 tim=1418923309741752
  68. hv=2789690021 ad='72c9cd80' sqlid='4u1zn1qm4fkp5'
  69. update test.test_pk t set
  70. t.subobject_name = '2' WHERE t.owner = 'SYS' and t.subobject_name is null and
  71. rownum <=10
  72. END OF STMT
  73. PARSE
  74. #140713126676496:c=1000,e=1679,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2551431330,tim=1418923309741749
  75. ***
  76. 2014-12-19 01:21:56.514
  77. WAIT #140713126676496: nam='enq: TX - row lock
  78. contention' ela= 6758479 name|mode=1415053318 usn<<16 | slot=393239
  79. sequence=73556 obj#=33998 tim=1418923316500907
  80. EXEC
  81. #140713126676496:c=12998,e=6810327,p=0,cr=11,cu=24,mis=0,r=10,dep=0,og=1,plh=2551431330,tim=1418923316552139
  82. STAT
  83. #140713126676496 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  TEST_PK
  84. (cr=11 pr=0 pw=0 time=6796270 us)'
  85. STAT #140713126676496 id=2 cnt=21 pid=1
  86. pos=1 obj=0 op='COUNT STOPKEY (cr=10 pr=0 pw=0 time=471 us)'
  87. STAT
  88. #140713126676496 id=3 cnt=21 pid=2 pos=1 obj=33998 op='TABLE ACCESS FULL TEST_PK
  89. (cr=10 pr=0 pw=0 time=429 us cost=103 size=174304 card=13408)'
  90. WAIT
  91. #140713126676496: nam='SQL*Net message to client' ela= 4 driver id=1413697536
  92. #bytes=1 p3=0 obj#=-1 tim=1418923316552281
  93. *** 2014-12-19
  94. 01:22:00.731
  95. WAIT #140713126676496: nam='SQL*Net message from client' ela=
  96. 4179574 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1418923320731900
  97. CLOSE
  98. #140713126676496:c=0,e=8,dep=0,type=0,tim=1418923320732016
  99. =====================
  100. PARSING
  101. IN CURSOR #140713126676496 len=6 dep=0 uid=0 oct=44 lid=0 tim=1418923320732111
  102. hv=3480936638 ad='0' sqlid='23wm3kz7rps5y'
  103. commit
  104. END OF STMT
  105. PARSE
  106. #140713126676496:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1418923320732110
  107. XCTEND
  108. rlbk=0, rd_only=0, tim=1418923320732178
  109. EXEC
  110. #140713126676496:c=1000,e=234,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,plh=0,tim=1418923320732390
  111. WAIT
  112. #140713126676496: nam='log file sync' ela= 322 buffer#=2764 sync scn=2806156040
  113. p3=0 obj#=-1 tim=1418923320732743
  114. WAIT #140713126676496: nam='SQL*Net message
  115. to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1
  116. tim=1418923320732811
  117. *** 2014-12-19 01:22:29.604
  118. WAIT #140713126676496:
  119. nam='SQL*Net message from client' ela= 28872097 driver id=1413697536 #bytes=1
  120. p3=0 obj#=-1 tim=1418923349604930
  121. CLOSE
  122. #140713126676496:c=0,e=12,dep=0,type=1,tim=1418923349605083
  123. *** 2014-12-19
  124. 01:22:29.605
  125. Processing Oradebug command 'event 10046 trace name context
  126. off'
  127. *** 2014-12-19 01:22:29.605
  128. Oradebug command 'event 10046 trace name
  129. context off' console output: <none>

复制代码


求职 : 数据库管理员
认证徽章
论坛徽章:
11
ITPUB社区千里马徽章
日期:2013-06-09 10:15:34懒羊羊
日期:2015-03-04 14:52:11懒羊羊
日期:2015-02-10 13:36:05马上有对象
日期:2015-02-02 12:29:02红宝石
日期:2015-01-19 09:44:10马上有车
日期:2014-11-11 14:16:07马上有车
日期:2014-03-27 15:59:39优秀写手
日期:2014-03-12 06:00:13马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09
发表于 2014-12-19 11:03 | 显示全部楼层
一致性、完整性

使用道具 举报

回复
论坛徽章:
6
2010广州亚运会纪念徽章:板球
日期:2010-11-15 11:29:572010广州亚运会纪念徽章:足球
日期:2010-11-15 11:30:022013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-20 06:00:12沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
发表于 2014-12-19 11:44 | 显示全部楼层
查询重启动

使用道具 举报

回复
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
 楼主| 发表于 2014-12-19 17:34 | 显示全部楼层
经网友指点可能的流程:假设两个Session各更新1000笔,只有最后一笔有交集。
session 1 update 1000-2000
session 2 update 1-1000
session 2 读到第1000笔发现有事物,一致读后就等待;
session 1 conmit
session 继续读1001-2001,发现2001满足条件,这时已经取到了需要的1000笔继续,因此进行更新。

使用道具 举报

回复
论坛徽章:
527
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2014-12-19 23:47 | 显示全部楼层
3楼说得对,因为你修改的数据是UPDATE的条件,所以解锁之后整个UPDATE会从头开始执行。

使用道具 举报

回复
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
 楼主| 发表于 2014-12-20 11:57 | 显示全部楼层
本帖最后由 netfairy 于 2014-12-20 12:14 编辑
newkid 发表于 2014-12-19 23:47
3楼说得对,因为你修改的数据是UPDATE的条件,所以解锁之后整个UPDATE会从头开始执行。

如果是update之后从头开始执行,那么表就要被扫描两遍,这与10046中看到的不符。貌似,我在4楼的解释更加接近。也就是在扫描过程中说遇到被锁的记录时暂停,拿到锁之后再继续扫描后面的。

使用道具 举报

回复
论坛徽章:
527
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2014-12-21 05:41 | 显示全部楼层
如果你用触发器就可以观察到启动两次,TOM的书里也是这么说的。

CREATE OR REPLACE TRIGGER TR_TEST_PK BEFORE UPDATE ON TEST_PK
BEGIN
  DBMS_OUTPUT.PUT_LINE('UPDATE STARTED');
END;
/

第二个会话在阻塞前如果已经成功UPDATE了几行,这些行也会被再次UPDATE, 这可以通过行级触发器观察到。

使用道具 举报

回复
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
 楼主| 发表于 2014-12-22 10:02 | 显示全部楼层
newkid 发表于 2014-12-21 05:41
如果你用触发器就可以观察到启动两次,TOM的书里也是这么说的。

CREATE OR REPLACE TRIGGER TR_TEST_PK  ...

触发器的确被触发了3次,不过这个结论与4楼的并不完全矛盾。
唯一的矛盾在于:这个查询重启到底是从头开始,还是从中间那笔之前未获得锁的记录开始?
假设一个场景情况:需要update一个1亿笔数据的表的10笔记录,正好这10条记录被另外10个Session都锁了。
如果查询重启是从头开始,那么:这1亿笔数据,是不是要被扫10次?
如果查询重启是从中间那笔之前未获得锁的记录开始,那么:这1亿笔数据只要被扫1次。(之前扫过的不用继续扫,只需要接着往后面扫)
明显后者性能更高,也符合10046中看到的现象:表没有被多次扫描。

使用道具 举报

回复
认证徽章
论坛徽章:
1
优秀写手
日期:2014-12-06 06:00:14
发表于 2014-12-22 10:23 | 显示全部楼层
为了保证数据的一致性和完整性,我觉得session1的锁释放之后,session2会重新检索符合条件的记录

使用道具 举报

回复
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
 楼主| 发表于 2014-12-22 10:25 | 显示全部楼层
chengwanhe 发表于 2014-12-22 10:23
为了保证数据的一致性和完整性,我觉得session1的锁释放之后,session2会重新检索符合条件的记录

重新检索符合条件的记录是必须的,但有两种做法:从头开始,还是只检索为检索的部分。两种做法的性能差别非常大。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表