查看: 3166|回复: 14

[PL/SQL] 【讨论】求教 for update VS for update skip locked

[复制链接]
论坛徽章:
310
生肖徽章:鼠
日期:2014-08-07 19:59:00生肖徽章:牛
日期:2015-01-14 17:40:00生肖徽章:虎
日期:2014-11-05 19:45:02生肖徽章:兔
日期:2015-03-05 07:52:09生肖徽章:龙
日期:2015-03-05 07:53:59生肖徽章:蛇
日期:2014-08-28 11:56:51生肖徽章:马
日期:2014-11-04 20:19:57生肖徽章:羊
日期:2015-01-09 15:48:14生肖徽章:猴
日期:2015-02-04 16:57:45生肖徽章:鸡
日期:2014-09-05 20:35:13
发表于 2014-9-29 10:27 | 显示全部楼层 |阅读模式
每日一题 2013-12-11 SELECT FOR UPDATE SKIP LOCKED 有点不明白
test1:
DECLARE
  CURSOR CUR_EMPLOYEES IS
    SELECT * FROM PLCH_EMPLOYEES ORDER BY EMPLOYEE_ID FOR UPDATE;
  L_EMP_REC CUR_EMPLOYEES%ROWTYPE;
BEGIN
  OPEN CUR_EMPLOYEES;
  PLCH_UPDATE(100, 20000);
  PLCH_UPDATE(200, 10000);
  LOOP
    FETCH CUR_EMPLOYEES
      INTO L_EMP_REC;
    EXIT WHEN CUR_EMPLOYEES%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(L_EMP_REC.EMPLOYEE_ID);
  END LOOP;
END;

输出:
Employee 100 is locked.
Employee 200 is locked.
100
200
300

===========================================
test2:
DECLARE
  CURSOR CUR_EMPLOYEES IS
    SELECT *
      FROM PLCH_EMPLOYEES
     ORDER BY EMPLOYEE_ID
       FOR UPDATE SKIP LOCKED;
  L_EMP_REC CUR_EMPLOYEES%ROWTYPE;
BEGIN
  OPEN CUR_EMPLOYEES;
  PLCH_UPDATE(100, 20000);
  PLCH_UPDATE(200, 10000);
  LOOP
    FETCH CUR_EMPLOYEES
      INTO L_EMP_REC;
    EXIT WHEN CUR_EMPLOYEES%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(L_EMP_REC.EMPLOYEE_ID);
  END LOOP;
END;

输出:
200
300


我的理解:
select for update 是对select查询结果加行锁,加上skip locked就是在尝试加锁之前判断记录是否已经被其他用户锁定,如果是,就跳过这条记录,如果否,就锁定该记录.
如果没有带skip locked,那么尝试加锁失败会进行等待,除非使用nowait立即抛出ora-00054或者wati n 等待 n 秒。
test1打开游标的时候,将[size=13.3333339691162px]PLCH_EMPLOYEES表的数据锁定,执行[size=13.3333339691162px]PLCH_UPDATE()时,发现记录被锁,提示语句;
我认为test2的结果应该和test1一样.


求解释test2输出结果的原因.

论坛徽章:
310
生肖徽章:鼠
日期:2014-08-07 19:59:00生肖徽章:牛
日期:2015-01-14 17:40:00生肖徽章:虎
日期:2014-11-05 19:45:02生肖徽章:兔
日期:2015-03-05 07:52:09生肖徽章:龙
日期:2015-03-05 07:53:59生肖徽章:蛇
日期:2014-08-28 11:56:51生肖徽章:马
日期:2014-11-04 20:19:57生肖徽章:羊
日期:2015-01-09 15:48:14生肖徽章:猴
日期:2015-02-04 16:57:45生肖徽章:鸡
日期:2014-09-05 20:35:13
 楼主| 发表于 2014-9-29 10:29 | 显示全部楼层

CREATE TABLE plch_employees
(
  employee_id INTEGER PRIMARY KEY
, last_name   VARCHAR2(100)
, salary      NUMBER
)
/

BEGIN
   INSERT INTO plch_employees
     VALUES (100, 'Jobs', 10000);

   INSERT INTO plch_employees
     VALUES (200, 'Ellison', 10000);

   INSERT INTO plch_employees
     VALUES (300, 'Gates', 10000);

   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE PLCH_UPDATE(P_EMPLOYEE_ID IN PLCH_EMPLOYEES.EMPLOYEE_ID%TYPE,
                                        P_SALARY      IN PLCH_EMPLOYEES.SALARY%TYPE) IS
  PRAGMA AUTONOMOUS_TRANSACTION;

  E_ROW_LOCKED EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_ROW_LOCKED, -54);

  L_EMPLOYEE_ID PLCH_EMPLOYEES.EMPLOYEE_ID%TYPE;
BEGIN
  SELECT EMPLOYEE_ID
    INTO L_EMPLOYEE_ID
    FROM PLCH_EMPLOYEES
   WHERE EMPLOYEE_ID = P_EMPLOYEE_ID
     FOR UPDATE NOWAIT;

  UPDATE PLCH_EMPLOYEES
     SET SALARY = P_SALARY
   WHERE EMPLOYEE_ID = P_EMPLOYEE_ID;

  COMMIT;
EXCEPTION
  WHEN E_ROW_LOCKED THEN
    DBMS_OUTPUT.PUT_LINE('Employee ' || P_EMPLOYEE_ID || ' is locked.');
END PLCH_UPDATE;

使用道具 举报

回复
论坛徽章:
310
生肖徽章:鼠
日期:2014-08-07 19:59:00生肖徽章:牛
日期:2015-01-14 17:40:00生肖徽章:虎
日期:2014-11-05 19:45:02生肖徽章:兔
日期:2015-03-05 07:52:09生肖徽章:龙
日期:2015-03-05 07:53:59生肖徽章:蛇
日期:2014-08-28 11:56:51生肖徽章:马
日期:2014-11-04 20:19:57生肖徽章:羊
日期:2015-01-09 15:48:14生肖徽章:猴
日期:2015-02-04 16:57:45生肖徽章:鸡
日期:2014-09-05 20:35:13
 楼主| 发表于 2014-9-30 08:41 | 显示全部楼层
求解惑~~~~~

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期: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-9-30 09:04 | 显示全部楼层
"带有SKIP LOCKED选项的游标循环不但会跳过被其他事务锁住的记录,而且会跳过那些在游标打开后被其他事务修改过的行。"
第一行100的工资被修改过了,所以游标就跳过了这一行。

使用道具 举报

回复
论坛徽章:
310
生肖徽章:鼠
日期:2014-08-07 19:59:00生肖徽章:牛
日期:2015-01-14 17:40:00生肖徽章:虎
日期:2014-11-05 19:45:02生肖徽章:兔
日期:2015-03-05 07:52:09生肖徽章:龙
日期:2015-03-05 07:53:59生肖徽章:蛇
日期:2014-08-28 11:56:51生肖徽章:马
日期:2014-11-04 20:19:57生肖徽章:羊
日期:2015-01-09 15:48:14生肖徽章:猴
日期:2015-02-04 16:57:45生肖徽章:鸡
日期:2014-09-05 20:35:13
 楼主| 发表于 2014-9-30 09:09 | 显示全部楼层
newkid 发表于 2014-9-30 09:04
"带有SKIP LOCKED选项的游标循环不但会跳过被其他事务锁住的记录,而且会跳过那些在游标打开后被其他事务修 ...

在 open游标的时候,PLCH_UPDATE()过程还没有执行呀,应该没有被修改的行呀

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期: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-9-30 09:26 | 显示全部楼层
四十五度向上 发表于 2014-9-30 09:09
在 open游标的时候,PLCH_UPDATE()过程还没有执行呀,应该没有被修改的行呀

OPEN的时候不算,FETCH时才会去比较数据。

使用道具 举报

回复
论坛徽章:
310
生肖徽章:鼠
日期:2014-08-07 19:59:00生肖徽章:牛
日期:2015-01-14 17:40:00生肖徽章:虎
日期:2014-11-05 19:45:02生肖徽章:兔
日期:2015-03-05 07:52:09生肖徽章:龙
日期:2015-03-05 07:53:59生肖徽章:蛇
日期:2014-08-28 11:56:51生肖徽章:马
日期:2014-11-04 20:19:57生肖徽章:羊
日期:2015-01-09 15:48:14生肖徽章:猴
日期:2015-02-04 16:57:45生肖徽章:鸡
日期:2014-09-05 20:35:13
 楼主| 发表于 2014-9-30 09:47 | 显示全部楼层
newkid 发表于 2014-9-30 09:26
OPEN的时候不算,FETCH时才会去比较数据。

哦,了解了,谢谢版主

另外,还想问下,
如果 salary 原值为200,我执行update语句将salary值修改为200,那Oracle在执行修改之前是不是会有一步判断,和原值不一样再真正执行修改

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期: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-9-30 10:07 | 显示全部楼层
四十五度向上 发表于 2014-9-30 09:47
哦,了解了,谢谢版主

另外,还想问下,

即使新旧值相同也会做UPDATE。

使用道具 举报

回复
论坛徽章:
310
生肖徽章:鼠
日期:2014-08-07 19:59:00生肖徽章:牛
日期:2015-01-14 17:40:00生肖徽章:虎
日期:2014-11-05 19:45:02生肖徽章:兔
日期:2015-03-05 07:52:09生肖徽章:龙
日期:2015-03-05 07:53:59生肖徽章:蛇
日期:2014-08-28 11:56:51生肖徽章:马
日期:2014-11-04 20:19:57生肖徽章:羊
日期:2015-01-09 15:48:14生肖徽章:猴
日期:2015-02-04 16:57:45生肖徽章:鸡
日期:2014-09-05 20:35:13
 楼主| 发表于 2014-9-30 10:14 | 显示全部楼层
newkid 发表于 2014-9-30 10:07
即使新旧值相同也会做UPDATE。

版主方便看下 【每日一题 2013-12-11 SELECT FOR UPDATE SKIP LOCKED】的 B选项的解释吗,如果新旧值相同也会update的话,
【相反,employee_id=200 这一行没有被plch_update所修改,它的工资值保持10000,因此这行不会被循环跳过。】是因为什么呢?

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期: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-9-30 10:23 | 显示全部楼层
四十五度向上 发表于 2014-9-30 10:14
版主方便看下 【每日一题 2013-12-11 SELECT FOR UPDATE SKIP LOCKED】的 B选项的解释吗,如果新旧值相同 ...

这是两个独立的事务,判断修改与否是通过比较两个值。
我楼上说的会作UPDATE,是说ORACLE会执行这个UPDATE动作(有REDO, 会执行触发器),但是对于另一个事务而言,它认为没有被改过。

使用道具 举报

回复

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

本版积分规则 发表回复

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