楼主: netfairy

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

[复制链接]
论坛徽章:
6
2014年新春福章
日期:2014-02-18 16:44:08马上有对象
日期:2014-02-18 16:44:08优秀写手
日期:2014-02-27 06:00:02问答徽章
日期:2014-04-13 19:36:532015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39
发表于 2014-12-22 11:44 | 显示全部楼层

使用道具 举报

回复
认证徽章
论坛徽章:
1
优秀写手
日期:2014-12-06 06:00:14
发表于 2014-12-22 14:54 | 显示全部楼层
netfairy 发表于 2014-12-22 10:25
重新检索符合条件的记录是必须的,但有两种做法:从头开始,还是只检索为检索的部分。两种做法的性能差别 ...

嗯,oracle更新或读取都是以数据块为单位,等待有锁的数据块的锁释放之后,只需要重新读入这部分的数据块判断是否满足条件的记录

使用道具 举报

回复
认证徽章
论坛徽章:
1
优秀写手
日期:2014-12-06 06:00:14
发表于 2014-12-22 15:05 | 显示全部楼层
netfairy 发表于 2014-12-22 10:25
重新检索符合条件的记录是必须的,但有两种做法:从头开始,还是只检索为检索的部分。两种做法的性能差别 ...

嗯,数据的读入或更新都是以数据块为单位,更新时发现有TX锁的数据块,就会等待,锁释放后,重新读入这个块的数据判断是否满足条件

使用道具 举报

回复
论坛徽章:
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-23 00:16 | 显示全部楼层
SELECT OBJECT_ID FROM test_pk t WHERE t.owner = 'SYS' and t.subobject_name is null and rownum <=20;

OBJECT_ID
----------
        14
         6
        44
        21
        45
        35
         5
        23
        47
        24
        36
        49
        37
        39
        57
        16
         8
        10
        34
        59

20 rows selected.

---------在SESSION 1做如下修改,故意跳过前两行,留给SESSION 2去锁
update test_pk t set t.subobject_name = '1' WHERE t.owner = 'SYS' and t.subobject_name is null AND OBJECT_ID NOT IN (14,6) and rownum <=10;

44 ROW UPDATE STARTED
21 ROW UPDATE STARTED
45 ROW UPDATE STARTED
35 ROW UPDATE STARTED
5 ROW UPDATE STARTED
23 ROW UPDATE STARTED
47 ROW UPDATE STARTED
24 ROW UPDATE STARTED
36 ROW UPDATE STARTED
49 ROW UPDATE STARTED

10 rows updated.

----在SESSION 2 试图锁住两行,执行成功,成功之后立即回滚释放锁
SELECT * FROM test_pk t WHERE OBJECT_ID IN (14,6) FOR UPDATE;

ROLLBACK;

----在SESSION 2 执行类似UPDATE, 前两行执行成功,到第三行被阻塞
update test_pk t set t.subobject_name = '1' WHERE t.owner = 'SYS' and t.subobject_name is null and rownum <=10;


----打开SESSION 3 试图锁住两行,执行失败,说明这两行确实被SESSION 2锁住
SELECT * FROM test_pk t WHERE OBJECT_ID IN (14,6) FOR UPDATE NOWAIT;

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


----- 到SESSION 1提交
COMMIT;

-----可以观察到SESSION 2如下输出:
14 ROW UPDATE STARTED
6 ROW UPDATE STARTED
44 ROW UPDATE STARTED   ---------这行曾经试图UPDATE但是被阻塞了,重启之后不满足条件,所以没有再次被UPDATE
14 ROW UPDATE STARTED   ---------第二次被UPDATE
6 ROW UPDATE STARTED    ---------第二次被UPDATE
37 ROW UPDATE STARTED
39 ROW UPDATE STARTED
57 ROW UPDATE STARTED
16 ROW UPDATE STARTED
8 ROW UPDATE STARTED
10 ROW UPDATE STARTED
34 ROW UPDATE STARTED
59 ROW UPDATE STARTED

10 rows updated.

从上述结果可以看出UPDATE是重新执行的,有两行被UPDATE两次,有一行被跳过因为已经不满足条件。

使用道具 举报

回复
论坛徽章:
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-23 00:18 | 显示全部楼层
试想如果UPDATE写的是这样:
update test_pk t
   set t.subobject_name = (SELECT MAX(OBJECT_NAME) FROM test_pk WHERE t.owner = 'SYS' and t.subobject_name is null)
WHERE t.owner = 'SYS' and t.subobject_name is null AND OBJECT_ID NOT IN (14,6) and rownum <=10;

阻塞解除之后,整个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-23 09:00 | 显示全部楼层
newkid 发表于 2014-12-23 00:16
SELECT OBJECT_ID FROM test_pk t WHERE t.owner = 'SYS' and t.subobject_name is null and rownum

感谢版主总在深夜回复我的疑问!!!
-----可以观察到SESSION 2如下输出:
14 ROW UPDATE STARTED
6 ROW UPDATE STARTED
你列出的结果中,这些被更新的行,用那种trace跟踪出结果的?

使用道具 举报

回复
论坛徽章:
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-23 09:25 | 显示全部楼层
忘记贴了,这是一个行级触发器:
CREATE OR REPLACE TRIGGER TR_TEST_PK BEFORE UPDATE ON TEST_PK FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE(:NEW.OBJECT_ID||' ROW UPDATE STARTED');
END;
/

使用道具 举报

回复
论坛徽章:
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-23 14:08 | 显示全部楼层
newkid 发表于 2014-12-23 00:18
试想如果UPDATE写的是这样:
update test_pk t
   set t.subobject_name = (SELECT MAX(OBJECT_NAME) FR ...

1)更新数据无交集时:两个Session都更新自己的记录。
Session 1执行:
update test_pk t set t.subobject_name = '1' WHERE  t.owner = 'SYS' and t.object_id in (20771,20772);
Session 2执行:
update test_pk t set t.subobject_name = (SELECT max(t.object_id)||'2' FROM test_pk t
WHERE t.owner = 'SYS' and t.owner = 'SYS' and t.subobject_name is not null) WHERE t.owner = 'SYS' and t.object_id in (20773,20774);
Session 1执行:commit
Session 2执行:commit
执行结果:
SELECT subobject_name,COUNT(*) FROM test_pk t WHERE t.owner = 'SYS' GROUP BY subobject_name ORDER BY 1;
SUBOBJECT_NAME                   COUNT(*)
------------------------------ ----------
1                                       2
2                                       2
                                    19056

2)更新数据有交集时: Session2中有一条与Session1存在交集,但Session2符合条件的数据不会被Session1修改成不符合条件。
Session 1执行:
SELECT max(t.object_id)||'2' FROM test_pk t WHERE t.owner = 'SYS' and t.subobject_name is not null;
MAX(T.OBJECT_ID)||'2'
-----------------------------------------
2
update test_pk t set t.subobject_name = '1' WHERE  t.owner = 'SYS' and t.object_id in (20771,20772);
Session 2执行:
SELECT max(t.object_id)||'2' FROM test_pk t WHERE t.owner = 'SYS' and t.subobject_name is not null;
MAX(T.OBJECT_ID)||'2'
-----------------------------------------
2
update test_pk t set t.subobject_name = (SELECT max(t.object_id)||'2' FROM test_pk t
WHERE t.owner = 'SYS' and t.owner = 'SYS' and t.subobject_name is not null) WHERE t.owner = 'SYS' and t.object_id in (20773,20772);
被阻塞
Session 1执行:
commit
SELECT max(t.object_id)||'2' FROM test_pk t WHERE t.owner = 'SYS' and t.subobject_name is not null;
MAX(T.OBJECT_ID)||'2'
-----------------------------------------
207722
Session 2执行:
前面的update执行成功。
commit
最终的执行结果:
SELECT subobject_name,COUNT(*) FROM test_pk t WHERE t.owner = 'SYS' GROUP BY subobject_name ORDER BY 1;
SUBOBJECT_NAME                   COUNT(*)
------------------------------ ----------
1                                       2
2                                       2
                                    19056

结论:Session2中被update成Session1 commit之前的值,这种情况下由于Session2中符合where条件的记录未发生变化,并未重启查询。

3)更新数据有交集时:Session2中有一条与Session1存在交集,而Session2中有一条符合条件的数据被Session1修改成不再符合条件。
Session 1执行:
SELECT max(t.object_id)||'2' FROM test_pk t WHERE t.owner = 'SYS' and t.subobject_name is not null;
MAX(T.OBJECT_ID)||'2'
-----------------------------------------
2
update test_pk t set t.subobject_name = '1',object_id=object_id+1000000 WHERE  t.owner = 'SYS' and t.object_id in (20771,20772);
SELECT max(t.object_id)||'2' FROM test_pk t WHERE t.owner = 'SYS' and t.subobject_name is not null;
MAX(T.OBJECT_ID)||'2'
-----------------------------------------
10207722
Session 2执行:
SELECT max(t.object_id)||'2' FROM test_pk t WHERE t.owner = 'SYS' and t.subobject_name is not null;
MAX(T.OBJECT_ID)||'2'
-----------------------------------------
2
update test_pk t set t.subobject_name = (SELECT max(t.object_id)||'2' FROM test_pk t WHERE t.owner = 'SYS' and t.owner = 'SYS' and t.subobject_name is not null) WHERE t.owner = 'SYS' and t.object_id in (20773,20772);
被阻塞
Session 1执行:
commit;
Session 2执行:
已更新1行
前面被阻塞的update执行成功,只更新了1行。
commit;
SELECT max(t.object_id)||'2' FROM test_pk t WHERE t.owner = 'SYS' and t.subobject_name is not null;
MAX(T.OBJECT_ID)||'2'
-----------------------------------------
10207722
最终的执行结果:
SELECT subobject_name,COUNT(*) FROM test_pk t WHERE t.owner = 'SYS' GROUP BY subobject_name ORDER BY 1;
SUBOBJECT_NAME                   COUNT(*)
------------------------------ ----------
1                                       2
10207722                                1
                                    19057

结论:由于Session2中有符合条件的数据被Session1修改成不再符合条件,因此查询重启,导致Session2的update的结果变成了Session1 commit之后的值。
这个是不是与“前面Session未提交的数据不会被后续的Session查到”相矛盾?

使用道具 举报

回复
论坛徽章:
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-23 14:25 | 显示全部楼层
netfairy 发表于 2014-12-23 14:08
1)更新数据无交集时:两个Session都更新自己的记录。
Session 1执行:
update test_pk t set t.subobj ...

这个不是矛盾。
session1提交之后,session2发现数据已经被修改过,不再满足更新的条件了,这时候为了数据的一致性与完整性,才需要重启查询。session2读取的是session1已经提交的数据

使用道具 举报

回复
论坛徽章:
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-23 15:24 | 显示全部楼层
abstractcyj 发表于 2014-12-23 14:25
这个不是矛盾。
session1提交之后,session2发现数据已经被修改过,不再满足更新的条件了,这时候为了数 ...

这就出现了在执行Session2的SQL时存在update的不确定性,有可能是2)的结果,有可能是3)的结果。对于开发来说,这可能导致update的结果不是自己想要的,这种Bug非常隐蔽很难被测试出来。

使用道具 举报

回复

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

本版积分规则 发表回复

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