Shulou(Shulou.com)06/01 Report--

Http://www.ttlsa.com/mysql/mysql5-6-waiting-for-commit-lock/

Http://bugs.mysql.com/bug.php?id=70307

Be careful with a bug, http://bugs.mysql.com/bug.php?id=70307 using MySQL5.6 and Xtrabackup, this bug has been fixed in 5.6.23.

Performing flushs tables with read lock and show slave status during Xtrabackup backup may form a deadlock with SQL Thread, causing SQL Thread to be stuck all the time, and STOP is useless. Kill will lose data in our test, only Restart Server. The reason is that after the DML operation of SQL Thread is completed, the rli- > data_lock lock is held, while the commit waits for MDL_COMMIT, while the show slave status executed after flush tables with read lock waits for rli- > data_lock; repair method is rli- > data_lock lock period is held only during DML operation. Reproduction steps: first, create tables CREATE TABLE test (id int (10) NOT NULL AUTO_INCREMENT, age int (11) DEFAULT'0, PRIMARY KEY (id), KEY idx_age (age)

) ENGINE=InnoDB

Second, the execution of update test set value=sleep (20) + 53 where id=1; on master (adding sleep (20) is for the convenience of simulation, so the binlog format,row format of statement is not good). Third, when synchronizing to slave and is being executed, the execution of flush tables with read lock;show slave status; will block. Official detailed explanation: Bug#19843808: DEADLOCK ON FLUSH TABLES WITH READ LOCK + SHOW SLAVE STATUS Problem: If a client thread on an slave does FLUSH TABLES WITH READ LOCK; then master does some updates, SHOW SLAVE STATUS in the same client will be blocked. Analysis: Execute FLUSH TABLES WITH READ LOCK on slave and at the same time execute a DML on the master. Then the DML should be made to stop at a state "Waiting for commit lock". This state means that sql thread is holding rli- > data_lock and waiting for MDL_COMMIT lock. Now in the same client session where FLUSH TABLES WITH READ LOCK was executed issue SHOW SLAVE STATUS command. This command will be blocked waiting for rli- > data_lock causing a dead lock. Once this happens it will not be possible to release the global read lock as "UNLOCK TABLES" command has to be issued in the same client where global read lock was acquired. This causes the dead lock. Fix: Existing code holds the rli- > data_lock for the whole duration of commit operation. Instead of holding the lock for entire commit duration the code has been restructured in such a way that the lock is held only during the period when rli object is being updated.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.