mysql事务未提交导致锁等待如何解决
程序员文章站
2024-01-13 20:06:34
...
1、实验环境
Myql版本5.7.17-log
实验表结构
([email protected]ocalhost)[apex]> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table| Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
|test | CREATE TABLE `test` (
`x` int(11) NOT NULL,
`y` int(11) DEFAULT NULL,
PRIMARY KEY (`x`)
)ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row inset (0.01 sec)
插入数据
([email protected])[apex]> insert into test values(1,1);
([email protected])[apex]> insert into test values(2,2);
([email protected])[apex]> insert into test values(3,3);
2、锁产生步骤
会话一:开启事务,更新数据,不提交
([email protected])[apex]> begin;
QueryOK, 0 rows affected (0.00 sec)
([email protected])[apex]> update test set y=y+1 where x=1;
QueryOK, 1 row affected (0.00 sec)
Rowsmatched: 1 Changed: 1 Warnings: 0
查看当前连接id号(线程id号)
([email protected])[apex]> select connection_id();
+-----------------+
|connection_id() |
+-----------------+
| 4 |
+-----------------+
1 row inset (0.00 sec)
会话二:开启另一个事务,更新同一行数据,
([email protected])[apex]> begin;
QueryOK, 0 rows affected (0.00 sec)
([email protected])[apex]> update test set y=y+1 where x=1;
ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transaction
执行update test set操作时,会卡在那边,不执行,经过50秒后,会报错;
(上面的卡住现象,是由于锁,可以通过查看表information_schema.innodb_lock,获取锁的状态)
([email protected])[information_schema]> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
|lock_id | lock_trx_id | lock_mode| lock_type | lock_table | lock_index| lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
|757082:3279:3:2 | 757082 | X | RECORD | `apex`.`test` | PRIMARY | 3279 | 3 | 2 | 1 |
|757081:3279:3:2 | 757081 | X | RECORD | `apex`.`test` | PRIMARY | 3279 | 3 | 2 | 1 |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rowsin set, 1 warning (0.00 sec)
查看当前连接id号(线程id号)
([email protected]) [apex]> selectconnection_id();
+-----------------+
|connection_id() |
+-----------------+
| 5 |
+-----------------+
1 row inset (0.00 sec)
以上说的50秒,是系统参数innodb_lock_wait_timeout决定的
([email protected])[apex]> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
|Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout| 50 |
+--------------------------+-------+
1 row inset (0.00 sec)
3、mysql 如何查看未提交的事务
方法一:
([email protected])[performance_schema]> SELECT * FROMinformation_schema.INNODB_TRX\G
***************************1. row ***************************
trx_id: 756996
trx_state: RUNNING
trx_started: 2017-05-08 15:08:07
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 4
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
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
1 row inset (0.00 sec)
通过以上可看出线程id为4 一直未提交,事务开始的时间为2017-05-08 15:08:07。
方法二:通过 show engine innodb status\G
其中有一段关于事务的描述
TRANSACTIONS
------------
Trx idcounter 756998
Purgedone for trx's n:o < 0 undo n:o < 0 state: running but idle
Historylist length 0
LIST OFTRANSACTIONS FOR EACH SESSION:
---TRANSACTION421519065333360, not started
0 lockstruct(s), heap size 1136, 0 row lock(s)
---TRANSACTION421519065332448, not started
0 lockstruct(s), heap size 1136, 0 row lock(s)
---TRANSACTION756996, ACTIVE 914 sec
2 lockstruct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140041791522560, query id25 localhost root
从以上也可以看出线程id号为4的事务一直未提交。
4、如何解决未提交的事务
方法一:如果能知道哪个用户在执行这个操作,让他提交一下(这种可能性很小)
方法二:kill掉这个线程id号,让事务回滚,
([email protected])[information_schema]> show processlist;
+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+
| Id |User | Host | db | Command | Time | State | Info |
+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 4469 | Waiting on empty queue | NULL |
| 4 | root | localhost | apex | Sleep | 871| | NULL |
| 5 | root | localhost | apex | Sleep | 82| | NULL |
| 6 | root | localhost | information_schema | Query | 0| starting | showprocesslist |
| 7 | root | 192.168.1.1:3708 | NULL | Sleep | 3221 | | NULL |
+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+
5 rowsin set (0.00 sec)
([email protected])[information_schema]> kill 4;
QueryOK, 0 rows affected (0.01 sec)
转载于:https://blog.51cto.com/corasql/1923427