说明：
介绍Oracle、MySQL、达梦数据库锁阻塞问题定位、分析、解决。
一.Oracle数据库
版本:11.2.0.4
1.创建测试数据：
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;
2.模拟锁阻塞:
1).会话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).会话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).会话3：
更新t1表id=3数据,被会话1锁阻塞,update语句卡住,不能执行完成。
SQL> select distinct SID from v$mystat; SID ---------- 224 SQL> update t1 set name='eeeee' where id=3;
3.问题定位
1).会话4：
SQL> select distinct SID from v$mystat; SID ---------- 6
2).产生阻塞的会话是 161,191。
select * from dba_blockers; HOLDING_SESSION --------------- 161 191
3).阻塞链是161阻塞了191和224,191阻塞了224。
set line 300 col LOCK_TYPE for a15 col MODE_HELD for a15 col MODE_REQUESTED for a15 select * from dba_waiters;
4.查看阻塞和被阻塞会话信息
SELECT SID,SERIAL#,PADDR,TADDR,USERNAME,STATUS,SQL_ID,SQL_ADDRESS,LAST_CALL_ET,PROGRAM FROM V$SESSION WHERE SID IN (161,191,224);
1).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
SQL> ho ps -ef|grep 29289
SQL> ho ps -ef|grep 29343
2).191被阻塞的 SQL
SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='bk1su8cru4r7b'; SQL_TEXT ------------------------------------------------------------ update t1 set name='ddddd' where id=3
3).224被阻塞的 SQL
SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='atfkj56kuzvr5'; SQL_TEXT ------------------------------------------------------------ update t1 set name='eeeee' where id=3
4).查看未结束的事物
V$TRANSACTION.ADDR=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
5.解决方案：
SID SERIAL# ---------- --------- 161 3823 191 6977
1).kill 161会话:
alter system kill session '161,3823' immediate;
2).被 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
1.查看参数
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)
1).修改锁等待超时间,默认300秒,临时调大,用于测试.
mysql> set innodb_lock_wait_timeout=30000; Query OK, 0 rows affected (0.00 sec)
2).超过锁等待时间,自动退出,有如下提示：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
2.测试数据:
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).模拟锁阻塞:
会话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;
卡住
2).问题定位：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;
查看正在运行的事务
mysql> select * from information_schema.innodb_trx\G;
被锁阻塞的SQL信息
select trx_mysql_thread_id,trx_id,trx_query,trx_started,trx_state from information_schema.innodb_trx where trx_state='LOCK WAIT';
查看阻塞关系:
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;
其中：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);
查看 data_locks
mysql> SELECT THREAD_ID,ENGINE_TRANSACTION_ID,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM data_locks;
解决:
终止阻塞会话
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;
3.达梦数据库:
版本: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
1.测试数据:
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).模拟锁阻塞:
会话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
2.问题定位:
通过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);
可以看到：
SESSID=127926984,TRX_ID=39948 的会话阻塞了其他两个会话,被阻塞的语句分别是：
update t1 set name='ddddd' where id=3; update t1 set name='eeeee' where id=3;
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;仍然是被阻塞状态。
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
