Locking full table scan fails to use table-level locking

The following test case shows that table locks are not being passed to the storage engine in any of the cases of locking read.

--source include/have_innodb.inc

--source include/have_sequence.inc

--source include/not_debug.inc

SET @save_freq=@@GLOBAL.innodb_purge_rseg_truncate_frequency;

SET GLOBAL innodb_purge_rseg_truncate_frequency=1;

CREATE TABLE t (a INT PRIMARY KEY) ENGINE=InnoDB;

INSERT INTO t(a) SELECT seq FROM seq_0_to_1000000;

--source include/wait_all_purged.inc

SET GLOBAL innodb_purge_rseg_truncate_frequency=@save_freq;

SET profiling_history_size=100;

SET profiling = 1;

START TRANSACTION;

LOCK TABLE t WRITE;

SELECT COUNT(*) FROM t FOR UPDATE;

UNLOCK TABLES;

COMMIT;

SELECT COUNT(*) FROM t FOR UPDATE;

START TRANSACTION;

LOCK TABLE t READ;

SELECT COUNT(*) FROM t LOCK IN SHARE MODE;

UNLOCK TABLES;

COMMIT;

SELECT COUNT(*) FROM t LOCK IN SHARE MODE;

SHOW PROFILES;

SET profiling = 0;

DROP TABLE t;

I would have expected that an explicit LOCK TABLE would be passed to the storage engine, like it seems to have been the case during the development of MDEV-14479.

I would also expect the optimizer to automatically pass information to the storage engine so that the table can be locked upfront, if we know that the entire table will be scanned in a locking operation. This should include the following:

• CREATE…SELECT
• INSERT…SELECT
• SELECT…LOCK IN SHARE MODE
• SELECT…FOR UPDATE
• SELECT executed at TRANSACTION ISOLATION LEVEL SERIALIZABLE
• UPDATE
• DELETE

If no WHERE or JOIN condition applies to the table, nor a LIMIT is present, then we should expect the operation to scan the entire table, and the storage engine should be requested to lock the entire table.

MDEV-14479 fixed the InnoDB record locking in such a way that when the table has already been locked in the corresponding mode, no individual record locks will be allocated and created. If we set a breakpoint in lock_create_low() after ha_innobase::create() has finished in the above test case, we will observe the following:

Thread 15 "mariadbd" hit Breakpoint 1, lock_rec_create_low (c_lock=0x0, 

    thr=0x0, type_mode=3, page_id={m_id = 21474836484}, 

    page=0x7ffff0b70000 "", heap_no=2, index=0x7fffbc01af40, 

    trx=0x7ffff17260d8, holds_trx_mutex=false)

    at /mariadb/10.5m/storage/innobase/lock/lock0lock.cc:1294

(gdb) p index.table.locks

$7 = {count = 1, start = 0x7ffff1726cb0, end = 0x7ffff1726cb0, 

  node = &lock_table_t::locks}

(gdb) p *index.table.locks.start

$8 = {trx = 0x7ffff17260d8, trx_locks = {prev = 0x0, next = 0x0}, 

  index = 0x0, hash = 0x0, requested_time = 0, wait_time = 0, un_member = {

    tab_lock = {table = 0x7fffbc019e10, locks = {prev = 0x0, next = 0x0}}, 

    rec_lock = {page_id = {m_id = 140736347610640}, n_bits = 0}}, 

  type_mode = 17}

Note: type_mode == 17 == LOCK_TABLE | LOCK_IX. For the LOCK IN SHARE MODE, it would be 16 == LOCK_TABLE | LOCK_IS. We would expect the table lock to exist in LOCK_X or LOCK_S mode, so that no locks will have to be allocated for each individual visited row.

If we change the CREATE TABLE to CREATE TEMPORARY TABLE, we can get an accurate estimate of how fast the execution would be without the row-level locking.

On my system, with locking, each full-table-scan consumes between 0.49 and 0.66 seconds (the first run being the slowest). This can probably be attributed to dynamic memory allocation.

With a temporary table, each locking full-table scan consumes between 0.28 and 0.30 seconds. The memory operations related to row-level locking are roughly doubling the execution time (and seriously increasing the memory footprint)!