Oracle关于TX锁的一个有趣的问题教程
前阵子有一个网友在群里问了一个关于Oracle数据库的TX锁问题，问题原文如下：
请教一个问题： 两个会话执行不同的delete语句，结果都是删除同一个行。先执行的会话里where条件不加索引走全表扫描，表很大，执行很慢；后执行的用where条件直接用rowid进行delete。 Oracle的什么机制使第二个会话执行后一直是等待第一个会话结束的呢。
那么我们先动手实验一下，来看看这个问题吧，首先，我们需要一个数据量较大的表（数据量大，全表扫描时间长，方便构造实验效果）, 这里实验测试的表为INV\_TEST，该表在字段FINAL\_GARMENT\_FACTORY\_CD上没有索引。因为我们要构造一个SQL走全表扫描去删除数据。我们更新了两条记录,设置字段FINAL\_GARMENT\_FACTORY\_CD ='KLB'。 如下所示：
<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> SELECT ROWID, T.FINAL_GARMENT_FACTORY_CD FROM TEST.INV_TEST T WHERE ROWNUM <=10;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">ROWID FINAL_GARM<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">------------------ ----------<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">AAC1coABNAAALEKAAA KLB<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">AAC1coABNAAALEKAAB GEG<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">AAC1coABNAAALEKAAC GEG<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">AAC1coABNAAALEKAAD GEG<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">AAC1coABNAAALEKAAE GEG<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">AAC1coABNAAALEKAAF KLB<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">AAC1coABNAAALEKAAG GEG<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">AAC1coABNAAALEKAAH GEG<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">AAC1coABNAAALEKAAI GEG<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">AAC1coABNAAALEKAAJ GEG首先，在会话1（SID=925）里面执行下面SQL语句，删除FINAL\_GARMENT\_FACTORY\_CD ='KLB'的两条记录
<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> SELECT USERENV('SID') FROM DUAL;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">USERENV('SID')<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">--------------<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 925<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">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这个条件。
<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">SQL> SELECT USERENV('SID') FROM DUAL; <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">USERENV('SID')<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">--------------<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 917<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">SQL> DELETE FROM TEST.INV_TEST WHERE ROWID ='AAC1coABNAAALEKAAA';此时，在会话3，我们使用下面SQL语句查询，就会发现会话2（SID=917）被会话1（SID=925）阻塞了。
<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> COLUMN blockeduser FORMAT a30 <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">SQL> SET linesize 480<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> BREAK ON BlockingInst SKIP 1 ON BlockingSid skip 1 ON BlockingSerial SKIP 1 <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">SQL> SELECT DISTINCT s1.INST_ID BlockingInst, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 2 s1.SID BlockingSid, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 3 s1.SERIAL# BlockingSerial, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 4 s2.INST_ID BlockedInst, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 5 s2.SID BlockedSid, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 6 s2.USERNAME BlockedUser, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 7 s2.SECONDS_IN_WAIT BlockedWaitTime <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 8 FROM gv$session s1, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 9 gv$lock l1, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 10 gv$session s2, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 11 gv$lock l2 <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 12 WHERE s1.INST_ID = l1.INST_ID <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 13 AND l1.BLOCK IN ( 1, 2 ) <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 14 AND l2.REQUEST != 0 <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 15 AND l1.SID = s1.SID <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 16 AND l1.ID1 = l2.ID1 <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 17 AND l1.ID2 = l2.ID2 <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 18 AND s2.SID = l2.SID <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 19 AND s2.INST_ID = l2.INST_ID <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 20 ORDER BY 1, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 21 2, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 22 3 <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 23 / <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">BLOCKINGINST BLOCKINGSID BLOCKINGSERIAL BLOCKEDINST BLOCKEDSID BLOCKEDUSER BLOCKEDWAITTIME<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">------------ ----------- -------------- ----------- ---------- ------------ ---------------<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 1 925 11600 1 917 TEST 30<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> COL SID FOR 999999;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">SQL> COL USERNAME FOR A12;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> COL MACHINE FOR A40;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">SQL> COL TYPE FOR A10;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> COL OBJECT_NAME FOR A32;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">SQL> COL LMODE FOR A16;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> COL REQUEST FOR A12;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">SQL> COL BLOCK FOR 999999;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> SELECT S.SID SID, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 2 S.USERNAME USERNAME, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 3 S.MACHINE MACHINE, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 4 L.TYPE TYPE, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 5 O.OBJECT_NAME OBJECT_NAME, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 6 DECODE(L.LMODE, 0, 'None', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 7 1, 'Null', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 8 2, 'Row Share', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 9 3, 'Row Exlusive', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 10 4, 'Share', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 11 5, 'Sh/Row Exlusive', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 12 6, 'Exclusive') LMODE, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 13 DECODE(L.REQUEST, 0, 'None', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 14 1, 'Null', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 15 2, 'Row Share', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 16 3, 'Row Exlusive', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 17 4, 'Share', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 18 5, 'Sh/Row Exlusive', <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 19 6, 'Exclusive') REQUEST, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 20 L.BLOCK BLOCK <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 21 FROM V$LOCK L, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 22 V$SESSION S, <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 23 DBA_OBJECTS O <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 24 WHERE L.SID = S.SID <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 25 AND USERNAME != 'SYSTEM' <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 26 AND O.OBJECT_ID(+) = L.ID1; <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> SID USERNAME MACHINE TYPE OBJECT_NAME LMODE REQUEST BLOCK<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">------- ------------ ------------------ ---------- ---------------- ---------------- ------------ -------<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 917 TEST DB-Server.localdomain TM INV_TEST Row Exlusive None 0<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 925 TEST DB-Server.localdomain TM INV_TEST Row Exlusive None 0<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 925 TEST DB-Server.localdomain TX Exclusive None 1<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 917 TEST DB-Server.localdomain TX None Exclusive 0使用下面脚本，我们知道，会话197在ROW\_ID=AAC1coABNAAALEKAAA 这条记录上等待获取TX锁，从而导致他被阻塞了。
<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">COL object_name FOR A32;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">COL row_id FOR A32;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SELECT<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> s.p1raw,<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> o.owner,<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> o.object_name,<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> dbms_rowid.rowid_create(1,o.data_object_id,f.relative_fno,s.row_wait_block#,s.row_wait_row#) row_id<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> FROM<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> v$session s<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> JOIN dba_objects o ON s.row_wait_obj# = o.object_id<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> JOIN dba_segments m ON o.owner = m.owner<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> AND o.object_name = m.segment_name<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> JOIN dba_data_files f ON s.row_wait_file# = f.file_id<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> AND m.tablespace_name = f.tablespace_name<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> WHERE<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> 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
然后我们将其中一条记录使用下面脚本更新。
<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> UPDATE INV_TEST SET FINAL_GARMENT_FACTORY_CD='KLB' WHERE ROWID='AAC1coAB4AAEuXrAAM';<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">1 row updated.<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> COMMIT;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">Commit complete.然后我们接下来继续上面实验， 不过第二个SQL是删除ROWID='AAC1coAB4AAEuXrAAM'这条记录，我们看看实验结果
<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">SQL> SELECT USERENV('SID') FROM DUAL;<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">USERENV('SID')<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4">--------------<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white"> 925<pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: #f4f4f4"> <pre style="border-top-style: none; overflow: visible; font-size: 11pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 11pt; padding-right: 0px; background-color: white">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). 关于这个，这里不展开叙说。