【mysql|mysql 死锁详细分析(三)】
文章目录
- 1、测试场景
- 2、测试数据准备
- 3、操作前检查
- 4、死锁测试操作
- 5、step3会话1被阻塞和step4死锁分析
-
-
- 5.1 查看innodb当前的锁(字段含义见 8.3)
- 5.2 查看锁的等待关系(字段含义见 8.4)
- 5.3 查看当前运行的所有事务信息
- 5.4 分析:
-
- 1. 先查看information_schema.innodb_trx
- 2. 再查看锁等待关系information_schema.INNODB_LOCK_WAITS
- 3. 再查看当前innodb所有的锁信息information_schema.INNODB_LOCKS
- 4. 会话1和会话2各自持有的锁
-
- 7、生产死锁原因分析
-
-
- 7.1 查看死锁的详细信息
- 7.2 逐步分析
-
- 8、mysql分析锁的参考命令
-
- 8.1关闭间隙锁:
- 8.2 information_schema.innodb_trx字段说明–当前运行的所有事务
- 8.3 information_schema.innodb_locks字段说明–当前出现的锁
- 8.4 information_schema.innodb_lock_waits字段说明–锁等待的对应关系
- 9、锁的优化建议
1、测试场景
- MySQL版本: 5.7.34
- 事务级别: RR(可重复读)
- 提交模式:手动
-- 准备表
CREATE TABLE `user_1` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`age` int DEFAULT NULL COMMENT '年龄',
`name` varchar(255)DEFAULT NULL COMMENT '姓名',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户信息表';
-- 先插入2条基础数据,并提交事务
insert into user_1 values (null, 10, 'aa');
insert into user_1 values (null, 20, 'ee');
注意:
- name是普通索引
- age没有索引
- 检查事务模式和事务隔离级别
- 使用DataGrip打开两个会话窗口
- 已写入的测试数据
- 原始数据如下:
mysql> select * from user_1;
+----+------+------+
| id | age| name |
+----+------+------+
|1 |10 | aa|
|2 |20 | ee|
+----+------+------+
2 rows in set (0.00 sec)
4、死锁测试操作 ① 会话1先执行:执行成功
-- step1:update name= 'ee',不提交事务
## 执行成功
update user_1 set age = 21 where name= 'ee';
② 会话2再执行: 执行成功
-- step2:update name= 'aa',不提交事务
## 执行被阻塞
update user_1 set age = 11 where name= 'aa';
③ 会话1再执行:执行被阻塞**
-- step3:插入cc,不提交
insert into user_1 values (null, 15, 'cc');
问题:step3中,此处会话1为什么被阻塞,在等待什么?
④ 会话2再执行:发生死锁**
-- step4:插入cc,不提交
insert into user_1 values (null, 30, 'gg');
5、step3会话1被阻塞和step4死锁分析 5.1 查看innodb当前的锁(字段含义见 8.3)
- 注意:以下操作是执行完step3,不提交事务
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 |
+-------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
| 5900:33:4:3 | 5900| X,GAP| RECORD| `mysql`.`user_1` | idx_name|33 |4 |3 | 'ee', 2|
| 5901:33:4:3 | 5901| X,GAP| RECORD| `mysql`.`user_1` | idx_name|33 |4 |3 | 'ee', 2|
+-------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
5.2 查看锁的等待关系(字段含义见 8.4)
mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 5900| 5900:33:4:3| 5901| 5901:33:4:3|
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
5.3 查看当前运行的所有事务信息
(由于列比较多,所以改成竖排显示,(字段含义见 8.2))
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 5901
trx_state: RUNNING
trx_started: 2022-08-25 20:06:05
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 5
trx_mysql_thread_id: 3
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 4
trx_lock_memory_bytes: 1136
trx_rows_locked: 3
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
*************************** 2. row ***************************
trx_id: 5900
trx_state: LOCK WAIT
trx_started: 2022-08-25 20:06:01
trx_requested_lock_id: 5900:33:4:3
trx_wait_started: 2022-08-25 20:06:09
trx_weight: 6
trx_mysql_thread_id: 2
trx_query: /* ApplicationName=DataGrip 2021.1 */ insert into user_1 values (null, 15, 'cc')
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 4
trx_lock_memory_bytes: 1136
trx_rows_locked: 4
trx_rows_modified: 2
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
2 rows in set (0.00 sec)
5.4 分析:
1. 先查看information_schema.innodb_trx
- 会话1主要信息:【trx_id:5900 trx_state: LOCK WAIT trx_started:2022-08-25 20:06:01】
- 会话2主要信息:【trx_id:5901 trx_state: RUNNINGtrx_started:2022-08-25 20:06:05】
- 会话1的语句,事务5900在等待锁状态,请求的锁id是:【trx_requested_lock_id: 5900:33:4:3】
- 会话2的语句,事务5901是运行状态,没有等待锁
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 5900| 5900:33:4:3| 5901| 5901:33:4:3|
+-------------------+-------------------+-----------------+------------------+
- 当前持有锁的会话2事务是5901,请求锁的会话1事务是5900
- 会话1在等待会话2释放锁。
3. 再查看当前innodb所有的锁信息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 |
+-------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
| 5900:33:4:3 | 5900| X,GAP| RECORD| `mysql`.`user_1` | idx_name|33 |4 |3 | 'ee', 2|
| 5901:33:4:3 | 5901| X,GAP| RECORD| `mysql`.`user_1` | idx_name|33 |4 |3 | 'ee', 2|
+-------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
- 会话1和会话2的事务都是行级别排他锁和间隙锁(X,GAP)
- 并且锁住的索引是普通索引idx_name 【lock_index:idx_name】
- 锁的的字段值都是 ‘ee’, 2 【lock_data : ‘ee’, 2】
- 会话1的语句,事务5900在等待锁,锁定的行数是3【trx_rows_locked: 4 trx_rows_modified: 2】
- 会话2的语句,事务5901是运行状态, 锁定的行数是3【trx_rows_locked: 3 trx_rows_modified: 1】
4. 会话1和会话2各自持有的锁 ① 会话1先执行:执行成功
-- step1:update name= 'ee',不提交事务
## 执行成功
update user_1 set age = 21 where name= 'ee';
- name列上有普通的索引,所以UPDATE 操作不会进行表锁,而是的每条记录上设置一个排他的 next-key 锁。
- 当前表里有两条数据。可见锁定的范围是:(10,20],(20,+∝)
- 锁定总范围:(10,+∝)
-
文章图片
② 会话2再执行: 执行成功
-- step2:update name= 'aa',不提交事务
## 执行被阻塞
update user_1 set age = 11 where name= 'aa';
- 同样,当前表里有两条数据。可见锁定的范围是:(-∝,10],(10,20)
- 锁定总范围:(-∝,20)
文章图片
③ 会话1再执行:执行被阻塞**
-- step3:插入cc,不提交
insert into user_1 values (null, 15, 'cc');
- 复习下INSERT 加锁整个流程
- 首先对插入的间隙加插入意向锁(Insert Intension Locks)
- 如果该间隙已被加上了GAP锁或 Next-Key 锁,则加锁失败进入等待。如果没有,则加锁成功,表示可以插入。
文章图片
- 可以看到‘cc’是在’aa’和’ee’的gap之间,并且这个gap已经被加了间隙锁,所以step3会进入等待。
- 等待哪个锁呢?因为该插入是会话1执行的,所以此时等待的是会话2持有的间隙锁:(10,20)
-- step4:插入cc,不提交
insert into user_1 values (null, 30, 'gg');
文章图片
- 可以看到’gg’是在’ee’之后,并且这个gap也已经被加了间隙锁,所以step4会进入等待。
- 等待哪个锁呢?因为该插入是会话2执行的,所以此时等待的是会话1持有的间隙锁:(20,+∝)
- 接着便会发生死锁,由于mysql有死锁自动检测算法,会自动释放权重小的事务。
- 为什么会发生死锁呢?如下图分析:
时间 | 会话1 | 会话2 |
---|---|---|
T1 | update user_1 set age = 21 where name= ‘ee’; 持有锁(10,20],(20,+∝) | |
T2 | update user_1 set age = 11 where name= ‘aa’; 持有锁(-∝,10],(10,20) | |
T3 | insert into user_1 values (null, 15, ‘cc’); 等待会话2的锁(10,20) | |
T4 | insert into user_1 values (null, 30, ‘gg’); 等待会话1的锁(20,+∝) |
- 在T4时刻,会话1在等待会话2的锁(10,20),而会话2也在等待会话1的锁 (20,+∝),进入了相互等待,产生了死锁。
- 在发生死锁时,如果开启死锁检测,InnoDB一般都能通过算法(wait-for graph)自动检测到。
- 所以 生产上如果发生死锁,直接看到的是mysql自动检测到死锁,并且可已经自动释放权重小的事务。看不到当时事务的情况和锁等待的关系。此时分析死锁,要通过如下方法:
开启主动死锁检测,默认开启:
innodb_deadlock_detect=on
7.1 查看死锁的详细信息
mysql> show engine innodb status;
2022-08-25 20:06:36 0x4c38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 57 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 8 srv_active, 0 srv_shutdown, 7898 srv_idle
srv_master_thread log flush and writes: 7906
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 62
OS WAIT ARRAY INFO: signal count 59
RW-shared spins 0, rounds 63, OS waits 31
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 63.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-08-25 20:06:33 0x82a8
*** (1) TRANSACTION:
TRANSACTION 5900, ACTIVE 32 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 32748, query id 312 localhost 127.0.0.1 root update
/* ApplicationName=DataGrip 2021.1 */ insert into user_1 values (null, 15, 'cc')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:?
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5900 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2;
compact format;
info bits 0
0: len 2;
hex 6565;
asc ee;
;
1: len 4;
hex 80000002;
asc;
;
*** (2) TRANSACTION:
TRANSACTION 5901, ACTIVE 28 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 33448, query id 325 localhost 127.0.0.1 root update
/* ApplicationName=DataGrip 2021.1 */ insert into user_1 values (null, 30, 'gg')
*** (2) HOLDS THE LOCK(S):?
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5901 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2;
compact format;
info bits 0
0: len 2;
hex 6565;
asc ee;
;
1: len 4;
hex 80000002;
asc;
;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:?
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5901 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1;
compact format;
info bits 0
0: len 8;
hex 73757072656d756d;
asc supremum;
;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 5907
Purge done for trx's n:o < 5907 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284557950381568, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284557950379824, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5901, ACTIVE 31 sec
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 33448, query id 334 localhost 127.0.0.1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0;
buffer pool: 0
429 OS file reads, 198 OS file writes, 130 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.96 writes/s, 0.75 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.74 non-hash searches/s
---
LOG
---
Log sequence number 3401095
Log flushed up to3401095
Pages flushed up to 3401095
Last checkpoint at3400989
0 pending log flushes, 0 pending chkp writes
87 log i/o's done, 0.46 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137297920
Dictionary memory allocated 105651
Buffer pool size8192
Free buffers7765
Database pages427
Old database pages 0
Modified db pages0
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 393, created 34, written 89
0.00 reads/s, 0.00 creates/s, 0.42 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 427, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=15540, Main thread ID=18340, state: sleeping
Number of rows inserted 2, updated 6, deleted 0, read 24
0.02 inserts/s, 0.04 updates/s, 0.00 deletes/s, 0.07 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
7.2 逐步分析
- 查看最近检测到的死锁
------------------------
LATEST DETECTED DEADLOCK
------------------------
- 查看事务5900发生死锁时的信息
2022-08-25 20:06:33 0x82a8
*** (1) TRANSACTION:
-- **事务5900,在执行insert操作,活跃了32秒**
TRANSACTION 5900, ACTIVE 32 sec inserting
-- **该事务使用一个mysql表,有一个锁**
mysql tables in use 1, locked 1
-- **当前有4行记录被锁**
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
-- **当前事务的线程id、查询id和数据库ip及账号**
MySQL thread id 2, OS thread handle 32748, query id 312 localhost 127.0.0.1 root update
-- **当前事务发生死锁时的执行语句**
/* ApplicationName=DataGrip 2021.1 */ insert into user_1 values (null, 15, 'cc')-- **当前在等待的锁**
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-- **索引idx_name的插入意向间隙锁在等待。等待的锁位置是“heap no 3”**
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5900 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2;
compact format;
info bits 0
-- **等待锁的范围是(ee,未知)**
0: len 2;
hex 6565;
asc ee;
;
1: len 4;
hex 80000002;
asc;
;
- 查看事务5901发生死锁时的信息
*** (2) TRANSACTION:
**事务5900,在执行insert操作,活跃了32秒**
TRANSACTION 5901, ACTIVE 28 sec inserting
-- **该事务使用一个mysql表,有一个锁**
mysql tables in use 1, locked 1
-- **当前有4行记录被锁**
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
-- **当前事务的线程id、查询id和数据库ip及账号**
MySQL thread id 3, OS thread handle 33448, query id 325 localhost 127.0.0.1 root update
-- **当前事务发生死锁时的执行语句**
/* ApplicationName=DataGrip 2021.1 */ insert into user_1 values (null, 30, 'gg')-- **当前事务持有的锁**
*** (2) HOLDS THE LOCK(S):
-- **持有的锁是索引idx_name的间隙排他锁。持有的锁位置是“heap no 3”**
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5901 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2;
compact format;
info bits 0
-- **持有锁的范围是(ee,未知)**
0: len 2;
hex 6565;
asc ee;
;
1: len 4;
hex 80000002;
asc;
;
-- **等待的锁**
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
-- **索引idx_name的插入意向间隙锁在等待。等待的锁位置是“heap no 1”**
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5901 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1;
compact format;
info bits 0
-- **等待的锁范围是(未知,+∝)**
0: len 8;
hex 73757072656d756d;
asc supremum;
;
- 分析
- 通过该日志可以看到,事务5900在等待锁的位置是“heap no 3”,而此位置的锁在事务5901那里,而5901等待的锁位置是“heap no 1”,可能是死锁已经释放,所以日志看不出改位置的锁在事务5900那里。
- 检测到死锁,并回滚
*** WE ROLL BACK TRANSACTION (1)
- 此时回滚的事务1。
-- 查看数据库版本
select version();
-- 查看默认值
show variables like '%engine%';
-- 查看事务隔离级别
show global variables like 'tx_isolation';
-- 查看是否开启自动提交
show variables like 'autocommit';
-- 查看是否开启间隙锁
show variables like 'innodb_locks_unsafe_for_binlog';
-- 查看锁等待的对应关系
select * from information_schema.INNODB_LOCK_WAITS;
-- 查看innodb当前的事务和锁
select * from information_schema.innodb_trx;
mysql5.6版本后,可以通过下面的命令来进行锁的状态监控
set global innodb_status_output=on;
set global innodb_status_output_locks=on;
-- 开启锁的状态监控后,查看详细的innodeb信息
show engine innodb status;
8.1关闭间隙锁:
-- 1、查看是否开启间隙锁:show variables like 'innodb_locks_unsafe_for_binlog';
mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name| Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF|
+--------------------------------+-------+
-- 注意:默认值为OFF(0),即启用gap lock。
-- 这个参数最主要的作用就是控制innodb是否对gap加锁。
-- 但是,这一设置变更并不影响外键和唯一索引(含主键)对gap进行加锁的需要。
-- 2、关闭间隙锁(gap lock)方法:
-- 在my.cnf里面的[mysqld]添加:
innodb_locks_unsafe_for_binlog = 1
-- 重启MySQL后生效。
8.2 information_schema.innodb_trx字段说明–当前运行的所有事务
Field | Extra |
---|---|
trx_id | 事务ID |
trx_state | 事务状态 |
trx_started | 事务开始时间 |
trx_requested_lock_id | innodb_locks.lock_id |
trx_wait_started | 事务开始等待的时间 |
trx_weight | 事务权重 |
trx_mysql_thread_id | 事务线程ID |
trx_query | 具体SQL语句 |
trx_operation_state | 事务当前操作状态 |
trx_tables_in_use | 事务中有多少个表被使用 |
trx_tables_locked | 事务拥有多少个锁 |
trx_lock_structs | |
trx_lock_memory_bytes | 事务锁住的内存大小(B) |
trx_rows_locked | 事务锁住的行数 |
trx_rows_modified | 事务更改的行数 |
trx_concurrency_tickets | 事务并发票数 |
trx_isolation_level | 事务隔离级别 |
trx_unique_checks | 是否唯一性检查 |
trx_foreign_key_checks | 是否外键检查 |
trx_last_foreign_key_error | 最后的外键错误 |
trx_adaptive_hash_latched | |
trx_adaptive_hash_timeout |
Field | Extra |
---|---|
lock_id | 锁ID |
lock_trx_id | 拥有锁的事务ID |
lock_mode | 锁模式 |
lock_type | 锁类型 |
lock_table | 被锁的表 |
lock_index | 被锁的索引 |
lock_space | 被锁的表空间号 |
lock_page | 被锁的页号 |
lock_rec | 被锁的记录号 |
lock_data | 被锁的数据 |
Field | Extra |
---|---|
requesting_trx_id | 请求锁的事务ID |
requested_lock_id | 请求锁的锁ID |
blocking_trx_id | 当前拥有锁的事务ID |
blocking_lock_id | 当前拥有锁的锁ID |
- 尽量使用INSERT … ON DUPLICATE KEY UPDATE代替REPLACE INTO
- REPLACE INTO有很多副作用,比如自增id会快速增大; slave 提升为 master后,可能会发生duplicate key error
- 在能正确完成业务的前提下,为确保效率,尽量使用较低的隔离级别(必须避免脏读)
- 设计合理的索引并尽量使用索引访问数据,使加锁更准确,减少锁冲突的机会,提高并发能力
- 选择合理的事务大小,小事务发生锁冲突的概率小(事务越大,包含的SQL越多,可能包含更多的表资源和行资源的锁,增大了锁冲突的概率)
- 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以- 固定的顺序存取表中的行。这样可以大大减少死锁的机会
- 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响(其实等值查询也会加间隙锁)
- 不要申请超过实际需要的锁级别
- 除非必须,查询时不要显示加锁(在已提交读和可重复读隔离级别,MVCC提供了读取机制,不需要手动加锁)
推荐阅读
- mysql|mysql replace的死锁分析(二)
- spark|spark sql日常开发问题
- Web安全|网络安全--SQL注入读写文件
- Web安全|网络安全--SQL注入整型报错注入
- 项目中的那些事|Spark SQL执行多次join后越来越慢,最后出现OOM
- 数据库|md5解密
- Spring|SpringBoot整合常用组件
- SQL注入|SQLmap的下载和安装,以及其参数大全和使用教程(值得收藏)
- database|17@MySQL数据库读现象详解(脏读、幻读、不可重复读)