Marko Mäkelä added a comment - 2018-07-03 12:20 For the record, I tried the following patch on top of MDEV-16675 , but we did not avoid acquiring all record locks when a covering exclusive table lock should exist: diff --git a/mysql-test/suite/innodb/t/monitor.test b/mysql-test/suite/innodb/t/monitor.test index 3535c9c85ad..475eb202be6 100644 --- a/mysql-test/suite/innodb/t/monitor.test +++ b/mysql-test/suite/innodb/t/monitor.test @@ -439,6 +439,20 @@ SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created'); SELECT @end - @start; +SET @start = @end; + +BEGIN; +LOCK TABLE t1 WRITE; +SELECT * FROM t1 FOR UPDATE; +SELECT * FROM t1 WHERE a>=10000 FOR UPDATE; +DELETE FROM t1; +COMMIT; +UNLOCK TABLES; + +SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME += 'lock_rec_lock_created'); +SELECT @end - @start; + DROP TABLE t1; --disable_warnings diff --git a/storage/innobase/lock/lock0lock.cc b/storage/innobase/lock/lock0lock.cc index cd65a9ac8e0..bc74261257d 100644 --- a/storage/innobase/lock/lock0lock.cc +++ b/storage/innobase/lock/lock0lock.cc @@ -5586,6 +5586,10 @@ lock_rec_convert_impl_to_expl( ut_ad(page_rec_is_leaf(rec)); ut_ad(!rec_is_default_row(rec, index)); + if (lock_table_has(caller_trx, index->table, LOCK_X)) { + return true; + } + if (dict_index_is_clust(index)) { trx_id_t trx_id; The above patch was just a quick attempt at avoiding X-lock creation in a special case. In addition to preventing X-locks from being created on records when a table X-lock exists, we should avoid creating record S-locks when either X-lock or S-lock is held on the table.

Marko Mäkelä added a comment - 2019-04-09 07:14 We should also consider replacing InnoDB table locks with MDL. Non-locking reads are already solely relying on MDL.

Marko Mäkelä added a comment - 2020-02-28 08:13 The patch looks OK to me. I only made a minor comment about formatting the code. Relaxing the debug check lock_trx_has_expl_x_lock() looks OK too. I was wondering whether we should change lock_rec_has_expl() as well. It is being consulted in lock_rec_other_trx_holds_expl_callback() , that is, lock_rec_other_trx_holds_expl() . That debug check is only invoked in lock_rec_convert_impl_to_expl() (when a conflicting transaction is trying to convert an implicit lock to an explicit one, on behalf of the lock-holding transaction). It seems that it is impossible to reach a situation where that check would fail, even without changing it. We can only avoid the explicit X lock creation if the implicit lock holder holds an X lock on the table. And in that case, the conflicting transaction (which would seek to convert the implicit record lock into an explicit one, so that it could wait for it to be released) would already conflict on the table lock. (Record S lock can only be requested if holding at least table IS lock, and record X lock only if holding at least table IX lock. Both table locks would conflict with the X table lock.) So, there should be no need for further changes.