Pt-Osc Test

Pt-Osc Test

1. The original table must have the primary key or unique key.

The new table `darren`.`_t_user_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.Please check you have at least one UNIQUE and NOT NULLABLE index.

2. For tables with foreign key constraints, add the -- alter-foreign-keys-method = rebuild_constraints parameter.

3. triggers cannot exist in the original table by default. But the -- preserve-triggers variable is required. It is not recommended to do so, which may cause risks.

The table `test`.`foo` has triggers but --preserve-triggers was not specified.

4. During the execution of Pt-Osc, if there is an update operation on the primary key, duplicate data will appear, which has been fixed in version 3.02.

Before 3.02: the SQL statement corresponding to the update trigger: replace into $ new_tbl-> {name} ($ qcols) values ($ new_vals); after 3.02: delete ignore from $ new_tbl-> {name} Where! ($ Upd_index_cols) and $ del_index_cols; replace into $ new_tbl-> {name} ($ qcols) values ($ new_vals );

5. innodb_autoinc_lock_mode = 1 Test

When innodb_autoinc_lock_mode = 1: for bulk inserts, use the auto-Inc locking method of the traditional table lock. ------------------------ Latest detected deadlock---2018-10-24 16:44:13 0x7f93585e3700 *** (1) transaction: Transaction 275263782, active 0 sec setting auto-Inc lockmysql tables in use 2, locked 2 Lock wait 4 lock struct (s), heap size 1136, 1 row lock (s ), UNDO log entries 2 MySQL thread ID 190, OS thread handle 140269612435200, query ID 20289531 10.126.126.164 Darren updatereplace into 'sbtest '. '_ sbtest1_new' ('id', 'c4 ', 'k', 'C', 'pad', 'c3', 'c5 ', 'c6', 'c7 ', 'c14', 'c9 ', 'c10') values (New. 'id', new. 'c4 ', new. 'k', new. 'C', new. 'pad ', new. 'c3', new. 'c5 ', new. 'c6 ', new. 'c7', new. 'c14', new. 'c9 ', new. 'c10') *** (1) waiting for this lock to be granted: Table lock table 'sbtest '. '_ sbtestbench new' trx id 275263782 lock mode auto-Inc waiting *** (2) Transaction: Transaction 275263754, active 0 sec fetching rows, thread declared inside InnoDB 4212 MySQL tables in use 2, locked 2258 lock struct (s), heap size 41168,179 13 row lock (s), UNDO log entries 17886 MySQL thread ID 204, OS thread handle 140270819489536, query ID 20289201 10.126.126.164 Darren sending datainsert low_priority ignore into 'sbtest '. '_ sbtest1_new' ('id', 'c4 ', 'k', 'C', 'pad', 'c3', 'c5 ', 'c6', 'c7 ', 'c14', 'c9 ', 'c10') Select 'id', 'c26', 'k', 'C', 'pad', 'c3', 'c26 ', 'c6', 'c7', 'c14', 'c9 ', 'c10' from 'sbtest '. 'sbtest1' Force Index ('Primary ') Where ('id'> = '000000') and ('id' <= '000000 ')) lock in share mode/* PT-Online-schema-change 69839 copy nibble */*** (2) holds the lock (s): Table lock table 'sbtest '. '_ sbtest1_new' trx id 275263754 lock mode auto-Inc *** (2) waiting for this lock to be granted: record locks Space ID 15248 Page No 25707 N bits 152 index primary of table 'sbtest '. 'sbtest1' trx id 275263754 lock mode s locks rec but not gap waitingrecord lock, heap No 40 physical record: n_fields 15; compact format; Info Bits 0 0: Len 8; hex limit 0016e050; asc p; 1: Len 6; hex 000010683126; ASC H1 &; 2: Len 7; hex 01000007722b37; asc r + 7; 3: Len 1; hex 30; ASC 0; 4: Len 4; hex 00385728; ASC 8 W (; 5: Len 30; hex large; ASC 41313398054-02824088067-808523; (total 119 bytes ); 6: Len 30; hex bytes; ASC 49709721928-13785867532-986130; (total 59 bytes); 7: Len 4; hex 80000000; ASC; 8: Len 1; hex 30; ASC 0; 9: Len 1; hex 30; ASC 0; 10: Len 1; hex 30; ASC 0; 11: Len 1; hex 30; ASC 0 ;; 12: Len 4; hex 80000000; ASC; 13: Len 4; hex 80000000; ASC; 14: Len 4; hex 80000000; ASC ;; * ** we roll back transaction (1)

[Analysis]

Two messages can be obtained based on the deadlock information: (1) Transaction 1 is waiting for the auto-Inc table lock of the "_ sbtest1_new" table; (2) transaction 2 holds the auto-Inc table lock of "_ sbtest1_new" and waits for the record lock of "sbtest1. The replace into Statement of transaction 1 is obviously generated by running the trigger created by Pt-Osc. When the original table generates record updates, the trigger synchronizes the records to the new table in the form of replace. Transaction 1: (1) conditional update, hold exclusive record locks for sbtest1; (2) the trigger is triggered after the update, and then insert the _ sbtest1_new table in the form of replace, you need to hold an implicit auto-increment lock for _ sbtest1_new. Transaction 2: (1) insert into select from, first add the table-Level Auto-increment lock to _ sbtest1_new; (2) after adding the table lock to the new table, apply for the record lock of the original table sbtest1 Based on the range of the primary key ID in the condition. From above, because transaction 1 first updates the original table sbtest1 and adds an exclusive lock to the updated record, when the trigger has not been triggered, transaction 2 starts to execute, at this time, transaction 2 now adds a table lock to the new table. When it applies to add a record-level sharing lock to the original table, it finds that some records are added with an exclusive lock, so it needs to wait. At this time, the transaction 1 trigger is triggered, and an auto-increment lock needs to be obtained for the new table, resulting in a loop and deadlock.

Pt-Osc Test