【困惑】一个我理解不了的游标问题

anlinew

原帖由 newkid 于 2010-8-19 22:21 发表

不行的，因为被父过程锁住了。这种情况下的自治事务很可能死锁。

子进程如果再申请父进程locked的资源，肯定会死锁，使用的时候需要很小心
从楼主描述的情况看，子进程是处理其他对象
当然实际开发中不少开发人员也会犯这种错误，将原本应该在父进程做的事情放到子进程里了，因此而出现数据库锁加业务逻辑锁构成了死锁的情况

qingyun

原帖由 newkid 于 2010-8-19 21:44 发表
不是BUG, 提交或回滚就意味着事务结束，所有锁都释放。
你可以试着把forupdate的逻辑用标志位实现，程序之间通过标志位进行协调。

但是这样写就OK：

begin
  for rec in (select .... for update
loop
    ....;
end loop;
commit;
commit;
commit;  --在循环体外，随便写多少commit都没事；
end;；

这个和您说的：“提交或回滚就意味着事务结束，所有锁都释放。”有矛盾；因为游标中如果有for update ,整个pl/sql不结束，如果没有commit，它是一只有锁的；
如果你这个说法成立，那么commit无论放在那里都会报错；

另外：
如果游标的记录恰好是一条；那么也没问题


“你可以试着把forupdate的逻辑用标志位实现，程序之间通过标志位进行协调。 ”
-- 如果是两个oracle数据库之间的dblink，我是能想到其他手段躲开这种写法

但是现在是通过GateWays，实现oracle，sqlserver的逻辑互联，虽然能够访问SqlServer方便了，写pl/sql的时候，就把它当成oracle来写；

但是毕竟还是有问题，更新sqlserver的数据和更新oracle自身的数据，这两个语句之间，必须有commit，否者会报错；报了错之后问题非常严重；
相关表会被锁住，连select它都不行，把数据库重启也不行；甚至把电脑重启都不行，这个死掉的分布式事务彻底整垮数据库，
我摸索了个方法：
  可以用 truncate table 那个被彻底锁死的表；

官方解决这个问题的方法：

解决办法如下：

1、使用Oracle DBA用户，查询如下数据字典：select * from dba_2pc_pending

2、强制Rollback或者Commit该事务：

COMMIT FORCE "10.30.743"; 或者 ROLLBACK FORCE "10.30.743";

3、再次启动Fiper专业的Weblogic服务，启动成功！

遇到问题要仔细分析原因，找到正确的方法解决。

用
ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY

就不考虑 分布式事务异常

彻底删除 dba_2pc_pending

DELETE FROM SYS.PENDING_TRANS$
      WHERE (STATE = 'collecting' OR STATE = 'committed' OR
             STATE = 'forced commit' OR STATE = 'forced rollback')


看一下源码：

  PROCEDURE PURGE_LOST_DB_ENTRY(XID VARCHAR2) IS
    TRANSACTION_NOT_FOUND EXCEPTION;
  BEGIN
    USE_ROLLBACK_SEGMENT('SYSTEM');   --应该是这个地方和automatic undo 冲突
    DELETE FROM SYS.PENDING_TRANS$
      WHERE (STATE = 'collecting' OR STATE = 'committed' OR
             STATE = 'forced commit' OR STATE = 'forced rollback')
      AND LOCAL_TRAN_ID = XID;
    IF SQL%ROWCOUNT = 1 THEN
      DELETE FROM SYS.PENDING_SESSIONS$ WHERE LOCAL_TRAN_ID = XID;
      DELETE FROM SYS.PENDING_SUB_SESSIONS$ WHERE LOCAL_TRAN_ID = XID;
    ELSE
      RAISE TRANSACTION_NOT_FOUND;
    END IF;
  END;

[ 本帖最后由 qingyun 于 2010-8-19 22:49 编辑 ]

newkid

原帖由 anlinew 于 2010-8-19 22:37 发表

子进程如果再申请父进程locked的资源，肯定会死锁，使用的时候需要很小心
从楼主描述的情况看，子进程是处理其他对象
当然实际开发中不少开发人员也会犯这种错误，将原本应该在父进程做的事情放到子进程里了，因此而出现数据库锁加业务逻辑锁构成了死锁的情况

如果是完全独立的，确实可以用自治事务。

newkid

原帖由 qingyun 于 2010-8-19 22:40 发表



但是这样写就OK：

begin
  for rec in (select .... for update
loop
    ....;
end loop;
commit;
commit;
commit;  --在循环体外，随便写多少commit都没事；
end;；

这个和您说的：“提交或回滚就意味着事务结束，所有锁都释放。”有矛盾；因为游标中如果有for update ,整个pl/sql不结束，如果没有commit，它是一只有锁的；
如果你这个说法成立，那么commit无论放在那里都会报错；

另外：
如果游标的记录恰好是一条；那么也没问题

没有矛盾呀？你COMMIT之后，哪怕PLSQL没结束，锁也被释放了。不信你做个试验，COMMIT之后SLEEP, 另外开个窗口去锁看看。

guostong

原帖由 qingyun 于 2010-8-19 22:40 发表



但是这样写就OK：

begin
  for rec in (select .... for update
loop
    ....;
end loop;
commit;
commit;
commit;  --在循环体外，随便写多少commit都没事；
end;；

这个和您说的：“提交或回滚就意味着事务结束，所有锁都释放。”有矛盾；因为游标中如果有for update ,整个pl/sql不结束，如果没有commit，它是一只有锁的；
如果你这个说法成立，那么commit无论放在那里都会报错；

另外：
如果游标的记录恰好是一条；那么也没问题

如果游标的记录恰好是一条, 那么就说明 fetch已经完成，commit 之后没有fetch了

qingyun

原帖由 newkid 于 2010-8-19 22:45 发表


没有矛盾呀？你COMMIT之后，哪怕PLSQL没结束，锁也被释放了。不信你做个试验，COMMIT之后SLEEP, 另外开个窗口去锁看看。

我的意思是： commit 是全局的，
不管它放在游标的循环体内，还是其他地方都一样，为什么放在forupdate的循环体内就出了问题。

我现在没方法了，就用数组的方法来实现：
1.先把游标的内容通过循环放在数组里，这个循环体内因为用不到commit;所以不用担心报错；
2.循环游标，这个循环体内可以加commit；

我觉得这个方法应该可行，马上我就测试一下，但是还是希望有更好的方法。

[ 本帖最后由 qingyun 于 2010-8-19 22:56 编辑 ]

newkid

原帖由 qingyun 于 2010-8-19 22:51 发表


我的意思是： commit 是全局的，
不管它放在游标的循环体内，还是其他地方都一样，为什么放在forupdate的循环体内就出了问题。

COMMIT之后如果你试图访问下一条，它已经不是锁住的，和游标定义冲突了。
如果允许你在循环内COMMIT, 那么它就完全等价于不加FOR UPDATE的游标了，你的 FOR UPDATE变成没有意义。

qingyun

原帖由 newkid 于 2010-8-19 22:56 发表

COMMIT之后如果你试图访问下一条，它已经不是锁住的，和游标定义冲突了。
如果允许你在循环内COMMIT, 那么它就完全等价于不加FOR UPDATE的游标了，你的 FOR UPDATE变成没有意义。

说的有道理，我茅塞顿开；呵呵，现在就是如何找个变通的方法解决这个问题。


我现在的pl/sql程序，其实没有加for update ;

但是因为游标写的是：

for (select ... from sqlserver的某个表@sqlserver的dblink名字)
loop
  commit;
end loop;

而sqlserver是读锁的，所以我觉得就相当于加了for update;


其实，我遇到了很多怪异的现象，如果游标里是两个sqlserver的表关联就没问题了，就是：


for (select ... from sqlserver的某个表@sqlserver的dblink名字 join sqlserver的另个表@sqlserver的dblink名字 on... )
loop
  commit;
end loop;

这种写法就不报错，太诡异了；

gateWays好像听起来挺玄，但是用起来陷阱很多的。

[ 本帖最后由 qingyun 于 2010-8-19 23:02 编辑 ]

guostong

-- 再回头看看你的code
begin
  for rec in (select * from test for update ) -- 这里出错，并且是在commit之后出错，如果只有一行数据就不会出错
loop
    commit;
end loop;
end;

for update 的 cursor 对整个结果集加锁，commit 会释放锁，同时结果集也不可用了，因此当你再fetch的时候，就出错了。

qingyun

用数组做游标的中介，也就是首先把游标的数据copy到数组里；
然后通过循环数组做处理，这样循环数组的内部就可以任意写commit,rollback;

按照这个思路，我程序写出来了，

下面凡是ERP_开头的表，其实都是sqlserver的表，我用同义词转了一下；


--同步客户资料(业务单位表)
  PROCEDURE SYNC_CUST(p_xgsj date,
                      p_code out pls_integer,
                      p_msg  out varchar2) IS
    v_date1 NUMBER;
    v_date2 NUMBER;
   TYPE TArr_ERP_KHZL IS TABLE OF ERP_KHZL%ROWTYPE INDEX BY BINARY_INTEGER;
    Arr_ERP_KHZL TArr_ERP_KHZL;
    i            simple_integer := 0;
    V_SNYC_A_CNT SIMPLE_INTEGER := 0;
    V_SNYC_U_CNT SIMPLE_INTEGER := 0;
  
    PROCEDURE reglog IS
    BEGIN
      v_date2 := dbms_utility.get_time;
      Crea_sync_Log('同步客户资料',
                    p_code,
                    p_msg,
                    'ERP_SYNC.SYNC_CUST',
                    TO_CHAR((v_date2 - v_date1) / 100));
    END;
  
  BEGIN
      v_date1      := dbms_utility.get_time;
       V_SNYC_A_CNT := 0;
       V_SNYC_U_CNT := 0;
  
    FOR REC IN (SELECT
               
                   A.KHID, --1、客户ID
                    A.KHDH, --2、客户代号
                 A.KHJC, --3、客户简称
                 A.KHMC, --4、客户名称
                 A.XGSJ --5、修改时间
                  FROM ERP_KHZL A --业务单位表
                 where A.XGSJ > p_xgsj
               
                ) --客户资料
   
     LOOP
      i := i + 1;
      Arr_ERP_KHZL(i) := rec;
     END LOOP;
  
    for j in Arr_ERP_KHZL.first .. Arr_ERP_KHZL.last loop
      --  dbms_output.put_line(Arr_ERP_KHZL(j).khid);
      UPDATE CUST A
         SET A.CUST_ID         = Arr_ERP_KHZL(j).KHID, --1、客户内码
             A.CUST_NO         = Arr_ERP_KHZL(j).KHDH, --2、客户编号
             A.CUST_NAME       = Arr_ERP_KHZL(j).KHMC, --3、客户名称
             A.CUST_SHORT_NAME = Arr_ERP_KHZL(j).KHJC, --4、客户简称
             A.UPDATE_TIME     = Arr_ERP_KHZL(j).XGSJ --6、更新时间
      
       WHERE A.CUST_ID = Arr_ERP_KHZL(j).KHID;
   
      if sql%rowcount = 0 then
        INSERT INTO CUST --客户资料
          (CUST_ID, --1、客户内码
           CUST_NO, --2、客户编号
           CUST_NAME, --3、客户名称
           CUST_SHORT_NAME, --4、客户简称
           CREA_TIME, --5、创建时间
           UPDATE_TIME --6、更新时间
           
           )
        VALUES
          (Arr_ERP_KHZL(j).KHID,
           Arr_ERP_KHZL(j).KHDH,
           Arr_ERP_KHZL(j).KHMC,
           Arr_ERP_KHZL(j).KHJC,
           Arr_ERP_KHZL(j).XGSJ,
           Arr_ERP_KHZL(j).XGSJ);
        COMMIT;
        V_SNYC_A_CNT := V_SNYC_A_CNT + 1;
      
      ELSE
        COMMIT;
        V_SNYC_U_CNT := V_SNYC_U_CNT + 1;
      END IF;
   
      commit;
    end loop;
  
    --同步完之后，修改max_read_id
    UPDATE ERP_SYS_IFDB_JCSJ_GC --基本信息识别记录表-冈村
       SET YDXGSJ = p_xgsj --3、已读修改时间
     WHERE TABLENAME = 'KHZL'; --2、接口表名
    COMMIT; --非常重要，否者会导致分布式事务不一致；
    p_code := 0;
    p_msg  := '同步客户信息成功,添加' || to_char(V_SNYC_A_CNT) || '条' || '修改' ||
              to_char(V_SNYC_U_CNT) || '条';
    reglog;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      p_code := SQLCODE;
      p_msg  := SUBSTR(SQLERRM, 1, 100);
      reglog;
   
  END;

[ 本帖最后由 qingyun 于 2010-8-20 00:55 编辑 ]