干货 | Oracle数据库有把TX锁，如何定位锁在哪？

[[220269]]

数据库是一个多用户使用的共享资源，为了保证数据的一致性，加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁，加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

数据库包含排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）两种基本的锁类型，利用这两种基本的锁类型来对数据库的事务进行并发控制。ORACLE数据库根据保护的对象不同，将Oracle数据库锁分为以下几大类：DML锁（data locks，数据锁），用于保证并发情况下的数据完整性；DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；内部锁和闩（internal locks and latches），保护数据库的内部结构。

这里我们只讨论DML锁。在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。当Oracle 执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。

在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务***发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时，***个会话在该条记录上加锁，其他的会话处于等待状态。当***个会话提交后，TX锁被释放，其他会话才可以加锁。

当Oracle数据库发生TX锁等待时，若处理不及时常会引起Oracle数据库挂起或死锁，产生ORA-00060的错误，导致应用出现长时间未响应、大量事务失败等问题。

如何处理TX锁等待

当数据库中发生enq: TX – row lock contention行级锁等待时，可以查询v$session.blocking_session列或v$lock视图来找到阻塞源，通过kill阻塞源来快速使业务恢复正常。

如何定位TX锁具体行数据

在某些情况下，用户想要了解经常发生TX锁等待的具体是哪些数据。下面来展示如何获取TX锁的具体行数据。

1、SQL语句中没有使用绑定变量

Session 1：

SQL> update t1 set b=10 where a=3;

Session 2：

SQL> update t1 set b=99 where a=3;

此时，可以通过v$sql和v$session视图联合查询来获取具体行数据：

select sql_text from v$sql a,v$session b where a.sql_id=b.sql_id and b.event='enq: TX - row lock contention';

可以得知t1表中a=3的行发生TX锁等待

2、SQL语句中使用绑定变量

Session 1：

SQL> variable v_a number; 
SQL> exec :v_a :=3;
SQL> update t1 set b=10 where a=:v_a;

Session 2：

SQL> variable v_a number;  
SQL> exec :v_a :=3;  
SQL> update t1 set b=99 where a=:v_a;

通过v$sql和v$session联合查询：

select sql_text from v$sql a,v$session b where a.sql_id=b.sql_id and b.event='enq: TX - row lock contention';

可以发现得到的是带有变量的SQL，无法定位具体的行。

此时可以通过以下SQL得到具体锁行信息：

SELECT row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#  
FROM v$session  
WHERE event='enq: TX - row lock contention';

这4个列所代表的意思如下：

object_id为14255的对象，在4号文件的133块中的第1行数据中发生等待（数据是从0行开始的）

根据object_id得到表名和data_object_id

SQL> select owner||'.'||object_name tab_name ,data_object_id from dba_objects where object_id=14255;  
TEST.T1   14296

再通过函数ROWID_CREATE转换得到ROWID

SQL> select dbms_rowid.ROWID_CREATE(1, 14296,4,133,1) from dual;  ---14296指的是dba_objects.data_object_id  
AAADfYAAEAAAACFAAB 
 
SQL> select * from TEST.T1 where rowid='AAADfYAAEAAAACFAAB';  
A  B  
3  4

可以发现，发生锁等待的正是此行数据。

注：此种方法同样适用于没有使用绑定变量情况

如何避免TX锁等待

避免使用select for update方式查询数据

修改完数据后尽可能尽快提交

需要修改大量数据时，避免在业务高峰期间进行；如果可能，拆分成多个事务分批修改提交

附(dbms_rowid.ROWID_CREATE函数)

关于dbms_rowid.ROWID_CREATE函数，需要注意的是其中OBJECT_NUMBER的输入值是dba_objects.data_object_id。

FUNCTION ROWID_CREATE RETURNS ROWID 
 
Argument Name         Type              In/Out Default? 
 
------------------------------ ----------------------- ------ -------- 
 
ROWID_TYPE                 NUMBER                 IN 
 
OBJECT_NUMBER          NUMBER                 IN  
 
RELATIVE_FNO              NUMBER                  IN 
 
BLOCK_NUMBER            NUMBER                 IN 
 
ROW_NUMBER               NUMBER                 IN