Oracle关于TX锁的一个有趣的问题

前阵子有一个网友在群里问了一个关于 Oracle 数据库的 TX 锁问题，问题原文如下：

请教一个问题：   两个会话执行不同的 delete 语句，结果都是删除同一个行。先执行的会话里 where 条件不加索引走全表扫描，表很大，执行很慢；后执行的用 where 条件直接用 rowid 进行 delete 。   Oracle 的什么机制使第二个会话执行后一直是等待第一个会话结束的呢。

那么我们先动手实验一下，来看看这个问题吧，首先，我们需要一个数据量较大的表（数据量大，全表扫描时间长，方便构造实验效果） , 这里实验测试的表为 INV_TEST ，该表在字段 FINAL_GARMENT_FACTORY_CD 上没有索引。因为我们要构造一个 SQL 走全表扫描去删除数据。我们更新了两条记录 , 设置字段 FINAL_GARMENT_FACTORY_CD ='KLB' 。 如下所示：

SQL> SELECT  ROWID, T.FINAL_GARMENT_FACTORY_CD FROM TEST.INV_TEST T WHERE ROWNUM <=10;
 
ROWID              FINAL_GARM
------------------ ----------
AAC1coABNAAALEKAAA KLB
AAC1coABNAAALEKAAB GEG
AAC1coABNAAALEKAAC GEG
AAC1coABNAAALEKAAD GEG
AAC1coABNAAALEKAAE GEG
AAC1coABNAAALEKAAF KLB
AAC1coABNAAALEKAAG GEG
AAC1coABNAAALEKAAH GEG
AAC1coABNAAALEKAAI GEG
AAC1coABNAAALEKAAJ GEG

首先，在会话 1 （ SID=925 ）里面执行下面 SQL 语句，删除 FINAL_GARMENT_FACTORY_CD ='KLB' 的两条记录

SQL> SELECT USERENV('SID') FROM DUAL;
 
USERENV('SID')
--------------
           925
 
SQL> DELETE FROM TEST.INV_TEST WHERE FINAL_GARMENT_FACTORY_CD ='KLB';

在会话 1 （ SID=925) 执行后，我们在会话 2 （ SID=197) 里面执行一个 DELETE 语句（删除 ROWID ='AAC1coABNAAALEKAAA' 的记录），其实就是删除第一条 FINAL_GARMENT_FACTORY_CD ='KLB' 的记录。不过我们使用的是 ROWID 这个条件。

 
SQL> SELECT USERENV('SID') FROM DUAL;                                     
 
USERENV('SID')
--------------
           917
 
SQL> DELETE FROM TEST.INV_TEST WHERE ROWID ='AAC1coABNAAALEKAAA';

此时，在会话 3 ，我们使用下面 SQL 语句查询，就会发现会话 2 （ SID=917 ）被会话 1 （ SID=925 ）阻塞了。

SQL> COLUMN blockeduser FORMAT a30 
SQL> SET linesize 480
SQL> BREAK ON BlockingInst SKIP 1 ON BlockingSid skip 1 ON BlockingSerial SKIP 1 
SQL> SELECT DISTINCT s1.INST_ID         BlockingInst, 
  2                  s1.SID             BlockingSid, 
  3                  s1.SERIAL#         BlockingSerial, 
  4                  s2.INST_ID         BlockedInst, 
  5                  s2.SID             BlockedSid, 
  6                  s2.USERNAME        BlockedUser, 
  7                  s2.SECONDS_IN_WAIT BlockedWaitTime 
  8  FROM   gv$session s1, 
  9         gv$lock l1, 
 10         gv$session s2, 
 11         gv$lock l2 
 12  WHERE  s1.INST_ID = l1.INST_ID 
 13         AND l1.BLOCK IN ( 1, 2 ) 
 14         AND l2.REQUEST != 0 
 15         AND l1.SID = s1.SID 
 16         AND l1.ID1 = l2.ID1 
 17         AND l1.ID2 = l2.ID2 
 18         AND s2.SID = l2.SID 
 19         AND s2.INST_ID = l2.INST_ID 
 20  ORDER  BY 1, 
 21            2, 
 22            3 
 23  / 
 
BLOCKINGINST BLOCKINGSID BLOCKINGSERIAL BLOCKEDINST BLOCKEDSID BLOCKEDUSER  BLOCKEDWAITTIME
------------ ----------- -------------- ----------- ---------- ------------ ---------------
           1         925          11600           1        917 TEST         30

SQL> COL SID  FOR 999999;
SQL> COL USERNAME FOR A12;
SQL> COL MACHINE FOR A40;
SQL> COL TYPE FOR A10;
SQL> COL OBJECT_NAME FOR A32;
SQL> COL LMODE FOR A16;
SQL> COL REQUEST FOR A12;
SQL> COL BLOCK FOR 999999;
SQL> SELECT S.SID                             SID, 
  2         S.USERNAME                        USERNAME, 
  3         S.MACHINE                         MACHINE, 
  4         L.TYPE                            TYPE, 
  5         O.OBJECT_NAME                     OBJECT_NAME, 
  6         DECODE(L.LMODE, 0, 'None', 
  7                         1, 'Null', 
  8                         2, 'Row Share', 
  9                         3, 'Row Exlusive', 
 10                         4, 'Share', 
 11                         5, 'Sh/Row Exlusive', 
 12                         6, 'Exclusive')   LMODE, 
 13         DECODE(L.REQUEST, 0, 'None', 
 14                           1, 'Null', 
 15                           2, 'Row Share', 
 16                           3, 'Row Exlusive', 
 17                           4, 'Share', 
 18                           5, 'Sh/Row Exlusive', 
 19                           6, 'Exclusive') REQUEST, 
 20         L.BLOCK                           BLOCK 
 21  FROM   V$LOCK L, 
 22         V$SESSION S, 
 23         DBA_OBJECTS O 
 24  WHERE  L.SID = S.SID 
 25         AND USERNAME != 'SYSTEM' 
 26         AND O.OBJECT_ID(+) = L.ID1; 
 
    SID USERNAME     MACHINE                TYPE       OBJECT_NAME      LMODE            REQUEST   BLOCK
------- ------------ ------------------ ---------- ---------------- ---------------- ------------ -------
    917 TEST    DB-Server.localdomain      TM         INV_TEST         Row Exlusive     None          0
    925 TEST    DB-Server.localdomain      TM         INV_TEST         Row Exlusive     None          0
    925 TEST    DB-Server.localdomain      TX                          Exclusive        None          1
    917 TEST    DB-Server.localdomain      TX                          None             Exclusive     0

使用下面脚本，我们知道，会话 197 在 ROW_ID=AAC1coABNAAALEKAAA 这条记录上等待获取 TX 锁，从而导致他被阻塞了。

COL object_name FOR A32;
COL row_id FOR A32;
SELECT
     s.p1raw,
     o.owner,
     o.object_name,
     dbms_rowid.rowid_create(1,o.data_object_id,f.relative_fno,s.row_wait_block#,s.row_wait_row#) row_id
 FROM
     v$session s
     JOIN dba_objects o ON s.row_wait_obj# = o.object_id
     JOIN dba_segments m ON o.owner = m.owner
                            AND o.object_name = m.segment_name
     JOIN dba_data_files f ON s.row_wait_file# = f.file_id
                              AND m.tablespace_name = f.tablespace_name
 WHERE
     s.event LIKE 'enq: TX%'

其实到这里就可以回答之前网友的问题了。 其实很简单，就是 ORACLE 数据库的锁机制实现的。我们知道 TX 锁称为事务锁或行级锁。当 Oracle 执行 DML 语句时，系统自动在所要操作的表上申请 TM 类型的锁。当 TM 锁获得后，系统再自动申请 TX 类型的锁，并将实际锁定的数据行的锁标志位进行置位。

在数据行上只有 X 锁（排他锁）。在 Oracle 数据库中，当一个事务首次发起一个 DML 语句时就获得一个 TX 锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML 语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后， TX 锁被释放，其他会话才可以加锁。由于第一个 SQL 语句的执行计划走全表扫描，所以导致这个事务的时间很长，会话 2 就一直被阻塞，直到第一个会话提交或回滚。

另外，我们都知道在 Oracle 中实现了细粒度的行锁 row lock ，且在 ORACLE 的内部实现中没有使用基于内存的行锁管理器， row lock 是依赖于数据块本身实现的。换句话说判定一行数据究竟有没有没锁住，要求 Server Process 去 pin 住相应的 block buffer 并检查才能够发现。所以，对于会话 1 （ SID=925 ），我们无法定位到那些行获取了 TX 锁。这个可以参考 https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9533876300346704362

那么问题来了，对于会话 1 的 SQL 走全表扫描，找到 FINAL_GARMENT_FACTORY_CD ='KLB' 的记录就会在对应的数据行的锁标志进行置位。假如 FINAL_GARMENT_FACTORY_CD ='KLB' 的记录位于扫描位置的末端呢？ 这个实验会是什么样的结果呢？我们用下面 SQL 找出一些记录。

SELECT ROWID, T.* FROM INV_TEST T WHERE STOCK_DATE > SYSDATE -120

然后我们将其中一条记录使用下面脚本更新。

SQL> UPDATE INV_TEST SET FINAL_GARMENT_FACTORY_CD='KLB' WHERE ROWID='AAC1coAB4AAEuXrAAM';
 
1 row updated.
 
SQL> COMMIT;
 
Commit complete.

然后我们接下来继续上面实验， 不过第二个 SQL 是删除 ROWID='AAC1coAB4AAEuXrAAM' 这条记录，我们看看实验结果

SQL> SELECT USERENV('SID') FROM DUAL;
 
USERENV('SID')
--------------
           925
 
SQL> DELETE FROM INVSUBMAT.INV_TEST WHERE FINAL_GARMENT_FACTORY_CD ='KLB';

等了大概 10 秒左右，我们在会话 2 执行第二个 SQL ，发现这个时候，这个 SQL2 马上执行完成了。跟之前的实验现象完全不同

其实出现这样的现象，是因为第二个会话（ SID=917) 首先获取了这一行的 TX 锁， 而第一个会话由于走全表扫描，它还没扫描到这条记录。可以说在一个事务中，对记录持有 X 锁是有顺序和时间差的。也就是说会话（ SID=917 ）首先在一行上获取了 TX 锁。

另外需要注意的是：其实关于 Oracle 的 row lock 或 TX 锁，虽然很多时候我们把 TX lock 叫做 row lock ， 但是实际上它们是两回事。 row lock 是基于数据块实现的，而 TX lock 则是通过内存中的 ENQUEUE LOCK 实现的 . 它是一种保护共享资源的锁定机制，一个排队机制，先进先出 (FIFO). 关于这个，这里不展开叙说。