前阵子有一个网友在群里问了一个关于 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). 关于这个,这里不展开叙说。
以上所述就是小编给大家介绍的《Oracle关于TX锁的一个有趣的问题》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:- acme.sh 续期问题(路径问题)
- 缓存的一些问题和一些加密算法【缓存问题】
- 如何把设计问题转化为数学问题(方法论)
- 推荐系统中的冷启动问题和探索利用问题
- GraphQL 教程(六)—— N+1问题和缓存等问题
- Golang 并发问题(四)之单核上的并发问题
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
面向对象分析与设计
Grady Booch、Robert A. Maksimchuk、Michael W. Engel、Bobbi J. Young、Jim Conallen、Kelli A. Houston / 王海鹏、潘加宇 / 人民邮电出版社 / 2009-8 / 79.00元
《面向对象分析与设计(第3版)》是UML创始人Grady Booch的代表作之一,书中介绍的概念都基于牢固的理论基础。同时,《面向对象分析与设计(第3版)》又是一本注重实效的书,面向架构师和软件开发者等软件工程实践者的实际需要。《面向对象分析与设计(第3版)》通过大量例子说明了基本概念,解释了方法,并展示了在不同领域的成功应用。全书分为理论和应用两部分。理论部分深刻剖析了面向对象分析与设计(OOA......一起来看看 《面向对象分析与设计》 这本书的介绍吧!