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). 关于这个，这里不展开叙说。