oracle 数据库中的行锁和死锁

1 什么是行锁，为什么会发生行锁？

       当一条sql语句更新或删除一行数据时，事务只获得这一行的锁，获得的这个锁就是行锁。锁这一行的是某一个事务，而不是会话。oracle的规则是这样的，写一行数据库数据时加行锁，读一行数据不对行枷锁，写阻塞写，写不阻塞读，读不阻塞读和写。

      当多个事务同时更新（包括删除） 统一数据时（表或索引），就会发生行锁竞争，在Oracle等待事件里就是enq: TX - row lock contention enq，enq时队列的意思，在oracle数据库里同锁是一个意思。

      发生行锁竞争一个最常见的场合时多个会话同时更新或删除同一表的同一条数据，或者是会话更新一行数据的同时其它会话正在删除数据，反过来也一样。

      表上有唯一索引时，插入数据有时也会导致行锁，这主要时由于插入数据时唯一索引的值引起的冲突，产生唯一索引的值的方法不正确，是表设计时唯一索引的选择不正确的结果。

     产生行锁竞争的另一个常见的场景是位图索引，位图索引的一个值对应表中的多个值，虽然我们在表中更新的是不同的值，在位图索引中对应的是一个值，这样即使我们更新的表中的不同的值也会导致行锁竞争。这也是位图索引在oltp事务中被禁止使用的原因。

2 发生行锁时如何分析诊断？

     数据库发生行锁会导致怎样的后果。受到直接影响的发生行锁竞争的会话及应用，行锁竞争直接导致会话被阻塞，应用得不到响应。行锁竞争也会消耗大量的计算资源，从而影响整个数据库的性能。

      oracle数据库有多种工具可以用于行锁的诊断分析，如常用的awr报告及ash报告，也可以使用视图来对行锁进行分析。下面的是实验模仿一个最简单的行锁场景，对行锁进行分析及处理。

2.1 数据准备

   先创建一个简单的表，插入几行数据，表的主键在这里不是必须的。

SQL> create table test(id int primary key, name varchar2(20),salary number);

      Table created.

SQL> insert into test values (1, 'zhangsan', 2000);

      1 row created.

SQL> insert into test values (2, 'lisi', 2500);

      1 row created.

SQL> insert into test values (3, 'wangwu', 2100);

      1 row created.

2.2 模仿行锁

在第一个会话中执行以下操作

检查以下autocommit是否关闭，如果没有关闭，需要关闭后在执行sql语句。关闭的命令是：set autocommit off;

SQL> show auto

     autocommit OFF

autocommit已关闭

SQL> update test set salary=2000 where id=1;

    1 row updated.

另开一个会话,关闭自动提交后更新同一行数据

SQL> show autocommit;

autocommit OFF

SQL> update test set salary=2200 where id=1;

这个会话卡住了

2.3 行锁分析

      发生行锁时，数据库中会有被锁定的对象，这个可以从视图 v$locked_object查询到。

SQL>  select object_id ,session_id, locked_mode from v$locked_object;

           OBJECT_ID SESSION_ID LOCKED_MODE

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

                76280        378           3

                76280        471           3

      对象76280被会话378，和471 同时锁定，查询dba_objects 视图，可以知道被锁定的对象正是update语句更新的表格test。

       另一个视图v$lock可以看到数据库中锁的详细信息，该视图的type列的值是锁类型，用oracle的术语说是2字母的资源标识符，用户类型的有三个TM - DML enqueue（数据操作语言锁），

TX - Transaction enqueue（事务锁），UL - User supplied，其它的是系统锁。

SQL>  select SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK from v$lock where lmode in (3,6);

           SID TY        ID1        ID2      LMODE    REQUEST      BLOCK

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

           399 RT          1          0          6          0          0

           401 TS      65539          1          3          0          0

           393 KD          0          0          6          0          0

           401 TS     196611          1          3          0          0

           471 TX     655363        994          6          0          1

           378 TM      76280          0          3          0          0

           471 TM      76280          0          3          0          0

    7 rows selected.

     有一个视图dba进程会查询，这就是v$session视图，这个视图中可以看到会话及阻塞此会话的会话，等待事件等许多有用的信息。

SQL> select sid, BLOCKING_SESSION,BLOCKING_SESSION_STATUS,command, event from v$session where BLOCKING_SESSION is not null;

           SID BLOCKING_SESSION BLOCKING_SE    COMMAND EVENT

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

           378              471 VALID                6 enq: TX - row lock contention

从上面可以看到会话378被会话471阻塞，等待事件是enq: TX - row lock contention，如果我们想知道行锁竞争发生在哪个表的那行数据，v$session会话也提供了这个信息，用下面语句可以查询到：

SQL> select event,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK# ,ROW_WAIT_ROW# from v$session  where BLOCKING_SESSION is not null;

     EVENT                                                            ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#

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

     enq: TX - row lock contention                                            76280             12             134            0

      从语句的输入可以看到，行锁竞争发生的等待对象id是76280，正好是更新语句的对象id，数据文件id是12，块id是134，行号是0

    查询dba _objects对象可以获得更新的表的名称。

SQL> select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from dba_objects where DATA_OBJECT_ID=76280

     OWNER      OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

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

     TEST       TEST            76280          76280 TABLE

要想查询到行锁竞争发生在表中的哪一行，需要用到一个oracle的工具包dbms_rowid，这个工具包可以获得表中一行数据的对象id，文件号，块号，行id，运行下面的语句可以查询到行锁竞争发生在表的哪一行。

SQL> select     id ,

               rowid,

               dbms_rowid.rowid_object(rowid) "object",

               dbms_rowid.rowid_relative_fno(rowid) "file",

               dbms_rowid.rowid_block_number(rowid) "block",

               dbms_rowid.rowid_row_number(rowid) "row"

           from test.test where dbms_rowid.rowid_row_number(rowid)=0;  6    7

             ID ROWID                  object       file      block        row

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

              1 AAASn4AAMAAAACGAAA      76280         12        134          0

可以看到行锁竞争发生在test表中id为1 的行，这一行的信息和v$session表中的信息是一致的。在实际的行锁分析中，知道行锁竞争发生的具体信息有时十分有用，可以让我们快速找到导致行锁的应用，特别是多个应用执行同一语句的时候或者一个应用给多个业务部门使用时。

2.4 行锁处理

     发生行锁时处理的一般办法是找到阻塞的会话，在数据库或操作系统里杀死阻塞的对话。

SQL> select sid, serial# from v$session where sid in ( select distinct blocking_session from v$session);

      SID    SERIAL#

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

        1      36638

在数据库里用下面语句杀掉会话

SQL> alter system kill session '1,36638';

      System altered.

数据库的告警日志里有下面信息

2022-08-05T09:25:36.879896+08:00

ORCLPDB1(3):KILL SESSION for sid=(1, 36638):

ORCLPDB1(3):  Reason = alter system kill session

ORCLPDB1(3):  Mode = KILL SOFT -/-/-

ORCLPDB1(3):  Requestor = USER (orapid = 70, ospid = 25353, inst = 1)

ORCLPDB1(3):  Owner = Process: USER (orapid = 71, ospid = 25414)

ORCLPDB1(3):  User = oracle

ORCLPDB1(3):  Program = sqlplus@iZ2ze0t8khaprrpfvmevjiZ (TNS V1-V3)

ORCLPDB1(3):  Result = ORA-0

kill掉的会话已经从数据库里退出登录

SQL> select * from dual;

select * from dual

*

ERROR at line 1:

ORA-01012: not logged on

Process ID: 25414

Session ID: 1 Serial number: 36638

如果在数据库里无法杀掉阻塞会话，可以查到会话的操作系统pid后，使用kill或kill -9命令杀掉操作系统进程

SQL> select a.sid, b.spid from v$session a , v$process b

            where a.paddr=b.addr

             and a.sid in (select distinct blocking_session from   v$session);

      SID SPID

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

        1 25534

  也可以用下面的语句生成操作系统下kill会话的命令，

SQL> select 'kill -9 ' || b.spid ||';' kill_session from

       v$session a , v$process b

      where a.paddr=b.addr

      and a.sid in

            (select distinct blocking_session from v$session);

KILL_SESSION

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

kill -9 25534;

转到操作系统下直接运行上面的命令

[oracle@iZ2ze0t8khaprrpfvmevjiZ ~]$ kill -9 25534;

被杀到的会话状态如下所示：

SQL> select * from dual;

select * from dual

      *

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 25534

Session ID: 1 Serial number: 57400

数据库告警日志里面没有操作系统下kill 进程的信息。

3 死锁及死锁的诊断处理

3.1 Oracle对于死锁的定义及处理

Oracle官方对死锁的定义是这样的

     A deadlock is a situation in which two or more users are waiting for data locked by  each other. Deadlocks prevent some transactions from continuing to work.  Oracle Database automatically detects deadlocks and resolves them by rolling back   one statement involved in the deadlock, releasing one set of the conflicting row locks.

    The database returns a corresponding message to the transaction that undergoes   statement-level rollback. The statement rolled back belongs to the transaction that  detects the deadlock. Usually, the signalled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting.

    从上面可以看出，Oracle自动检测死锁，并回滚涉及到死锁的一个语句。数据库返回相应的信息给经历语句级回滚的事务。回滚的语句属于检测到死锁的事务。

3.2  模拟一个死锁

--session 1

查看一下会话的sid

SQL> select userenv('sid') from dual;

   USERENV('SID')

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

              425

关闭自动提交

SQL> set autocommit off;

SQL> show autoc

     autocommit OFF

main session作为监控会话

SQL>  select a.sid, b.pid from v$session a ,v$process b where a.paddr=b.addr and a.sid=425;

      SID        PID

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

      425         64

再打开另一个会话，作为会话2

--session2

SQL> set autocommit off;

SQL> show autoc

   autocommit OFF

按照顺序再会话1和2执行下面语句

--session 1

SQL> update test set salary=465;

--session2

SQL> update test2 set salary=473;

--session1

SQL> update test2 set salary=466;

--session2

SQL> update test set salary=474;

10秒之后，会话1报一下错误

update test2 set salary=466

                *

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

3.3 死锁的诊断

数据库的告警日志中含有一下信息

2022-08-01T14:48:39.788730+08:00

     ORCLPDB1(3):ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_13275.trc.

从告警日志中可以看到，更多的信息可以再会话进程的跟踪文件中看到

[oracle@iZ2ze0t8khaprrpfvmevjiZ trace]$ more /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_13275.trc

[Transaction Deadlock]

     The following deadlock is not an ORACLE error. It is a

     deadlock due to user error in the design of an application

     or from issuing incorrect ad-hoc SQL. The following

     information may aid in determining the deadlock:

     Deadlock graph:

                                               ------------Blocker(s)-----------  ------------Waiter(s)------------

     Resource Name                             process session holds waits serial  process session holds waits serial

     TX-000A0003-000004B0-79F3F24D-00000000         64     425     X        42850      65     456           X  46887

     TX-00070006-0000049A-79F3F24D-00000000         65     456     X        46887      64     425           X  42850

     ----- Information for waiting sessions -----

     Session 425:

       sid: 425 ser: 42850 audsid: 9310003 user: 109/TEST

       pdb: 3/ORCLPDB1

         flags: (0x41) USR/- flags2: (0x40009) -/-/INC

         flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-/-

       pid: 64 O/S info: user: oracle, term: UNKNOWN, ospid: 13275

         image: oracle@iZ2ze0t8khaprrpfvmevjiZ  client details:

         O/S info: user: oracle, term: pts/1, ospid: 13273

         machine: iZ2ze0t8khaprrpfvmevjiZ program: sqlplus@iZ2ze0t8khaprrpfvmevjiZ (TNS V1-V3)

         application name: SQL*Plus, hash value=3669949024

       current SQL:

       update test2 set salary=463

     Session 456:

       sid: 456 ser: 46887 audsid: 9340002 user: 109/TEST

       pdb: 3/ORCLPDB1

         flags: (0x41) USR/- flags2: (0x40009) -/-/INC

         flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-/-

       pid: 65 O/S info: user: oracle, term: UNKNOWN, ospid: 13432

         image: oracle@iZ2ze0t8khaprrpfvmevjiZ  client details:

         O/S info: user: oracle, term: pts/2, ospid: 13430

         machine: iZ2ze0t8khaprrpfvmevjiZ program: sqlplus@iZ2ze0t8khaprrpfvmevjiZ (TNS V1-V3)

         application name: SQL*Plus, hash value=3669949024

       current SQL:

       update test set salary=471

进程的跟踪文件中可以看到死锁的详细信息，如发生死锁的资源（这里时数据库里一行数据，每个资源的持有者，等待者，发生死锁时正在执行的语句等）。

   值得注意的是，Oracle在处理死锁问题时，只是做语句级的回滚，并不退出事务和会话，事务仍然需要提交或回滚。