深入研究insert into select锁表故障（下）

落叶说Mysql数据库运维 2020-10-19

2126

在上一篇文章“深入研究insert into select语句锁表故障（上）”中，已经详细介绍了如何模拟insert into select锁表复盘，以及获取详细的Lock信息，和解决方案。

但是上篇文章，并没有完全将整个故障研究说完，下面就将未说完研究过程说完。

隔离级别

分析一个故障，一定要看数据库的隔离级别，在不同的隔离级别下，会有不一样的结果。

RR隔离级别

获取数据库的隔离级别

[root@localhost] 18:11:29 [testdb1]>show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.00 sec)`

模拟insert into select锁表

[root@localhost] 17:54:52 [testdb1]>begin;
Query OK, 0 rows affected (0.00 sec)


[root@localhost] 17:54:55 [testdb1]>insert into t_test_2 select * from t_test_1 where name like 'trest';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

获取lock信息

TRANSACTIONS
------------
Trx id counter 182565
Purge done for trx's n:o < 182565 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421524582451936, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 182560, ACTIVE 3 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140049254979328, query id 105 localhost root
TABLE LOCK table `testdb1`.`t_test_1` trx id 182560 lock mode IS
RECORD LOCKS space id 97 page no 4 n bits 72 index idx_t_test_1_name of table `testdb1`.`t_test_1` trx id 182560 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 10; hex 74726573742020202020; asc trest     ;;
 1: len 4; hex 80000001; asc     ;;


TABLE LOCK table `testdb1`.`t_test_2` trx id 182560 lock mode IX

RC隔离级别

获取数据库的隔离级别

[root@localhost] 18:14:58 [(none)]>show variables like '%iso%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.00 sec)

获取lock信息

[root@localhost] 18:15:39 [(none)]>show engine innodb status\G;
TRANSACTIONS
------------
Trx id counter 183051
Purge done for trx's n:o < 183046 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421137823890144, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 183050, ACTIVE 5 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 139662496147200, query id 17 localhost root
TABLE LOCK table `testdb1`.`t_test_2` trx id 183050 lock mode IX

看到没有，在隔离级别RC下，insert into t_test_2 select * from t_test_1 where name like 'trest'语句，在t_test_1表上没有任何的lock产生。

建议

在生产使用insert into t_test_2 select * from t_test_1 where name like 'trest'语句时，有以下2个建议

建议一：建议mysql数据库隔离级别使用RC
建议二：select表的查询条件字段，建议创建索引。

数据库

文章转载自落叶说Mysql数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

深入研究insert into select锁表故障（下）

隔离级别

RR隔离级别

RC隔离级别

建议

评论