-
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系统下如何使用开发者选项?正确..2023-06-10
- win10系统下如何使用硬盘重装win7系统..2021-03-18
- ssd硬盘win10系统加上机械硬盘后启动很..2021-02-26
- win10电脑字体文件夹在哪里?..2022-03-24
- 如何取消win10 64位操作中心_win10关闭操..2021-05-09
- win10恢复出厂设置要多久_win10强制恢复..2021-07-10