Oracle-enq:TX-row-contention等待场景

牛牛的笔记

于 2022-05-19 19:56:22 发布

阅读量407

点赞数

分类专栏： Oracle 文章标签： oracle 数据库 sql dba database

本文链接：https://blog.csdn.net/sinat_36757755/article/details/124870155

版权

Oracle 专栏收录该内容

105 篇文章 19 订阅

订阅专栏

本文详细探讨了数据库中导致TX锁等待的各种场景，包括应用代码逻辑错误、事务管理不当、主键和唯一键冲突、位图索引问题、大事务回滚以及慢SQL和事务查询调用等。通过具体的示例展示了如何分析和解决这些锁等待问题，有助于优化数据库性能和避免死锁。

摘要由CSDN通过智能技术生成

前言：

产生TX锁等待的原因一般都是应用程序的逻辑问题，表，索引设计问题以及性能问题导致，本文主要讨论TX锁的等待产生场景。

等待场景:

1 应用代码逻辑层设计问题导致同时修改相同数据引发锁等待。

2 事务没有正常commit/rollback引发锁等待。

3 主键或者唯一键冲突引发锁等待。

4 位图索引引发锁等待。

5 大事务回滚导致的锁等待。

6 慢SQL导致的锁等待。

7 查询包含事务调用导致的锁等待。

表数据:

table_name:test.test_1
 SQL> desc test.test_1;
 Name             Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID             NOT NULL NUMBER(13)
 NAME1                VARCHAR2(30)
 NAME2                VARCHAR2(30)
 NAME3                VARCHAR2(30)
 STATUS               VARCHAR2(2)
 primary key(id)
 bitmap index(status)
 
  ID NAME1              NAME2        NAME3        STATUS
----------  ------------------------------ ------------------------------ ------------------------------ --
   1 1aaaaaa        1bbbbbbbbb       1ccccccccc      1
   2 2aaaaaa        2bbbbbbbbb       2ccccccccc      0
   3 3aaaaaa        3bbbbbbbbb       3ccccccccc      0
   4 4aaaaaa        4bbbbbbbbb       4ccccccccc      0
   5 5aaaaaa        5bbbbbbbbb       5ccccccccc      1

场景一:应用代码逻辑层设计问题导致同时修改相同数据引发锁等待。

session:sid:32

#执行一条update，更新id=1的数据
SQL> update test.test_1
set name1='1bbbb'
where id=1;  

1 row updated

session:sid:45

#执行一条update，更新id=1的数据
#会话45执行被会话32堵塞，TX锁级别为6
SQL> update test.test_1
set name1='2bbbb'
where id=1; 
---->blocking

BLOCKING_STATUS
--------------------------------------------------------------------------------
TEST@rac1 ( SID=32 )  is blocking SYS@rac1 ( SID=45 )

有时还会引发死锁问题

session:sid:32

#执行一条update，更新id=2的数据
SQL> update test.test_1
set name1='2bbbb'
where id=2; 

1 row updated.

session:sid:45

#执行一条update，更新id=1的数据
SQL> update test.test_1
set name1='1bbbb'
where id=1;

1 row updated.

session:sid:32

#执行一条update，更新id=1的数据
#会话32执行被会话45堵塞，TX锁级别为6
SQL> update test.test_1
set name1='1aaaa'
where id=1;
---->blocking

BLOCKING_STATUS
--------------------------------------------------------------------------------
SYS@rac1 ( SID=45 )  is blocking TEST@rac1 ( SID=32 )

session:sid:45

#执行一条update，更新id=2的数据
#会话45执行被会话32堵塞，TX锁级别为6
SQL> update test.test_1
set name1='2bbbb'
where id=2;
---->blocking

BLOCKING_STATUS
--------------------------------------------------------------------------------
TEST@rac1 ( SID=32 )  is blocking SYS@rac1 ( SID=45 )

session:sid:32

#此时会话32报00060死锁错误，执行的sql被回滚但事务并没回滚
SQL> update test.test_1
set status=0
where id=1; 
update test.test_1
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

场景二:事务没有正常commit/rollback引发锁等待。

1 会话出现超时导致死链接，导致事务无法正常提交。

2 代码逻辑设计出现问题导致没有完成commit/rollback流程，特别是在对一些异常捕获的流程处理。

3 数据库性能问题，bug导致的无法正常commit/rollback。

场景三:主键或者唯一键冲突引发锁等待。

session:sid:32

#会话32插入一条主键为6的数据
insert into test.test_1 values(6,'6aaaaaa','6bbbbbbb','6ccccccc',0);

1 row created.

session:sid:45

#会话45也插入一条主键为6的数据
#会话45执行被会话32堵塞，TX锁级别为4
insert into test.test_1 values(6,'6aaaaaa','6bbbbbbb','6ccccccc',0);
---->blocking
BLOCKING_STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST@rac1 ( SID=32 )  is blocking SYS@rac1 ( SID=45 )

   INST_ID    SID TY  ID1     ID2 OWNER        OBJECT_NAME           LMODE    REQUEST
---------- ---------- -- ---------- ---------- -------------------- ---------------------------------------- ---------- ----------
   1     32 TX     720926    2932                                   6    0
   1     32 TM      88060       0 TEST        TEST_1                3    0
   1     45 TX     983040    2339                                   6    0
   1     45 TM      88060       0 TEST        TEST_1                3    0
   1     45 TX     720926    2932                                   0    4

需要等到会话32提交了事务，会话45才会报唯一键冲突。

ERROR at line 1:
ORA-00001: unique constraint (TEST.SYS_C0011112) violated

场景四:位图索引引发锁等待。

session:sid:32

#会话32更新表test_1 id=1的数据
SQL> update test.test_1
     set status=2
     where id=1;
     
1 row updated.

session:sid:45

#会话45更新test_1 id=2的数据
#会话45执行被会话32堵塞，TX锁级别为4
SQL> update test.test_1
  2  set status=3
  3  where id=2;
---->blocking
BLOCKING_STATUS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST@rac1 ( SID=32 )  is blocking SYS@rac1 ( SID=45 )

   INST_ID    SID TY  ID1     ID2 OWNER        OBJECT_NAME           LMODE    REQUEST
---------- ---------- -- ---------- ---------- -------------------- ---------------------------------------- ---------- ----------
   1     32 TX    1114133    4282                                      6    0
   1     32 TM      88060       0 TEST        TEST_1                   3    0
   1     45 TX    1179656    2547                                      6    0
   1     45 TM      88060       0 TEST        TEST_1                   3    0
   1     45 TX    1114133    4282

由于位图索引使用位图来记录数据，不同的DML更新不同的行可能争用同一段位图段，所以会产生锁等待。

场景五:大事务回滚导致的锁等待。

session:sid:55

#会话55更新表test_1 id=2的2百万数据
SQL> update test.test_1       
set name1='aaaaaaaaaaa'
where id=2;
#从操作系统kill了会话55，让会话55事务进行回滚
kill -9 15699

ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 15699
Session ID: 55 Serial number: 3

session:sid:29

#会话29更新表test_1 id=2数据
#会话55执行被会话29堵塞，TX锁级别为6
update test.test_1       
set name1='xxx'
where id=2 and rownum<2;
---->blocking
BLOCKING_STATUS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST@rac1 ( SID=55 )  is blocking SYS@rac1 ( SID=29 )
#堵塞的会话正是当前正在回滚的事务
INST_ID ADDR         XIDUSN    XIDSLOT     XIDSQN     UBAFIL   UBABLK     UBASQN     UBAREC STATUS         START_TIME      START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR    FLAG SPA REC NOU PTX
------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------ ------------ ------------ ------------ ---------------- ---------- --- --- --- ---
NAME                                             PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN     DSCN-B    DSCN-W  USED_UBLK  USED_UREC     LOG_IO     PHY_IO   CR_GET
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
 CR_CHANGE START_DAT  DSCN_BASE  DSCN_WRAP  START_SCN DEPENDENT_SCN XID        PRV_XID        PTX_XID
---------- --------- ---------- ---------- ---------- ------------- ---------------- ---------------- ----------------
      1 000000007E907E60   17      14       4285     5    49614        885     66 ACTIVE         05/19/22 18:28:08       5967266    0     0  5   226    751        40 00000000827AABC8  7683 NO  NO  NO  NO
                                              0       0    0     0        0    0      0         0      43346    4194304   12749349      73104  2185496
   2092747 19-MAY-22        0    0    5967266      0 11000E00BD100000 0000000000000000 0000000000000000

场景六:慢SQL导致的锁等待。

1 事务里面包含多个执行sql或者存在慢SQL导致提交时间延长。

#执行一条update，更新id=1的数据,并执行数据查询
#这时候数据查询的速度会直接影响事务的提交速度，如果查询过慢，就可能出现锁等待的情况
SQL> update test.test_1
set name1='1bbbb'
where id=1;
SQL> select count(*) from test.test_2 where object_id=1;

2 自身DML操作语句执行效率过低，如全表扫描，或者多表关联操作。

场景七:查询包含事务调用导致的锁等待。

#创建一个带匿名事务的函数
create or replace function fun_test return varchar2
as
pragma autonomous_transaction;
begin
  update test.test_1
  set name1='aaa'
  where id='6';
  commit;
  return 1;
end;
/

session:sid:52

#会话52执行带函数的查询
select fun_test from dual;

session:sid:29

#会话29执行update id=6
#会话29执行被会话52堵塞，TX锁级别为6,直到会话52执行语句结束
update test.test_1
set name1='xxx'
where id=6 and rownum<2;
---->blocking
BLOCKING_STATUS
--------------------------------------------------------------------------------
TEST@rac1 ( SID=52 )  is blocking SYS@rac1 ( SID=29 )