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
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-27025 is MDEV-27992. Let's take a look MDEV-27992 once more. Suppose we don't have MDEV-27025 fix. 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 why MDEV-27025 is 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-27992 case 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.
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.
There is some interesting commit in MySQL-8: "Bug #11745929 CHANGE LOCK PRIORITY SO THAT THE TRANSACTION HOLDING S-LOCK GETS X-LOCK". It should be analyzed, it might fix the issue.