1、实验环境
Myql版本5.7.17-log
实验表结构
|
1
2
3
4
5
6
7
8
9
10
11
|
(root@localhost)[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)
|
插入数据
|
1
2
3
|
(root@localhost)[apex]>
insert
into
test
values
(1,1);
(root@localhost)[apex]>
insert
into
test
values
(2,2);
(root@localhost)[apex]>
insert
into
test
values
(3,3);
|
2、锁产生步骤
会话一：开启事务，更新数据，不提交
|
1
2
3
4
5
|
(root@localhost)[apex]>
begin
;
QueryOK, 0
rows
affected (0.00 sec)
(root@localhost)[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号）
|
1
2
3
4
5
6
7
|
(root@localhost)[apex]>
select
connection_id();
+
-----------------+
|connection_id() |
+
-----------------+
| 4 |
+
-----------------+
1 row inset (0.00 sec)
|
会话二：开启另一个事务，更新同一行数据，
|
1
2
3
4
5
|
(root@localhost)[apex]>
begin
;
QueryOK, 0
rows
affected (0.00 sec)
(root@localhost)[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，获取锁的状态）
|
1
2
3
4
5
6
7
8
|
(root@localhost)[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号）
|
1
2
3
4
5
6
7
|
(root@localhost) [apex]> selectconnection_id();
+
-----------------+
|connection_id() |
+
-----------------+
| 5 |
+
-----------------+
1 row inset (0.00 sec)
|
以上说的50秒，是系统参数innodb_lock_wait_timeout决定的
|
1
2
3
4
5
6
7
|
(root@localhost)[apex]> show variables
like
'innodb_lock_wait_timeout'
;
+
--------------------------+-------+
|Variable_name | Value |
+
--------------------------+-------+
| innodb_lock_wait_timeout| 50 |
+
--------------------------+-------+
1 row inset (0.00 sec)
|
3、mysql 如何查看未提交的事务
方法一：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
(root@localhost)[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
其中有一段关于事务的描述
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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号，让事务回滚，
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
(root@localhost)[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)
(root@localhost)[information_schema]> kill 4;
QueryOK, 0
rows
affected (0.01 sec)
|
本文转自 corasql 51CTO博客，原文链接：http://blog.51cto.com/corasql/1923427，如需转载请自行联系原作者
