preface

Insert into SELECT please use with caution. Today XXX received A request to migrate the data from table A to table B as A backup. I wanted to find out through the program first query and then batch insert. However, XXX felt that this was a bit slow and needed to consume a lot of network I/O, so he decided to adopt other methods to implement it. By cruising Baidu, he discovered that he could use insert into SELECT to avoid network I/O and use SQL to rely on database I/O, which is not too good. And then he was fired.

How the accident happened.

Due to the large amount of order_today data in the database, it seemed to be 700W at that time and was increasing by 30W every day. So the boss ordered XXX to migrate some of the data in order_today to order_RECORD and delete the data in order_today. This reduces the amount of data in the ORDER_TODAY table.

Considering that the database I/O will be occupied, in order not to affect the business, the plan is to start the migration after 9:00, but XXX tried to migrate a small part of the data (1000 pieces) at 8:00, thought there was no problem, and began to consider mass migration.

  • In the process of migration, the emergency group responds to the payment failure of a small number of users, and then responds to the payment failure of a large number of users, as well as the initial order failure. Meanwhile, Tencent also starts to alarm.
  • Then XXX panicked and immediately stopped the migration.

We thought that by stopping the migration we would be able to recover, but we didn’t. You can imagine what happens next.

The accident reduction

A compact version of the database was set up locally and 100W of data was generated. Simulate what happens on the line.

Create table structure

The order sheet

CREATE TABLE `order_today` (

'id' varchar(32) NOT NULL COMMENT '主键',

'merchant_id' varchar(32) CHARACTER SET utf8 COLLATE UTf8_general_ci NOT NULL COMMENT 'merchant ',

'amount' decimal(15,2) NOT NULL COMMENT 'order amount ',

'pay_success_time' datetime NOT NULL COMMENT 'pay_success_time ',

'order_status' varchar(10) CHARACTER SET utf8 COLLATE UTf8_general_ci NOT NULL COMMENT' order_status ',

'remark' varchar(100) CHARACTER SET utf8 COLLATE UTf8_general_ci DEFAULT NULL COMMENT 'remarks ',

'create_time' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create_time ',

'update_time' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update_time ',

  PRIMARY KEY (`id`) USING BTREE,

KEY 'idx_merchant_id' (' merchant_id ') USING BTREE COMMENT 'merchant id'

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Copy the code

Order sheet

CREATE TABLE order_record like order_today;

Copy the code

Order table data today

To simulate the migration

Migrate all data before 8th to order_RECORD table.

INSERT INTO order_record SELECT

    * 

FROM

    order_today 

WHERE

    pay_success_time < '2020-03-08 00:00:00';

Copy the code

Run the migrated SQL in Navicat, at the same time open another window to insert data, simulate the order.

It can be seen from the above that the insertion was normal at the beginning, but then it suddenly stuck, and it took 23s to succeed, and then it could continue to insert. At this point, the migration has been successful, so you can insert normally.

Cause of occurrence

Insert into ORDER_RECORD SELECT * from order_TODAY insert into ORDER_RECORD select * from order_TODAY insert into ORDER_RECORD select * from order_TODAY insert into ORDER_RECORD select * from order_TODAY insert into ORDER_RECORD select * from order_TODAY

Analyze the execution process.

Order_today is a full table scan, which means mysql scans the records in order_today from top to bottom and locks them when executing an INSERT into SELECT FROM statement. In this way, it is the same as locking the table directly.

This can explain why only a small number of users failed to pay at the beginning, followed by a large number of users failed to pay, failed to initialize the order, etc. Because only a small part of data is locked at the beginning, the data that is not locked can still be changed to the normal state. As more and more data is locked, there are a lot of payment failures. Finally, they were all locked, so the order could not be inserted and the order failed to be initialized.

The solution

To prevent an ORDER_today full table scan, add an idx_PAY_SUC_TIME index to the pay_SUCCESS_TIME field. Only eligible records are locked.

The final SQL

INSERT INTO order_record SELECT

    * 

FROM

    order_today FORCE INDEX (idx_pay_suc_time)

WHERE

    pay_success_time <= '2020-03-08 00:00:00';

Copy the code

Implementation process

conclusion

Insert into tablA select * from tableB; insert into tablA select * from tableB; insert into tablA select * from tableB;

Refer to the article

  • Insert into… Select Deadlock analysis caused by the SELECT table

At the end

If you feel helpful to you, you can comment more, more like oh, you can also go to my home page to have a look, maybe there is an article you like, you can also click a concern oh, thank you.