通过下面的查询, 来查询当前数据库, 有哪些事务,都锁定哪些资源。
trx_state
AS
`事务状态`,
trx_wait_started
AS
`事务开始等待时间`,
trx_tables_in_use
AS
`事务使用表`,
trx_tables_locked
AS
`事务拥有锁`,
trx_rows_locked
AS
`事务锁定行`,
trx_rows_modified
AS
`事务更改行`,
trx_mysql_thread_id AS '事物的线程ID'
information_schema.innodb_trx ;
查看事件比较久的事物。如何当前时间select sysdate();AM 10:07而事物开始的时间是:AM 9:50,显示是不正常的。根据这个事物的线程ID(trx_mysql_thread_id)。
执行命令:
kill 线程ID
数据库修复正常。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
1
2
3
4
5
6
7
|
追问:
没有很明白,能不能举例说下?谢谢
追答:
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
28
29
30
31
32
33
34
35
36
37
38
39
40
|
会话1:
mysql>
select
*
from
test_main;
+
----+---------+
| id | value |
+
----+---------+
| 1 | ONE |
| 2 | TWO |
| 3 | THREE-3 |
| 4 | FOUR-4 |
| 6 | SIX |
+
----+---------+
5
rows
in
set
(0.00 sec)
mysql> START
TRANSACTION
;
Query OK, 0
rows
affected (0.00 sec)
mysql>
UPDATE
test_main
SET
value =
'FOUR-4'
WHERE
id = 4;
Query OK, 0
rows
affected (0.00 sec)
Rows
matched: 1 Changed: 0 Warnings: 0
会话2:
mysql> START
TRANSACTION
;
Query OK, 0
rows
affected (0.00 sec)
mysql>
UPDATE
test_main
SET
value =
'THREE-3'
WHERE
id = 3;
Query OK, 0
rows
affected (0.00 sec)
Rows
matched: 1 Changed: 0 Warnings: 0
会话1:
UPDATE
test_main
SET
value =
'THREE-5'
WHERE
id = 3;
因为会话2 已经锁定了 id=3 的这行数据,
因此, 当前会话暂时不能更新被锁定行, 进入锁定等待状态......
|