ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How to find a user who is locking the rows

How to find a user who is locking the rows

原创 Linux操作系统 作者:yanggq 时间:2019-03-20 08:42:06 0 删除 编辑
PURPOSE
-------
The following programs will attempt to pinpoint all the locked rows of a table and tell who has the lock on each of them. 
 

SCOPE & APPLICATION

-------------------

 

So far this is the only known automatic method to obtain who is locking which row. This is very slow and is NOT bullet-proof.

It might block for a short moment other concurrent sessions accesing the table in question.

It has been tested only in Unix but should work on any O.S. It requires to be able to compile Pro*C Programs.

Due to all the PL/SQL features used, the programs require Oracle RDBMS 8.1.5 or greater.

 

The DB users executing the Programs need to have execution privileges on the dbms_pipe package, the 'alter system' privilege and access to v_$session and v_$lock;

Ej:

 grant execute on dbms_pipe to test;

 grant alter session to test;

 grant select on V_$SESSION to test;

 grant select on V_$lock; to test;

 

HOW IT WORKS

------------

The basic idea is to first locate all the rows that are locked and then find out who is locking it.

To first locate all the locked rows a cursor is build which will try to lock each row in the table. (Select for update nowait)If it success then that row is not locked by anyone.

If it fails it will fail with an ora-54 (See Note 18245.1) which its trapped with the use of an exception. This means this row is being locked.

Now that we know what rows are locked then we proceed to try to find who has the lock on it.

 

If we try to lock that row with a select for update the session will remain on a waiting state until it is freed. The information that this session is being blocked by another is found in the v$lock under the column request.

This line identifies which enqueue is being used in column id1. 

In this enqueue are all the requests to modifiy the same resource in this case the same row. Since only one person can modify the same row at the same time there will be a row in v$lock which will have in the block column greater than 0.  

This column tell us how many sessions are waiting on the same enqueue. In this row we will find the Session ID (SID column) which we can look up in v$session giving us the name of the owner of the lock.

 

The problem here is that we can't wait on a lock and do the query to find out who is blocking us at the same time with the same session, so we use another session to do the lock and wait on it until we obtain all the info.

Then, that other session can release the lock and we can proceed to the next row.

 

There are two programs in this article. 

A PL/SQL Procedure and a Pro*C Program.

The PL/SQL will find all the locked rows and pass the instruction to the Pro*C to try to put the lock on one of those rows.

After we obtain the info the PL/SQL will kill the session of the Pro*C which will reconect waiting for the next instruccion.

HOW TO USE IT

-------------

Modify the Pro*C with the proper user and password and compile it. If you name the program 'lock' A suggestion for compiling it is :

make -f demo_proc.mk EXE=lock OBJS=lock.o build PROCFLAGS="SQLCHECK=SEMANTICS userid=test/test"

 

In a seperate telnet session run the program.

 

Connect to a session with access to the table in question.

issue a SET SERVEROUTPUT ON

issue locksmith('with_your_table');

 

It is indispensable for the Pro*C program to run before the PL/SQL.

The Result will be like this :

 

Pro*C on telnet 1

-----------------

$ lock

Connecting.

Waiting.

Executing select 1 from EMP where rowid ='AAADWQAABAAAVrrAAJ' for update

Connecting.

Waiting.

Executing select 1 from EMP where rowid ='AAADWQAABAAAVrrAAN' for update

Connecting.

Waiting.

Exiting.   

 

PL/SQL on telnet 2

------------------

SQL> Set serveroutput on

SQL> exec locksmith('EMP');

Row AAADWQAABAAAVrrAAJ locked by SCOTT SID(15)

Row AAADWQAABAAAVrrAAN locked by BRUCE SID(9)

 

PL/SQL procedure successfully completed.

 

 ERROR MESSAGES

--------------

If the message 'failure probably due to desyncronization' appears at some point, kill the Pro*C program and reinitialize it before running the PL/SQL again.

This message also appears if the PL/SQL was executed before the Pro*C Program.

 

RELATED DOCUMENTS

-----------------

PL/SQL User's Guide and Reference Release 8.1.5

Oracle8 Server Reference Release 8.1.5

 

SOURCE CODE

------------

Pro*C

-----

 

#include 

#include 

EXEC SQL include sqlca;

 

EXEC SQL begin declare section;

  char *uid = "TEST/TEST";  /* User/password to connect to Oracle, 

                               Modify accordingly*/

  int status;                 /* Return value for dbms_pipe.send_message

                                 and dbms_pipe.receive_message */

  varchar command[20];        /* Command to execute */

  varchar value[2000];        /* SQL statement */

  int audsid;               /* The Audit Session Id to identify myself */

  

 

EXEC SQL end declare section;

 

/* This is the error handler for connecting to Oracle.  If we failed on the

   connection attempt, we need to exit the program. */

void connect_error() {

 

  char msg_buffer[512];

  int msg_length;

  int buffer_size = 512;

 

  EXEC SQL whenever sqlerror continue;

  sqlglm(msg_buffer, &buffer_size, &msg_length);

  printf("Error while connecting:n");

  printf("%.*sn", msg_length, msg_buffer);

  printf("Quitting.n");

  exit(1);

  }

 

 

main() {

 

 EXEC SQL EXECUTE

  begin

   dbms_pipe.purge('LockSmith');

   dbms_pipe.purge('Lock');

  end;

 END-EXEC;

 

 while (1) {

  EXEC SQL whenever sqlerror do connect_error();

  printf("Connecting.n");

  EXEC SQL connect :uid;

  EXEC SQL EXECUTE

   begin

    dbms_pipe.purge('LockSmith');

    select userenv('SESSIONID') into :audsid FROM DUAL;

    dbms_pipe.pack_message(:audsid);

    :status := dbms_pipe.send_message('LockSmith');

   end;

  END-EXEC;

 

  EXEC SQL whenever sqlerror continue;

 

  printf("Waiting.n");

 

  EXEC SQL EXECUTE

   begin

    :status := dbms_pipe.receive_message('Lock');

     if :status = 0 then

      dbms_pipe.unpack_message(:command);

     end if;

   end;

 

  END-EXEC;

 

  if (status == 0) {

    command.arr[command.len] = '';

    if (!strcmp((char *)command.arr, "STOP")) {

      printf("Exiting.n");

      exit(0);

    }

    else {

     EXEC SQL EXECUTE

      begin

       dbms_pipe.unpack_message(:value);

      end;

     END-EXEC;

     EXEC SQL whenever sqlerror continue;

     value.arr[value.len] = '';

     printf("Executing %sn",value.arr); 

     EXEC SQL EXECUTE IMMEDIATE :value;

     status = sqlca.sqlcode;

     EXEC SQL ROLLBACK WORK;

    }

  }

 }

}

 

 

PL/SQL

------

 

create or replace procedure locksmith(table_name varchar2) is

 type tabcurtype is ref cursor;

 type tabrowstype is table of varchar2(50) index by binary_integer;

 table_cursor tabcurtype;

 rowid_table tabrowstype;

 row_id rowid;

 status number;

 aud_sid number;

 test_sid number;

 test_serial number;

 locker_sid number;

 locker_name varchar2(1000);

 message varchar2(1000);

 

resource_busy exception;

pragma exception_init(resource_busy,-54);

 

begin

 rowid_table(0):='0';

 dbms_output.enable(1000000);

 open table_cursor for 'select rowid from '||table_name;

 loop

  begin

   fetch table_cursor into row_id;

   exit when table_cursor%notfound;

   savepoint one_register;

   execute immediate 'select 1 from '||table_name

           ||' where rowid =:r for update nowait' using row_id;

   exception 

    when resource_busy then

     rowid_table(rowid_table.last+1):=row_id;

  end;

  rollback to savepoint one_register;

 end loop;

 close table_cursor;

 

 for r in rowid_table.first+1 .. rowid_table.last loop

  status:=dbms_pipe.RECEIVE_MESSAGE('LockSmith',1);

  if status=0 then 

   dbms_pipe.unpack_message(aud_sid);

   begin

    select a.sid,a.serial# into test_sid,test_serial 

    from v$session a where a.audsid=aud_sid;

    dbms_pipe.pack_message('select 1 from '||table_name

       ||' where rowid ='''||rowid_table(r)||''' for update');

    status:=dbms_pipe.send_message('Lock'); 

 

    status:=0;

    loop

     begin 

      select sid,username into locker_sid,locker_name 

      from v$session c 

      where c.sid =( select sid 

                     from v$lock a where a.block>0

                     and a.id1 =(select b.id1 

                                 from v$lock b 

                                 where b.request=6 and b.sid=test_sid

                                )

                   );

      exit;

     exception

      when no_data_found then

      status:=status+1;

      exit when status>100;

     end;

    end loop;

    if status=<101 then 

    execute immediate 'alter system kill session '''

           ||to_char(test_sid)||','||to_char(test_serial)||'''';

    dbms_output.put_line('Row '||rowid_table(r)||' locked by '

           ||locker_name||' SID('||to_char(locker_sid)||')' );

    else

    dbms_output.put_line('Row '||rowid_table(r)||' was locked and released.' );

    end if;

   exception

   when no_data_found then

    null;

   end;

  else

   dbms_output.put_line('failure probably due to desyncronization');

   exit;

  end if;

 end loop;

 dbms_pipe.pack_message('STOP');

 status:=dbms_pipe.send_message('Lock'); 

end;

/

 

KEYWORDS

--------

v$lock v$session lock row enqueue ora-54 resource busy nowait

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26706/viewspace-64604/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle Wait Interface
下一篇: From a msg
请登录后发表评论 登录
全部评论

注册时间:2002-10-29

  • 博文量
    17
  • 访问量
    11953