【熟能生巧】查看Oracle DB Lock及快速处理

前情回顾

在上一篇中，我们执行了一个 update 语句，但是却被 block 住了，经过一番查询，发现是另一个人也在 update 同一个表，但是没有 commit 。

修改也很简单， commit 一下即可。

新的问题

我们有一个 Loader 模块，其作用是把数据从文件写入到数据库表中。

最终，执行的语句是这样的：

insert /*+ APPEND PARALLEL(4) */ into BIG_TABLE_A ...

在测试中，起了两个实例，但是，却发现 L1 阻塞住了 L2 。

分析

问题来了：为什么都是 insert 语句，一个会 block 住另一个呢？

按照我对于 Oracle 的认知，事务的默认隔离级别是 READ_COMMITTED 已提交读。写的时候加行级共享锁 RS ，直到事务结束才释放。
对应到这里，两个模块 L1 和 L2 其 insert 的内容独立，各自加行级共享锁的话，应该是互不影响的，怎么会发生阻塞呢？

首先，我们通过上一篇的查看锁的SQL，可以查询得知：

L1 SID(800) is blocking L2 SID(900)

所以，我们需要看看 SID=800 这个线程，到底 block 在哪里。

Lock Mode

查询这个锁的 Lock Mode ：

SELECT * FROM V$ENQUEUE_LOCK where sid = 800;

在结果中，可以看到有一列 LMODE = 6 。
表明， L1 使用的是 exclusive lock ，即锁全表。所以发生了阻塞。

进一步查询资料可知：两种情况可能进入 Exclusive Lock Mode：

1. 创建索引
2. 使用多线程直接插入

如下：

Value   Name(s)                    Table method (TM lock)
    0   No lock                    n/a
 
    1   Null lock (NL)             Used during some parallel DML operations (e.g. update) by
                                   the pX slaves while the QC is holding an exclusive lock.
 
    2   Sub-share (SS)             Until 9.2.0.5/6 "select for update"
        Row-share (RS)             Since 9.2.0.1/2 used at opposite end of RI during DML until 11.1
                                   Lock table in row share mode
                                   Lock table in share update mode
 
    3   Sub-exclusive(SX)          Update (also "select for update" from 9.2.0.5/6)
        Row-exclusive(RX)          Lock table in row exclusive mode
                                   Since 11.1 used at opposite end of RI during DML
 
    4   Share (S)                  Lock table in share mode
                                   Can appear during parallel DML with id2 = 1, in the PX slave sessions
                                   Common symptom of "foreign key locking" (missing index) problem
                                   Note that bitmap indexes on the child DON'T address the locking problem
 
    5   share sub exclusive (SSX)  Lock table in share row exclusive mode
        share row exclusive (SRX)  Less common symptom of "foreign key locking" but likely to be more
                                   frequent if the FK constraint is defined with "on delete cascade."
 
    6   Exclusive (X)              Lock table in exclusive mode
                                   create index    -- duration and timing depend on options used
                                   insert /*+ append */ 

第一种情况很很好理解，插入一条数据且需要创建索引的时候， table （理论上）可以只加行级锁，互不干扰。但是对于 index 来说，
L1/L2 各自的数据是相对连贯的，二叉树的插入和平衡比较快，但是如果交替进行，效率可能就不高。所以只能使用互斥锁，一个一个来。

第二种情况，Oracle的文档里面写到： direct-load INSERT ，需要在 table 级别使用 exclusive locks 。

In direct-load INSERT, exclusive locks are obtained on the table (or on all the partitions of a partitioned table) precluding any concurrent insert, update, or delete on the table. Concurrent queries, however, are supported and will see only the data in the table before the INSERT began. These locks also prevent any concurrent index creation or rebuild operations. This must be taken into account before using direct-load INSERT because it affects table concurrency.

参考 -> https://docs.oracle.com/cd/A87860_01/doc/server.817/a76965/c21dlins.htm

Lock Object

我们进一步看看 lock 的 object 是啥。

SELECT C.SID WAITER_SID, A.OBJECT_NAME, A.OBJECT_TYPE
FROM DBA_OBJECTS A, V$SESSION B, V$SESSION_WAIT C
WHERE (A.OBJECT_ID = B.ROW_WAIT_OBJ# OR
    A.DATA_OBJECT_ID = B.ROW_WAIT_OBJ#)
AND B.SID = C.SID
AND B.SID = 800
;

结果如下：

WAITER_SID    OBJECT_NAME          OBJECT_TYPE
800	      INDX_BIG_TABLE_A	   INDEX PARTITION

果然，是锁在 index 上。

解决

原有大概知道了，那怎么办呢？

1. 等。
2. 把一个低优先级的 session 停掉，让另一个先跑。

参考

• Diagnosing Oracle Wait for TX Enqueue in Mode 6 https://logicalread.com/diagnosing-oracle-wait-for-tx-enqueue-mode-6-mc01/
• Lock Modes https://jonathanlewis.wordpress.com/2010/06/21/locks/