Details
Description
We have two transactions and one record. The first transaction holds ORDINARY S-lock on the record, the second transaction created waiting ORDINARY X-lock and waits for the first transaction. Then the first transaction requests insert-intention lock on the record. And this lock conflicts with the waiting ORDINARY X-lock of the second transaction. What causes deadlock. Why it should conflict? The first transaction already holds lock on the record. And the second's transaction lock contains "waiting" flag.
Let's take a look 10.6 code:
dberr_t
|
lock_rec_insert_check_and_lock(...)
|
{
|
...
|
const unsigned type_mode= LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION; |
|
if (lock_t *c_lock= lock_rec_other_has_conflicting(type_mode, |
g.cell(), id,
|
heap_no, trx))
|
{
|
trx->mutex_lock();
|
err= lock_rec_enqueue_waiting(c_lock, type_mode, id, block->frame,
|
heap_no, index, thr, nullptr);
|
trx->mutex_unlock();
|
}
|
...
|
}
|
Neither lock_rec_insert_check_and_lock() nor lock_rec_other_has_conflicting() doesn't check conflicting lock is in waiting state and it already waits for the requesting insert-intention lock transaction.
The test is attached: ii-conflicts-waiting.test
Attachments
Issue Links
- blocks
-
MDEV-10962 Deadlock with 3 concurrent DELETEs by unique key
-
- Closed
-
- causes
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
MDEV-24738 Improve the InnoDB deadlock checker
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
SAMU-292 Loading...
- mentioned in
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Activity
What was done as part of fixing this issue ? It is not clear if anything from vlad's comment above has been addressed.
Is the issue in https://github.com/mysql/mysql-server/commit/7037a0bdc83196755a3bf3e935cfb3c0127715d5
addressed now ?
The above commit include a test case. Does that test case now work in MariaDB?
Note that in MariaDB 10.6 we have now optimized bit operations in my_bitmap.cc
There is no looping over bits anymore and all operations are done on 64 bits at a time.
In order to run the test ii-conflicts-waiting.test on MariaDB Server 10.6 or later, the following adjustment is needed.
@@ -18,7 +18,7 @@
|
|
--connect(con_del,localhost,root,,)
|
SET DEBUG_SYNC = 'now WAIT_FOR ins_set_locks';
|
-SET DEBUG_SYNC = 'lock_wait_suspend_thread_enter SIGNAL del_locked';
|
+SET DEBUG_SYNC = 'lock_wait_start SIGNAL del_locked';
|
###############################################################################
|
# This DELETE creates waiting ORDINARY X-lock for heap_no 2 as the record is
|
# delete-marked, this lock conflicts with ORDINARY S-lock set by the the last |
vlad.lesin, it seems that MDEV-34877 is not fixing the scenario of this test:
|
11.4 30140c066d50f7e4ac4f490a9e081d9d605aea07 |
mysqltest: At line 43: query 'reap' failed: ER_LOCK_DEADLOCK (1213): Deadlock found when trying to get lock; try restarting transaction
|
I tested this with both values of innodb_snapshot_isolation (MDEV-35124) and got the same result. Based on my reading of the analysis in MDEV-27992, this setting should make no difference in that scenario, but I did not check that by re-applying and retesting the original fix of MDEV-27025.
The regression MDEV-27992, which forced us to revert the original fix, involves a scenario where some PRIMARY KEY columns are being updated, or more generally, if the same transaction is first deleting and then inserting rows in the same table. If the problems are only limited to only such scenarios, I wonder if we could treat those as a special case and enable the optimization in other cases. At the core of the MDEV-27992 fix is the added parameter bool insert_before_waiting, which is being set in the calls of lock_rec_add_to_queue() in lock_rec_convert_impl_to_expl_for_trx() and when lock_rec_other_has_conflicting() sets was_ignored in lock_rec_lock().
I have analysed "Bug #11745929 CHANGE LOCK PRIORITY SO THAT THE TRANSACTION HOLDING S-LOCK GETS X-LOCK" commit from Oracle.
The general reason why we reverted
MDEV-27025isMDEV-27992. Let's take a lookMDEV-27992once more. Suppose we don't haveMDEV-27025fix. And we have trx 1 which holds ordinary X-lock on some record. And trx 2 executes "DELETE FROM t" or "SELECT * FOR UPDATE" in RR. It creates waiting ordinary X-lock on the same record. And then trx 1 wants to insert some record just before the locked record. It requests insert-intention lock. And the only thing which does not let trx 1 to insert new record is that trx 2 has conflicting waiting X-lock. That is whyMDEV-27025is not a bug, it's a feature. If trx 1 ii-lock did not conflict with waiting trx 2 ordinary X-lock, there would be phantom records in RR. And there is nothing to fix.But, what if trx 1 holds S-lock, and tries to acquire X-lock on the same record after trx 2 created waiting X-lock? Why trx 1 should wait for trx 2? This is exactly the case of the above MySQL fix. More commonly it can be formulated as "insert-intention locks must not overtake a waiting ordinary or gap locks".
I think it could be useful for us to port that commit to fix the above issue. Besides, it contains some useful optimization:
`lock_rec_find_set_bit` which searches for the first bit set in a bitmap used bit-by-bit loop. Now it uses 13x times faster implementation which tries to skip 64,then 32,16, or 8 bits at a time. This is important for WAITING locks which have just a single bit set, in a bitmap with number of bits equal to the number of records on a page (which can be ~500).
If we port it some time, we should also port "Bug #34123159 Assertion failure: lock0lock.cc:5161:lock_rec_has_expl(LOCK_X | LOCK_REC_NOT_GAP".
One more note. In
MDEV-27992case DELETE converts implicit lock to explicit one despite conflicting transaction already holds ordinary X-lock on the record. That is because lock_rec_convert_impl_to_expl_for_trx() checks only LOCK_X | LOCK_REC_NOT_GAP when it looks for existing explicit locks. We could include LOCK_ORDINARY in the search also.