-
MySQL死锁检查处理的正常方法
- 作者:孙紫潇 分类:win10 发布时间:2021-07-25 09:32:00
正常情况下,死锁发生时,权重最小的连接将被kill并回滚。但是为了找出语句来优化,启用可启用死锁将死锁信息记录下来。 ?12345678910 #step 1:窗口一 mysql> start transaction ; mysql> update aa set name = 'aaa' where id = 1; #step 2:窗口二 mysql> start transaction ; mysql> update bb set name = 'bbb' where id = 1; #step 3:窗口一 mysql> update bb set name = 'bbb' ; #step 1:窗口一mysql> start transaction;mysql> update aa set name='aaa' where id = 1; #step 2:窗口二mysql> start transaction;mysql> update bb set name='b...
正常情况下,死锁发生时,权重最小的连接将被kill并回滚。但是为了找出语句来优化,启用可启用死锁将死锁信息记录下来。
?12345678910#step 1:窗口一
mysql> start
transaction
;
mysql>
update
aa
set
name
=
'aaa'
where
id = 1;
#step 2:窗口二
mysql> start
transaction
;
mysql>
update
bb
set
name
=
'bbb'
where
id = 1;
#step 3:窗口一
mysql>
update
bb
set
name
=
'bbb'
;
?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677#step 4:窗口三
#是否自动提交
mysql> show variables
like
'autocommit'
;
+
---------------+-------+
| Variable_name | Value |
+
---------------+-------+
| autocommit |
ON
|
+
---------------+-------+
#查看当前连接
mysql> show processlist;
mysql> show
full
processlist;
mysql>
SELECT
*
FROM
INFORMATION_SCHEMA.PROCESSLIST;
+
----+------+-----------+------+---------+------+-------+------------------+
| Id |
User
| Host | db | Command |
Time
| State | Info |
+
----+------+-----------+------+---------+------+-------+------------------+
| 4 | root | localhost | test | Sleep | 244 | |
NULL
|
| 5 | root | localhost | test | Sleep | 111 | |
NULL
|
| 6 | root | localhost |
NULL
| Query | 0 | init | show processlist |
+
----+------+-----------+------+---------+------+-------+------------------+
#查看当前正在被锁的事务(锁请求超时后则查不到)
mysql>
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 |
+
------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+
| 130718495:65:3:4 | 130718495 | X | RECORD | `test`.`bb` | GEN_CLUST_INDEX | 65 | 3 | 4 | 0x000000000300 |
| 130718496:65:3:4 | 130718496 | X | RECORD | `test`.`bb` | GEN_CLUST_INDEX | 65 | 3 | 4 | 0x000000000300 |
+
------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+
#查看当前等待锁的事务(锁请求超时后则查不到)
mysql>
SELECT
*
FROM
INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
猜您喜欢
- 轻松掌握win10看配置技巧,如何检查你..2024-03-26
- 揭秘!如何检查Windows 10是否已成功激..2024-03-24
- Win10提示无法连接打印机?检查打印机..2024-03-12
- Win10磁盘检查卡住?进不去系统?只需..2024-02-25
- 轻松一招:如何检查Win10是否激活,让..2024-02-24
- Win10小技巧:轻松关闭磁盘修复检查..2024-02-02
相关推荐
- Win10桌面背景被禁用,如何解锁你的桌..2024-02-20
- Win10笔记本电源已接通未充电怎么办?..2022-05-29
- 微软为Win10 PC推送最新16176快速预览版..2023-01-25
- Win10个性化主题桌面图标设置打不开?..2024-04-05
- Win10如何设置开机密码?Win10设置开机..2022-03-21
- win10系统下word2013打不开如何解决..2023-03-09