pt-osc原表往临时表复制过程中锁问题验证

背景：在技术群里跟朋友们聊天的过程中，出现一个疑问：pt-osc修改表结果的过程中，真的不会阻塞写入操作吗？

验证过程

原表结构

"root@localhost:mysql.sock  [lijk]>show create table lijktest;
+----------+------------------+
| Table    | Create Table                                                                                                                                                                                          |
+----------+------------------+
| lijktest | CREATE TABLE `lijktest` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4652975 DEFAULT CHARSET=utf8 |
+----------+------------------+
1 row in set (0.04 sec)

修改表结构命令

[root@pxc01 bin]# ./pt-online-schema-change -u root -h 127.0.0.1  -p 123456 --alter "ADD COLUMN c1 INT" D=lijk,t=lijktest --execute
No slaves found.  See --recursion-method if host pxc01 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `lijk`.`lijktest`...
Creating new table...
Created new table lijk._lijktest_new OK.
Altering new table...
Altered `lijk`.`_lijktest_new` OK.
2017-03-31T04:34:47 Creating triggers...
2017-03-31T04:34:47 Created triggers OK.
2017-03-31T04:34:47 Copying approximately 3826207 rows...
Copying `lijk`.`lijktest`:  32% 01:00 remain
Copying `lijk`.`lijktest`:  69% 00:26 remain
2017-03-31T04:36:21 Copied rows OK.
2017-03-31T04:36:21 Analyzing new table...
2017-03-31T04:36:21 Swapping tables...
2017-03-31T04:36:22 Swapped original and new tables OK.
2017-03-31T04:36:22 Dropping old table...
2017-03-31T04:36:22 Dropped old table `lijk`.`_lijktest_old` OK.
2017-03-31T04:36:22 Dropping triggers...
2017-03-31T04:36:22 Dropped triggers OK.
Successfully altered `lijk`.`lijktest`.

修改表结构期间产生的锁信息

"root@localhost:mysql.sock  [(none)]>pager grep "lock(s)"
PAGER set to 'grep "lock(s)"'
"root@localhost:mysql.sock  [(none)]>
"root@localhost:mysql.sock  [(none)]>
"root@localhost:mysql.sock  [(none)]>show engine innodb status;
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>
"root@localhost:mysql.sock  [(none)]>
"root@localhost:mysql.sock  [(none)]>
"root@localhost:mysql.sock  [(none)]>show engine innodb status;
19 lock struct(s), heap size 2936, 6527 row lock(s), undo log entries 6526
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
34 lock struct(s), heap size 6544, 12693 row lock(s), undo log entries 12693
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
46 lock struct(s), heap size 13864, 17792 row lock(s), undo log entries 17791
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
41 lock struct(s), heap size 6544, 15331 row lock(s), undo log entries 15331
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
6 lock struct(s), heap size 1184, 971 row lock(s), undo log entries 970
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
43 lock struct(s), heap size 6544, 16498 row lock(s), undo log entries 16498
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
43 lock struct(s), heap size 6544, 16291 row lock(s), undo log entries 16291
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
12 lock struct(s), heap size 2936, 3828 row lock(s), undo log entries 3827
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
14 lock struct(s), heap size 2936, 4419 row lock(s), undo log entries 4419
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
61 lock struct(s), heap size 13864, 23729 row lock(s), undo log entries 23729
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
33 lock struct(s), heap size 6544, 12088 row lock(s), undo log entries 12088
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
44 lock struct(s), heap size 6544, 16779 row lock(s), undo log entries 16778
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
63 lock struct(s), heap size 13864, 24417 row lock(s), undo log entries 24417
1 row in set (0.01 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
59 lock struct(s), heap size 13864, 22852 row lock(s), undo log entries 22852
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
63 lock struct(s), heap size 13864, 24543 row lock(s), undo log entries 24543
1 row in set (0.01 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
13 lock struct(s), heap size 2936, 4211 row lock(s), undo log entries 4211
1 row in set (0.01 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
62 lock struct(s), heap size 13864, 24068 row lock(s), undo log entries 24068
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
45 lock struct(s), heap size 13864, 17511 row lock(s), undo log entries 17510
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
10 lock struct(s), heap size 2936, 2821 row lock(s), undo log entries 2820
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
61 lock struct(s), heap size 13864, 23845 row lock(s), undo log entries 23845
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
56 lock struct(s), heap size 13864, 21543 row lock(s), undo log entries 21543
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
13 lock struct(s), heap size 2936, 3910 row lock(s), undo log entries 3910
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
40 lock struct(s), heap size 6544, 15370 row lock(s), undo log entries 15370
1 row in set (0.01 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
45 lock struct(s), heap size 13864, 17075 row lock(s), undo log entries 17075
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
29 lock struct(s), heap size 6544, 10682 row lock(s), undo log entries 10681
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
16 lock struct(s), heap size 2936, 5420 row lock(s), undo log entries 5420
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
48 lock struct(s), heap size 13864, 18066 row lock(s), undo log entries 18065
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
18 lock struct(s), heap size 2936, 6241 row lock(s), undo log entries 6240
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>show engine innodb status;
1 row in set (0.00 sec)

"root@localhost:mysql.sock  [(none)]>

结论：由此可见，pt-osc的过程中，不是没有任何锁的。只不过加的是IS锁，而且是在复制的过程中，对正在拷贝的chunk加锁，拷贝完成之后即释放，所以加的锁时间比较短，对数据库当前写入影响很小。

命令参考：

 pt-online-schema-change  --host=127.0.0.1 --port=3306 --user=root --password=xxxxx  --nodrop-old-table  --charset=utf8   --lock-wait-timeout=51 --max-load Threads_running=100 --alter="add cityid bigint(20) DEFAULT NULL COMMENT '城市id' " D=dbname,t=tbname --execute