[MDEV-10548] Some of the debug sync waits do not work with InnoDB 5.7 (branch bb-10.2-jan) Created: 2016-08-12  Updated: 2016-09-14  Resolved: 2016-09-14

Status: Closed
Project: MariaDB Server
Component/s: Tests
Affects Version/s: 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Jan Lindström (Inactive) Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-10024 Fix test failures on InnoDB 5.7 in Ma... Closed

 Description   

In tests:

  • main.lock_sync
  • main.innodb_mysql_sync
  • main.partition_debug_sync
  • innodb.auto_increment_dup

branch bb-10.2-jan commit 3f1b8c9474e351e50a6556675108864d5aac5528

Example:

CURRENT_TEST: main.lock_sync
 
--- /home/jan/mysql/10.2/mysql-test/r/lock_sync.result	2016-06-30 15:27:02.839686282 +0300
 
+++ /home/jan/mysql/10.2/mysql-test/r/lock_sync.reject	2016-08-12 09:19:24.040144695 +0300
 
@@ -748,10 +748,20 @@
 
 # Wait until the above SELECT ... FOR UPDATE is blocked after
 
 # acquiring lock for the the first instance of 't1'.
 
 set debug_sync= 'now WAIT_FOR parked';
 
+Warnings:
 
+Warning	1639	debug sync point wait timed out
 
 # Send LOCK TABLE statement which will try to get TL_WRITE lock on 't1':
 
 lock table v1 write;;
 
 connection default;
 
 # Wait until this LOCK TABLES statement starts waiting for table lock.
 
+Timeout in wait_condition.inc for select count(*)= 1 from information_schema.processlist
 
+where state= 'Waiting for table level lock' and
 
+info='lock table v1 write'
 
+Id	User	Host	db	Command	Time	State	Info	Progress
 
+4	root	localhost	test	Query	0	init	show full processlist	0.000
 
+7	root	localhost	test	Sleep	231		NULL	0.000
 
+8	root	localhost	test	Sleep	31		NULL	0.000
 
+9	root	localhost	test	Sleep	331		NULL	0.000
 
 # Allow SELECT ... FOR UPDATE to resume.
 
 # Since it already has TL_WRITE_ALLOW_WRITE lock on the first instance
 
 # of 't1' it should be able to get lock on the second instance without
 
@@ -852,10 +862,7 @@
 
 # Reaping: OPTIMIZE TABLE t1
 
 Table	Op	Msg_type	Msg_text
 
 test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
 
-test.t1	optimize	error	Lock wait timeout exceeded; try restarting transaction
 
-test.t1	optimize	status	Operation failed
 
-Warnings:
 
-Error	1205	Lock wait timeout exceeded; try restarting transaction
 
+test.t1	optimize	status	OK
 
 SET DEBUG_SYNC= 'now SIGNAL release_thrlock';
 
 disconnect con1;
 
 connection con2;



 Comments   
Comment by Sergei Golubchik [ 2016-09-08 ]

jplindst, I've fixed the first three, the last one is yours, as discussed

Comment by Michael Widenius [ 2016-09-08 ]

The problem is in this new code:
row_ins_sec_index_entry_low()

/* When using the REPLACE statement or ON DUPLICATE clause, a
gap lock is taken on the position of the to-be-inserted record,
to avoid other concurrent transactions from inserting the same
record. */

The related code will cause new inserts to be blocked on a leaf page of an unique index.

In the test we do (note that k is an unique secondary key)
thread_1: INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1'
thread_2: INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1'

What happens is:
thread_1 inserts k=1 and waits for thread2 to complete (part of the DBUG_SYNC test)
thread_2 inserts k=2, but this is blocked in InnoDB by the gap lock caused by k=1

I have verified that MySQL 5.7 also will block for this test

Possible fixes:

  • Don't take the gap lock
  • Run test in repeatable read mode
  • Run test with --innodb-locks-unsafe-for-binlog and fix that we don't take the gap lock if this is set
  • Fix InnoDB to not take gap locks if binary log is not enabled
Comment by Jan Lindström (Inactive) [ 2016-09-14 ]

See detailed analysis of innodb.auto_increment_dup test case in http://bugs.mysql.com/bug.php?id=66301. In my opinion current behavior is correct and previous behavior was incorrect.

Comment by Jan Lindström (Inactive) [ 2016-09-14 ]

Part 2 of this (no public bug):

commit c93b0d9a972cb6f98fd445f2b69d924350f9128a
Author: Annamalai Gurusami <annamalai.gurusami@oracle.com> Mon Nov 11 11:39:32 2013
Committer: Annamalai Gurusami <annamalai.gurusami@oracle.com> Mon Nov 11 11:39:32 2013

Bug #11758237 INSERT ON DUPLICATE KEY UPDATE SOMETIMES WRITES BINLOG
POSITION INCORRECT

Problem:

When concurrent INSERT ... ON DUPLICATE UPDATE statement is executed on a
table, the final outcome is sometimes not serializable.

Solution:

When INSERT ... ON DUPLICATE UPDATE statement is execute, first the record
would be inserted into the clustered index followed by the secondary indexes.
If a duplicate error is encountered, we do not stop processing. We continue to
process all the unique secondary indexes and place the necessary gap locks.
Once a duplicate error has been encountered, the records will not be actually
inserted but only gap locks will be placed.

rb#3196 approved by Marko

Comment by Jan Lindström (Inactive) [ 2016-09-14 ]

Current behavior is correct for repeatable read (and serializable) isolation level. Old behavior is correct for read committed isolation level. Added test cases for both repeatable read and read committed.

Generated at Thu Feb 08 07:43:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.