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

在上一篇文章“深入研究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 182565Purge done for trx's n:o < 182565 undo n:o < 0 state: running but idleHistory list length 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421524582451936, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 182560, ACTIVE 3 sec3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 11, OS thread handle 140049254979328, query id 105 localhost rootTABLE LOCK table `testdb1`.`t_test_1` trx id 182560 lock mode ISRECORD 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 SRecord 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 183051Purge done for trx's n:o < 183046 undo n:o < 0 state: running but idleHistory list length 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421137823890144, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 183050, ACTIVE 5 sec1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1MySQL thread id 5, OS thread handle 139662496147200, query id 17 localhost rootTABLE 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表的查询条件字段，建议创建索引。