Oracle、MySQL、达梦数据库锁阻塞问题对比和解决

本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/143830041

说明：

本文主要介绍Oracle、MySQL、达梦数据库锁阻塞问题定位、分析、解决。
Oracle数据库
版本:11.2.0.4

创建测试数据：

sqlplus cjc/******
create table t1(id int,name varchar(10));
insert into t1 values(1,'aaa');
insert into t1 values(2,'bbb');
insert into t1 values(3,'ccc');
commit;

模拟锁阻塞:
会话1：更新t1表id=3数据，没有commit提交，锁没有释放

SQL> select distinct SID from v$mystat;
       SID
----------
       161
SQL> update t1 set name='ccccc' where id=3;
SQL> SELECT * FROM t1;

会话2：更新t1表id=3数据，被会话1锁阻塞，update语句卡住

SQL> select distinct SID from v$mystat;
       SID
----------
       191
SQL> update t1 set name='ddddd' where id=3;

会话3：更新t1表id=3数据，被会话1锁阻塞，update语句卡住，不能执行完成

SQL> select distinct SID from v$mystat;
       SID
----------
       224
SQL> update t1 set name='eeeee' where id=3;

问题定位

会话4：

SQL> select distinct SID from v$mystat;
       SID
----------
         6

产生阻塞的会话是 161，191

select * from dba_blockers;
HOLDING_SESSION
---------------
            161
            191

阻塞链是 161 阻塞了 191， 191阻塞了224

set line 300
col LOCK_TYPE for a15
col MODE_HELD for a15
col  MODE_REQUESTED for a15
select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE       MODE_HELD       MODE_REQUESTED    LOCK_ID1   LOCK_ID2
--------------- --------------- --------------- --------------- --------------- ---------- ----------
            224             161 Transaction     Exclusive       Exclusive           327683       3199
            191             161 Transaction     Exclusive       Exclusive           327683       3199
            224             191 Transaction     None            Exclusive           327683       3199
            191             191 Transaction     None            Exclusive           327683       3199

查看阻塞和被阻塞会话信息

set line 300
col USERNAME for a10
col PROGRAM for a35
SELECT SID,SERIAL#,PADDR,TADDR,USERNAME,STATUS,SQL_ID,SQL_ADDRESS,LAST_CALL_ET,PROGRAM FROM V$SESSION WHERE SID IN (161,191,224);
       SID    SERIAL# PADDR            TADDR            USERNAME   STATUS   SQL_ID        SQL_ADDRESS      LAST_CALL_ET PROGRAM
---------- ---------- ---------------- ---------------- ---------- -------- ------------- ---------------- ------------ -----------------------------------
       161       3823 000000017A335170 0000000177D8C9A0 CJC        INACTIVE               00                        261 sqlplus@SATEST-DB-004 (TNS V1-V3)
       191       6977 000000017D2C01D8 0000000177D8BF90 CJC        ACTIVE   bk1su8cru4r7b 000000017B38C010         1472 sqlplus@SATEST-DB-004 (TNS V1-V3)
       224       1741 000000017A336228 0000000177E046F0 CJC        ACTIVE   atfkj56kuzvr5 000000015DD91198         1436 sqlplus@SATEST-DB-004 (TNS V1-V3)

Oracle 是多进程架构

SELECT SPID,ADDR FROM V$PROCESS WHERE ADDR IN ('000000017A335170','000000017D2C01D8','000000017A336228');
SPID                     ADDR
------------------------ ----------------
29187                    000000017A335170
29289                    000000017D2C01D8
29343                    000000017A336228
SQL> ho ps -ef|grep 29187
oracle   29187 29186  0 10:34 ?        00:00:00 oraclecjc (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
SQL> ho ps -ef|grep 29289
oracle   29289 29288  0 10:36 ?        00:00:00 oraclecjc (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
SQL> ho ps -ef|grep 29343
oracle   29343 29342  0 10:36 ?        00:00:00 oraclecjc (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

191 被阻塞的 SQL

SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='bk1su8cru4r7b';
SQL_TEXT
------------------------------------------------------------
update t1 set name='ddddd' where id=3

224 被阻塞的 SQL

SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='atfkj56kuzvr5';
SQL_TEXT
------------------------------------------------------------
update t1 set name='eeeee' where id=3

查看未结束的事物,V $TR A NS A CT I ON . A DD R = V$ SESSION.TADDR

SQL> select ADDR,STATUS,START_TIME,FLAG FROM V$TRANSACTION;
ADDR             STATUS           START_TIME                 FLAG
---------------- ---------------- -------------------- ----------
0000000177D8C9A0 ACTIVE           06/13/24 10:35:47          3587

解决方案：

      SID    SERIAL#
---------- ----------
       161       3823
       191       6977

kill 161会话:

alter system kill session '161,3823' immediate;

被 kill 的会话会断开连接

SQL> DESC V$SESSION;
ERROR:
ORA-03135: connection lost contact
Process ID: 29187
Session ID: 161 Serial number: 3823

kill 161会话后，191 会话的 update 不会被阻塞，如果不提交或回滚，将会继续阻塞 224 会话

Oracle 是单进程多线程架构，如果 alter system kill 没有立即中断会话，也可以通过操作系统 kill 命令终止对应会话。

例如前面查到，191 会话对于的 PID 是 29289，终止 191 会话。

ps -ef|grep 29289
kill -9 29289

也可以批量生产kill语句，根据实际情况之下kill_session列语句，谨慎操作。

COL kill_session FOR A60
select 
       'alter system kill session ' || '''' || sid || ',' || serial# || ',' || '@' ||
       inst_id || '''' || ' immediate;' as kill_session
  from gv$session
 where username='CJC';

例如：

KILL_SESSION
------------------------------------------------------------
alter system kill session '191,6977,@1' immediate;
alter system kill session '224,1741,@1' immediate;

MySQL 数据库

版本:8.0.32

查看参数

mysql> SELECT @@TRANSACTION_ISOLATION;
+-------------------------+
| @@TRANSACTION_ISOLATION |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 300   |
+--------------------------+-------+
1 row in set (0.00 sec)

修改锁等待超时间，默认300秒，临时调大，用于测试

mysql> set innodb_lock_wait_timeout=30000;
Query OK, 0 rows affected (0.00 sec)

超过锁等待时间，自动退出，有如下提示：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

测试数据:

mysql -uroot -p******
mysql> use cjc;
create table t1(id int,name varchar(10));
insert into t1 values(1,'aaa');
insert into t1 values(2,'bbb');
insert into t1 values(3,'ccc');

模拟锁阻塞:

会话1：Connection id:          26

开启事物

mysql> BEGIN;
mysql> update t1 set name='ccccc' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|1| aaa   |
|    2 | bbb   |
|3| ccccc |
+------+-------+
3 rows in set (0.00 sec)

会话2:Connection id: 27

mysql> use cjc;
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|1| aaa  |
|    2 | bbb  |
|3| ccc  |
+------+------+
3 rows in set (0.00 sec)
mysql> BEGIN;
mysql> update t1 set name='ddddd' where id=3;

卡住

会话3:Connection id: 28

mysql> BEGIN;
mysql> update t1 set name='eeeee' where id=3;

卡住

问题定位：Connection id: 30

查看正在运行的SQL：

mysql> select id,USER,HOST,DB,COMMAND,TIME,STATE,info from information_schema.processlist where info is not NULL and id !=30;
+----+------+-----------+------+---------+------+----------+---------------------------------------+
| id | USER | HOST      | DB   | COMMAND | TIME | STATE    | info                                  |
+----+------+-----------+------+---------+------+----------+---------------------------------------+
|27| root | localhost | cjc  | Query   |  249 | updating | update t1 set name='ddddd' where id=3 |
| 28 | root | localhost | cjc  | Query   |  199 | updating | update t1 set name='eeeee' where id=3 |
+----+------+-----------+------+---------+------+----------+---------------------------------------+
2 rows in set (0.00 sec)

查看正在运行的事务

mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 1419779
                 trx_state: LOCK WAIT
               trx_started: 2024-06-13 12:11:02
     trx_requested_lock_id: 140737100400152:45:4:2:140737018693184
          trx_wait_started: 2024-06-13 12:11:02
                trx_weight: 2
       trx_mysql_thread_id: 28
                 trx_query: update t1 set name='eeeee' where id=3
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: 1
*************************** 2. row ***************************
                    trx_id: 1419778
                 trx_state: LOCK WAIT
               trx_started: 2024-06-13 12:10:57
     trx_requested_lock_id: 140737100399344:45:4:2:140737018687168
          trx_wait_started: 2024-06-13 12:10:57
                trx_weight: 2
       trx_mysql_thread_id: 27
                 trx_query: update t1 set name='ddddd' where id=3
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: 1
*************************** 3. row ***************************
                    trx_id: 1419774
                 trx_state: RUNNING
               trx_started: 2024-06-13 11:28:04
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 26
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 4
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
3 rows in set (0.00 sec)
ERROR: 
No query specified

被锁阻塞的SQL信息

select trx_mysql_thread_id,trx_id,trx_query,trx_started,trx_state from information_schema.innodb_trx where trx_state='LOCK WAIT';
+---------------------+---------+---------------------------------------+---------------------+-----------+
| trx_mysql_thread_id | trx_id  | trx_query                             | trx_started         | trx_state |
+---------------------+---------+---------------------------------------+---------------------+-----------+
|                  28 | 1419779 | update t1 set name='eeeee' where id=3 | 2024-06-13 12:11:02 | LOCK WAIT |
|                  27 | 1419778 | update t1 set name='ddddd' where id=3 | 2024-06-13 12:10:57 | LOCK WAIT |
+---------------------+---------+---------------------------------------+---------------------+-----------+
2 rows in set (0.00 sec)

查看阻塞关系:BLOCKING_THREAD_ID 98阻塞了99和100,99阻塞了100。

mysql> SELECT BLOCKING_THREAD_ID,BLOCKING_EVENT_ID,REQUESTING_THREAD_ID,REQUESTING_EVENT_ID,BLOCKING_ENGINE_TRANSACTION_ID,REQUESTING_ENGINE_TRANSACTION_ID FROM performance_schema.data_lock_waits\G;
*************************** 1. row ***************************
              BLOCKING_THREAD_ID: 99
               BLOCKING_EVENT_ID: 23
            REQUESTING_THREAD_ID: 100
             REQUESTING_EVENT_ID: 17
  BLOCKING_ENGINE_TRANSACTION_ID: 1419778
REQUESTING_ENGINE_TRANSACTION_ID: 1419779
*************************** 2. row ***************************
              BLOCKING_THREAD_ID: 98
               BLOCKING_EVENT_ID: 45
            REQUESTING_THREAD_ID: 100
             REQUESTING_EVENT_ID: 17
  BLOCKING_ENGINE_TRANSACTION_ID: 1419774
REQUESTING_ENGINE_TRANSACTION_ID: 1419779
*************************** 3. row ***************************
              BLOCKING_THREAD_ID: 98
               BLOCKING_EVENT_ID: 45
            REQUESTING_THREAD_ID: 99
             REQUESTING_EVENT_ID: 23
  BLOCKING_ENGINE_TRANSACTION_ID: 1419774
REQUESTING_ENGINE_TRANSACTION_ID: 1419778
3 rows in set (0.00 sec)

其中：BLOCKING_THREAD_ID 对应的processlist_id如下

SELECT
b.HOST,
b.db,
b.USER,
a.THREAD_OS_ID "os_id",
a.THREAD_ID,
b.id "processlist_id",
b.command,
b.time,
b.state,
a.PROCESSLIST_INFO,
b.info "sql" 
FROM
PERFORMANCE_SCHEMA.THREADS a,
information_schema.PROCESSLIST b 
WHERE
b.id = a.processlist_id and b.id in (26,27,28);
+-----------+------+------+-------+-----------+----------------+---------+------+----------+---------------------------------------+---------------------------------------+
| HOST      | db   | USER | os_id | THREAD_ID | processlist_id | command | time | state    | PROCESSLIST_INFO                      | sql                                   |
+-----------+------+------+-------+-----------+----------------+---------+------+----------+---------------------------------------+---------------------------------------+
| localhost | cjc  | root | 27140 |        98 |             26 | Sleep   |  250 |          | NULL                                  | NULL                                  |
| localhost | cjc  | root |   611 |        99 |             27 | Query   | 1105 | updating | update t1 set name='ddddd' where id=3 | update t1 set name='ddddd' where id=3 |
| localhost | cjc  | root |  1013 |       100 |             28 | Query   | 1100 | updating | update t1 set name='eeeee' where id=3 | update t1 set name='eeeee' where id=3 |
+-----------+------+------+-------+-----------+----------------+---------+------+----------+---------------------------------------+---------------------------------------+
3 rows in set (0.00 sec)

查看 data_locks

mysql> SELECT THREAD_ID,ENGINE_TRANSACTION_ID,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM data_locks;
+-----------+-----------------------+---------------+-------------+-----------+-----------+-------------+------------------------+
| THREAD_ID | ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+-----------+-----------------------+---------------+-------------+-----------+-----------+-------------+------------------------+
|98|               1419774 | cjc           | t1          | TABLE     | IX        | GRANTED     | NULL                   |
|        98 |               1419774 | cjc           | t1          | RECORD    | X         | GRANTED     | supremum pseudo-record |
|98|               1419774 | cjc           | t1          | RECORD    | X         | GRANTED     | 0x000000049800         |
|        98 |               1419774 | cjc           | t1          | RECORD    | X         | GRANTED     | 0x000000049801         |
|98|               1419774 | cjc           | t1          | RECORD    | X         | GRANTED     | 0x000000049802         |
|       100 |               1419779 | cjc           | t1          | TABLE     | IX        | GRANTED     | NULL                   |
|100|               1419779 | cjc           | t1          | RECORD    | X         | WAITING     | 0x000000049800         |
|        99 |               1419778 | cjc           | t1          | TABLE     | IX        | GRANTED     | NULL                   |
|99|               1419778 | cjc           | t1          | RECORD    | X         | WAITING     | 0x000000049800         |
+-----------+-----------------------+---------------+-------------+-----------+-----------+-------------+------------------------+
9 rows in set (0.00 sec)

解决:
终止阻塞会话
kill 26;
也可以按条件批量生成kill语句，谨慎执行:

select concat('kill ',id,';') from information_schema.processlist where COMMAND !='Sleep' and user not in ('repl','event_scheduler') ORDER BY time desc limit 10;

达梦数据库:

版本:DM 8.1.3.62
数据库版本信息如下：

SQL> select BUILD_VERSION from v$instance;
行号     BUILD_VERSION                      
---------- -----------------------------------
1          1-3-62-2023.12.23-213044-20067-ENT 
SQL> select id_code();
行号     id_code()                                 
---------- ------------------------------------------
1          --03134284094-20231223-213044-20067 Pack23

测试数据:

SQL> create table t1(id int,name varchar(10));
SQL> insert into t1 values(1,'aaa');
SQL> insert into t1 values(2,'bbb');
SQL> insert into t1 values(3,'ccc');
SQL> commit;

模拟锁阻塞:
会话1：更新t1表id=3数据，更新后没有commit提交，锁没有释放

SQL> select sessid();
行号     sessid()            
---------- --------------------
1          127926984
SQL> update t1 set name='ccccc' where id=3;
SQL> SELECT * FROM t1;

会话2：更新t1表id=3数据，被会话1锁阻塞，update语句卡住，不能执行完成

行号     sessid()            
---------- --------------------
1          140736720183832
update t1 set name='ddddd' where id=3;

会话3：更新t1表id=3数据，被会话1锁阻塞，update语句卡住，不能执行完成

行号     sessid()            
---------- --------------------
1          140736285014184
update t1 set name='eeeee' where id=3;

会话4：

行号     sessid()            
---------- --------------------
1          140736416998632

问题定位:
通过V$TRXWAIT表能看到锁阻塞关系：

SQL> SELECT * FROM V$TRXWAIT;
行号     ID                   WAIT_FOR_ID          WAIT_TIME   THRD_ID    
---------- -------------------- -------------------- ----------- -----------
1          39943                39948                703117      3773
2          39944                39948                689783      3773
3          39944                39943                689783      3829
已用时间: 0.599(毫秒). 执行号:14412.

可以看到，事务ID=39948 的事务阻塞了 39943、39944，39943 阻塞了 39944；

通过事务ID查询SESSID和SQL等信息；

SQL> select SESS_ID,STATE,USER_NAME,TRX_ID,CREATE_TIME,RUN_STATUS,SQL_TEXT from v$sessions where TRX_ID IN (39943,39944,39948);
行号     SESS_ID              STATE  USER_NAME TRX_ID               CREATE_TIME                RUN_STATUS SQL_TEXT                              
---------- -------------------- ------ --------- -------------------- -------------------------- ---------- --------------------------------------
1          127926984            IDLE   CJC       39948                2024-05-10 15:34:49.000000 IDLE       SELECT * FROM t1;
2          140736720183832      ACTIVE CJC       39943                2024-05-10 15:35:00.000000 RUNNING    update t1 set name='ddddd' where id=3;
3          140736285014184      ACTIVE CJC       39944                2024-05-10 15:35:21.000000 RUNNING    update t1 set name='eeeee' where id=3;
已用时间: 1.229(毫秒). 执行号:14419.

可以看到：

SESSID=127926984，TRX_ID=39948 的会话阻塞了其他两个会话，被阻塞的语句分别是：

update t1 set name='ddddd' where id=3;
update t1 set name='eeeee' where id=3;

解决方案：
此时手动提交或回滚SESS_ID=127926984 ，TRX_ID=39948的会话既可以解决此问题；
或者和业务沟通能否强制kill SESS_ID=127926984 ，TRX_ID=39948 会话

语法如下：

call sp_close_session('SESS_ID');

其中阻塞源头 SESS_ID=127926984

执行：

call sp_close_session('127926984');

执行后，事务ID=39943的update t1 set name=‘ddddd’ where id=3;可以正常执行，在提交或回滚前，事务ID=39944的update t1 set name=‘eeeee’ where id=3;仍然是被阻塞状态。

###chenjuchao 20240613###

欢迎关注我的公众号"IT小Chen"