现在的位置: 首页 > 关系型数据库 > SQL基本用法 > 正文

insert into a select * from b 锁问题分析

时间:2018年07月13日 | 分类:SQL基本用法 | 评论:0 条 | 浏览:125 次

很长一段时间,都认为"insert into a select * from b" 会导致b表被锁住。在最近一次组内同事的质疑中,发现错了.b表会不会被锁,其实和隔离级别有一定关系,下面我们具体分析一下

环境说明:
1.隔离级别:REPEATABLE-READ
2.MySQL版本:5.6.29
3.Binlog格式:ROW
4.建表SQL:

CREATE TABLE `os_diskio_history_bak` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`ip` varchar(50) NOT NULL,
`tags` varchar(100) DEFAULT NULL,
`fdisk` varchar(50) NOT NULL DEFAULT '0',
`disk_io_reads` bigint(18) NOT NULL DEFAULT '0',
`disk_io_writes` bigint(18) NOT NULL DEFAULT '0',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`YmdHi` bigint(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_ymdhi` (`YmdHi`) USING BTREE,
KEY `idx_ip_ymdhi` (`ip`,`YmdHi`),
KEY `idx_io_reads` (`disk_io_reads`),
KEY `idx_io_writes` (`disk_io_writes`)
) ENGINE=InnoDB AUTO_INCREMENT=484556 DEFAULT CHARSET=utf8

测试用例:
1.直接插入

终端1:
mysql> insert into os_diskio_history_bak select * from os_diskio_history;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 2
929 lock struct(s), heap size 112168, 207989 row lock(s), undo log entries 207063
mysql> show engine innodb status;
mysql tables in use 2, locked 2
1636 lock struct(s), heap size 194088, 363913 row lock(s), undo log entries 362280
mysql> show engine innodb status;
mysql tables in use 2, locked 2
1984 lock struct(s), heap size 226856, 440646 row lock(s), undo log entries 438666

从上面,我们看到 row lock 和 lock struct 在不停的增长,被锁的行数在不断增加

2.按主键降序插入

终端1:mysql> insert into os_diskio_history_bak select * from os_diskio_history order by id desc;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 2
502 lock struct(s), heap size 63016, 109585 row lock(s), undo log entries 109583
mysql> show engine innodb status;
mysql tables in use 2, locked 2
630 lock struct(s), heap size 79400, 137782 row lock(s), undo log entries 137781
mysql> show engine innodb status;
mysql tables in use 2, locked 2
920 lock struct(s), heap size 112168, 201581 row lock(s), undo log entries 201580
mysql> show engine innodb status;
mysql tables in use 2, locked 2
1240 lock struct(s), heap size 144936, 271567 row lock(s), undo log entries 271566

从上面,我们看到 row lock 和 lock struct 在不停的增长,被锁的行数在不断增加,其实同测试案例1
结论:
通过主键排序或则不加排序字段的导入操作"insert into a select * from b",是会锁b表,但他的锁是逐步地锁定已经扫描过的记录。
默认:
主键升序的select :从第一行开始扫描到最后,即第一行开始锁直到最后。
主键倒序select:从最后一行开始扫描到最前,即最后一行开始锁直到第一行。

3.B 表使用非主键字段排序

终端1:mysql> insert into os_diskio_history_bak select * from os_diskio_history order by YmdHi desc;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 2
2190 lock struct(s), heap size 259624, 486689 row lock(s)
mysql> show engine innodb status;
mysql tables in use 2, locked 2
2191 lock struct(s), heap size 259624, 486689 row lock(s)
mysql> show engine innodb status;
mysql tables in use 2, locked 2
2192 lock struct(s), heap size 259624, 486689 row lock(s), undo log entries 44409

从上面,我们看到 row lock 和 lock struct 值一直没有变,他的锁是一开始就会锁定整张表,不能进行任何操作,直到锁释放了

结论:insert into a select * from b 的导入操作是会锁定原表,但是锁是有2种情况:“逐步锁”,“全锁”

现在我们调整隔离级别为:RC

终端1:
mysql> set tx_isolation='READ-COMMITTED';
mysql> insert into os_diskio_history_bak select * from os_diskio_history order by YmdHi desc;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 1
2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 41394
mysql> show engine innodb status;
mysql tables in use 2, locked 1
2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 95860

从上面,我们看到 row lock 为 0

总结:
1.在RR模式下:在按主键进行排序或不排序时,采用的是逐步锁定行
2.在RR模式下:在按非主键进行排序时,采用的是表锁
3.在RC模式下:无论按主键还是非主键排序,B表都不加锁

×