![]() ![]() Therefore the fix Bug #25966845 is actually about bringing back serializability guarantees by adding more locks.Īnd more locks can (as expected) cause more deadlocks in some scenarios. Which means among other things that replication could fail to produce exact copy of DB. It could happen that knowing what queries transactions performed and in which order they committed was no longer enough to determine the final state of the DB, However, when investigating what exactly went wrong, we've discovered that implementation does not provide enough locks which was much more serious problem! In particular the Bug #25966845 INSERT ON DUPLICATE KEY GENERATE A DEADLOCK has a title complaining about a deadlock, but deadlocks are a fact of life and not a bug per se. This is how it turned out that deadlocks were just (perhaps anoying) symptoms of much bigger problem. I want to stress that deadlock is not a bug per se.īut new strange behaviour might be indicative of some bug underneath, so we often investigate. Many people view deadlocks as something very bad so keep reporting them. The titles were set by initial reporters who noticed some particular behaviour which they considered buggy. Jakub Lopuszanski Impressive investigation - thanks! :)įirst of all, when talking about these particular bugs, we have to completely ignore semantics of their titles. Seem like the "exclusive next-key lock" is the extra locking that gets removed after the change in 5.7.26? An exclusive next-key lock is taken for a duplicate unique key value." An exclusive index-record lock is taken for a duplicate primary key value. ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. My concern is will the doc still up-to-date with this change in 5.7.26. In 5.7.26, the lock periods get shorter => 2 batch transactions are intervened and cause deadlock Why 5.7.4 implementation has more aggressive locking but the deadlock happens more frequently when the change get reverted?īefore 5.7.4, the lock is extended for a longer period => the two batch transactions are fully isolated. Once a duplicate error has been encountered, the records will not be actually inserted but only gap locks will be placed."ĥ.7.4 (2014) applied a longer lock to help resolve the replication issue.ĥ.7.26 (2019) found a better approach for the replication, so the extra locks are removed. We continue to process all the unique secondary indexes and place the necessary gap locks. If a duplicate error is encountered, we do not stop processing. ON DUPLICATE UPDATE statement is executed, first, the record would be inserted into the clustered index followed by the secondary indexes. "This bug fix was just extending the gap locks for duplicates as a solution to this problem, which was not a foolproof solution. This change is the commit introduced in 5.7.26 Msb_5_7_26/use test -e "REPLACE INTO test SET id=NULL, a='foobar', b = 1, d = 'boobar' " Įxplain why 5.7.26+ detects more far more deadlocks and if that's a regression bug, fix it. Msb_5_7_26/use test -e "REPLACE INTO test SET id=NULL, a='foobar', b = 2, d = 'boobar' " $ for i in do msb_5_7_26/use test -e "REPLACE INTO test SET id=NULL, a='foobar', b = 1, d = 'boobar' " done &ĮRROR 1213 (40001) at line 1: Deadlock found when trying to get lock try restarting transaction Then run concurrent REPLACE loops against both. 26.ĬREATE TABLE test (id int unsigned NOT NULL AUTO_INCREMENT, a varchar(20), b int, c timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, d varchar(10), PRIMARY KEY (id), UNIQUE KEY uk1 (a,b,d)) ENGINE=InnoDB Set up simple sandbox instances with all defaults (I used dbdeployer), using MySQL Community 5.7.25 and. During partial rollback of a tuple, another session could update it. ON DUPLICATE KEY UPDATE operation generated a deadlock. ![]() "InnoDB: Two sessions concurrently executing an INSERT. I wonder if that change in 5.7.26 could be possibly related: More deadlocks are observed after minor version upgrade from 5.7.25 to 5.7.26 or higher.įor a simple test case with REPLACE statements, I am getting around 20x more deadlock messages after upgrade. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |