Pt-online-schema-change modifying field properties resulting in deadlock

1. The deadlock information is as follows:

2015-06-15 22:23:18 7f0266772700

(1) TRANSACTION:

TRANSACTION 1834696957, ACTIVE 0 sec Setting Auto-inc Lock

MySQL tables in use 2, locked 2

Lock WAIT 4 lock struct (s), heap size 1184, 1 row lock (s), undo log Entries 1

MySQL thread ID 1008989, OS thread handle 0x7f0265de0700, query ID 1925131733 127.0.0.1 Huanghualiang sending data

INSERT low_priority IGNORE into ' newmall '. ' _cash_xxx_new ' (' id ', ' type ', ' name ', ' discount ', ' Amount ', ' begintime ', ' Endt ') IME ', ' userid ', ' realname ', ' Code ', ' Releaseuserid ', ' releaseorderid ', ' OrderID ', ' status ', ' Packetid ', ' deleted ', ' Createtime ', ' updatetime ', ' OpenID ', ' headimgurl ') SELECT ' id ', ' type ', ' name ', ' discount ', ' Amount ', ' begintime ', ' endti ' Me ', ' userid ', ' realname ', ' Code ', ' Releaseuserid ', ' releaseorderid ', ' OrderID ', ' status ', ' Packetid ', ' deleted ', ' Createtime ', ' updatetime ', ' OpenID ', ' headimgurl ' from ' newmall '. ' Cash_xxx ' Force INDEX (' PRIMARY ') WHERE (' id ' >= ' 16 8256 ') and ((' id ' <= ' 174595 ')) LOCK in SHARE MODE/*pt-online-schema-change 17123 copy nibble*/

(1) Waiting for this LOCK to be granted:

Table lock table ' Newmall '. ' _cash_xxx_new ' Trx ID 1834696957 LOCK mode auto-inc waiting

(2) TRANSACTION:

TRANSACTION 1834696069, ACTIVE 1 sec fetching rows, thread declared inside InnoDB 2131

MySQL tables in use 2, locked 2

1580 lock struct (s), Heap size 161320, 3 row lock (s), Undo log Entries 2

MySQL thread ID 1007462, OS thread handle 0x7f0266772700, query ID 1925130797 10.162.216.179 xm_mall closing tables

Update ' cash_xxx ' Set status = 2 where orderId = 3271393

(2) holds the LOCK (S):

Table lock table ' Newmall '. ' _cash_xxx_new ' Trx ID 1834696069 LOCK Mode auto-inc

(2) Waiting for this LOCK to be granted:

RECORD LOCKS Space ID 11019 page no 3462 n bits 184 index ' PRIMARY ' of table ' Newmall '. ' Cash_xxx ' Trx ID 1834696069 lock_m Ode X Locks Rec but not gap waiting

WE Roll Back TRANSACTION (1)

Transaction 1:insert low_priority IGNORE into ' newmall '. ' _cash_xxx_new ' .... Select ... From ' Newmall ', ' cash_xxx ' Force INDEX (' PRIMARY ') WHERE (' id ' >= ' 168256 ')) and ((' id ' <= ' 174595 ')) LOCK in SHARE M ODE;

Wait for: _cash_xxx_new self-increment primary key table-level lock

s lock for hold:cash_xxx table

Transaction 2:update ' cash_xxx ' Set status = 2 where orderId = 3271393

Wait for:cash_xxx x lock on table

Hold: _cash_xxx_new self-locking (this is because the Pt-online-schema-change trigger turns update to replace into a new table)

Transaction 1 was finally rolled back.

This article is from the My DBA life blog, so be sure to keep this source http://huanghualiang.blog.51cto.com/6782683/1662474

Pt-online-schema-change modifying field properties resulting in deadlock