Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
-
None
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;
|
Attachments
Issue Links
- is part of
-
-
- Closed
-
Activity
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
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.
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
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.
jplindst, I've fixed the first three, the last one is yours, as discussed