Bug #85447 | Slave SQL thread locking issue on a certain XA workload on master | ||
---|---|---|---|
Submitted: | 14 Mar 2017 13:09 | Modified: | 25 Oct 2018 9:37 |
Reporter: | Laurynas Biveinis (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
Version: | 5.7, 5.7.17 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[14 Mar 2017 13:09]
Laurynas Biveinis
[14 Mar 2017 13:43]
MySQL Verification Team
Hello Laurynas, Thank you for the report and test case. Thanks, Umesh
[30 Aug 2017 23:16]
Kenny Gryp
This is what you would get in INNODB_LOCKS: perconaserver mysql> select * from information_schema.INNODB_LOCKS; +------------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+------------------------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +------------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+------------------------+ | 2238925:347:13:1 | 2238925 | X | RECORD | `xa`.`t1` | PRIMARY | 347 | 13 | 1 | supremum pseudo-record | | 2238923:347:13:1 | 2238923 | S | RECORD | `xa`.`t1` | PRIMARY | 347 | 13 | 1 | supremum pseudo-record | +------------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+------------------------+ SHOW ENGINE INNODB TRANSACTIONS output: ---TRANSACTION 2238938, ACTIVE 2 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 18, OS thread handle 140294237603584, query id 96216 System lock ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 347 page no 13 n bits 72 index PRIMARY of table `xa`.`t1` trx id 2238938 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ------------------ TABLE LOCK table `xa`.`t1` trx id 2238938 lock mode IX RECORD LOCKS space id 347 page no 13 n bits 72 index PRIMARY of table `xa`.`t1` trx id 2238938 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ---TRANSACTION 2238923, ACTIVE (PREPARED) 1085 sec recovered trx 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2 TABLE LOCK table `xa`.`t1` trx id 2238923 lock mode IX TABLE LOCK table `xa`.`t2` trx id 2238923 lock mode IX RECORD LOCKS space id 347 page no 13 n bits 72 index PRIMARY of table `xa`.`t1` trx id 2238923 lock mode S Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; RECORD LOCKS space id 347 page no 14 n bits 72 index PRIMARY of table `xa`.`t1` trx id 2238923 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 0000002229cb; asc ") ;; 2: len 7; hex ee000001d80110; asc ;; 3: len 30; hex 616161616161616161616161616161616161616161616161616161616161; asc aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; (total 8000 bytes);
[30 Aug 2017 23:17]
Kenny Gryp
Given the fact that this is also a 'supremum pseudo-record' issue. This bug might be very likely related: https://bugs.mysql.com/bug.php?id=86486
[30 Aug 2017 23:19]
Kenny Gryp
NOTE FOR PEOPLE HAVING THIS ISSUE: You can 'workaround' this issue by restarting the database, which causes the locks by the XA transaction that is already prepared to be gone and replication will just continue. (no need to restore slaves from backups, skip binlog events...)
[27 Sep 2017 23:21]
Vlad Lesin
The issue has been already fixed in 5.7.18 with the following commit: commit c90aba97ee76f65770f6b4641edef1c32db58279 Author: Debarun Banerjee <debarun.banerjee@oracle.com> Date: Sun Nov 13 10:31:35 2016 +0530 BUG#25082593 FOREIGN KEY VALIDATION DOESN'T NEED TO ACQUIRE GAP LOCK IN READ COMMITTED
[18 Oct 2017 11:27]
Kenny Gryp
The test case from Laurynas does not fail in 5.7.19. IMO this bug can be closed as fixed.
[31 Oct 2017 0:48]
Jesper wisborg Krogh
Posted by developer: The test case does not reproduce for me in 5.7.18. I agree, this is most likely a duplicate of Bug 25082593 which was fixed in 5.7.18 with the following change log entry: A gap lock was taken unnecessarily during foreign key validation while using the READ COMMITTED isolation level.
[25 Oct 2018 9:37]
Erlend Dahl
Fixed in 5.7.18 under the heading of Bug#25082593 FOREIGN KEY VALIDATION DOESN'T NEED TO ACQUIRE GAP LOCK IN READ COMMITTED