使用PLSQL演示TM锁互斥现象

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 := ‘lock table ‘ || pkg_show_tm.tb_name ||
                           ‘ in ROW EXCLUSIVE mode nowait  ‘;
  g_statement(2).lockmode := ‘RX‘;

  g_statement(3).lockst := ‘lock table ‘ || pkg_show_tm.tb_name ||
                           ‘ in SHARE MODE nowait  ‘;
  g_statement(3).lockmode := ‘S‘;

  g_statement(4).lockst := ‘lock table ‘ || pkg_show_tm.tb_name ||
                           ‘ in SHARE ROW EXCLUSIVE MODE nowait  ‘;
  g_statement(4).lockmode := ‘SRX‘;

  g_statement(5).lockst := ‘lock table ‘ || pkg_show_tm.tb_name ||
                           ‘ in EXCLUSIVE MODE nowait  ‘;
  g_statement(5).lockmode := ‘X‘;

end;
/

最好用sys执行，不然需要额外授权grant execute on dbms_pipe to &user ;
_sys@FAKE10> create table t (x int) tablespace users ;

Table created.

Session 1 : 
_sys@FAKE10> exec pkg_show_tm.blocker ;

PL/SQL procedure successfully completed.






Session 2 :
_sys@FAKE10> set serveroutput on
_sys@FAKE10> exec pkg_show_tm.waiter ;
Space     RS        RX        S         SRX       X
************************************************************
RS        Yes       Yes       Yes       Yes       No
RX        Yes       Yes       No        No        No
S         Yes       No        Yes       No        No
SRX       Yes       No        No        No        No
X         No        No        No        No        No

PL/SQL procedure successfully completed.

思路其实很简单，要演示TM锁互斥的时候。需要开启2个session。例如：
session 1 执行
lock table tun2_tab in ROW SHARE mode ; 


session 2 依次执行
lock table tun2_tab in （ROW SHARE|ROW EXCLUSIVE|...) mode ;
查看session 2 是否发生了等待 。

这里 
session 1 = pkg_show_tm .blocker 
session 2 = pkg_show_tm.waiter