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