使用PLSQL演示TM锁互斥现象(一)

PLSQL代码如下：

create or replace package pkg_show_tm is

  -- Author  : DEX
  -- Created : 1/24/2013 8:29:26 PM
  -- Purpose : 演示TM锁的兼容性
  -- 需要事先定义表名，任意表即可
  -- Action  : Session 1  : exec pkg_show_tm.blocker ;
  --           Sesssion 2 : set serveroutput on ;            
  --                        exec pkg_show_tm.waiter ;
  tb_name varchar2(20) := 't' ;
  -- Public type declarations
  procedure blocker ;
  procedure waiter ;

end ;
/

create or replace package body pkg_show_tm is
  Type rec is record(
    lockst   varchar2(2000),
    lockmode varchar2(20));
  Type t is table of rec;
  g_statement t;
  /*
    进程间通信，发送msg
  */
  procedure send(msg varchar2) is
    stats integer;
  begin
    dbms_pipe.pack_message(item => msg);
    stats := dbms_pipe.send_message(msg);
    if stats != 0 then
      raise_application_error(-20999, msg || ' error in blocker ');
    end if;
  end;
  /*
    进程间通信，接收msg
  */
  procedure recive(msg varchar2) is
    stats integer;
  begin
    stats := dbms_pipe.receive_message(msg);
    if stats != 0 then
      raise_application_error(-20999, msg || ' error in waiter ');
    end if;
  end;
  /*
    输出：
    Space     RS        RX        S         SRX       X         
    ************************************************************
  */
  procedure output_title is
  begin
    dbms_output.put(rpad('Space', 10, ' '));
    dbms_output.put(rpad('RS', 10, ' '));
    dbms_output.put(rpad('RX', 10, ' '));
    dbms_output.put(rpad('S', 10, ' '));
    dbms_output.put(rpad('SRX', 10, ' '));
    dbms_output.put_line(rpad('X', 10, ' '));
    dbms_output.put_line(rpad('*', 60, '*'));
  end;
  
  /*
    session 1 = blocker 
    循环执行加锁操作 阻塞session 2
  */
  procedure blocker is
  begin
    for i in 1 .. g_statement.last loop
      execute immediate g_statement(i).lockst;
      send(g_statement(i).lockmode);                  --发送消息
    
      recive(g_statement(i).lockmode || 's');         --等待接收消息，以判断是否可以继续执行下一个加锁操作
      commit;
    end





 loop;
  end;
  /*
    session 2 = waiter 
    循环执行加锁操作，以判断与session 1 是否互斥
  */
  procedure waiter is
  begin
    output_title;
    /* output :
    Space     RS        RX        S         SRX       X         
    ************************************************************
    */
    for i in 1 .. g_statement.last loop
      recive(g_statement(i).lockmode);                --等待session 1 发送的消息，以判断session 1 是否已经成功加锁
      dbms_output.put(rpad(g_statement(i).lockmode, 10, ' '));
      /* output :
        Space     RS        RX        S         SRX       X         
        ************************************************************
        RS
      */
      for j in 1 .. g_statement.last loop
        /*
          这里如果session 2 中的加锁操作与session 1 如果互斥，则会爆出
          ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
          为了省事，直接加others捕获
        */
        begin
          execute immediate g_statement(j).lockst;
          dbms_output.put(rpad('Yes', 10, ' '));
          commit;
        exception
          when others then
            dbms_output.put(rpad('No', 10, ' '));
            commit;
        end;
      end loop;
      dbms_output.put_line(' ');
      /* output :
        Space     RS        RX        S         SRX       X         
        ************************************************************
        RS        Yes       Yes       Yes       Yes       No
      */
      send(g_statement(i).lockmode || 's');     --发送消息给session 1
    end loop;
  end;
/*
操作说明：
lock table tun2_tab in ROW SHARE mode ;           lmode=2
lock table tun2_tab in ROW EXCLUSIVE mode ;       lmode=3   
lock table tun2_tab in SHARE MODE ;               lmode=4
lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ; lmode=5
lock table tun2_tab in EXCLUSIVE MODE ;           lmode=6
*/
begin
  g_statement := t();

  g_statement.extend(5);

  g_statement(1).lockst := 'lock table ' || pkg_show_tm.tb_name ||
                           ' in ROW SHARE mode nowait ';
  g_statement(1).lockmode := 'RS';

  g_statement(2).lockst