Details
-
Bug
-
Status: In Review (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.5, 10.11, 11.5(EOL)
Description
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:
10.5 ffc5d064895cadbc42711efd7dbb6ae1b323f050 |
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)!
Attachments
Issue Links
- is duplicated by
-
MDEV-21974 InnoDB DML under backup locks make buffer pool usage grow permanently
- Open
- relates to
-
MDEV-10719 'create temporary table as select' generates unnecessary table locks
- Confirmed
-
MDEV-17512 Deadlock after locking all rows in table
- Open
-
MDEV-27992 DELETE fails to delete record after blocking is released
- Closed
-
MDEV-14479 Do not acquire InnoDB record locks when covering table locks exist
- Closed
-
MDEV-16232 Use fewer mini-transactions
- Stalled
-
MDEV-17918 InnoDB sometimes prints incorrect buffer pool size in the "Over xx percent of the buffer pool is occupied" message
- Open
-
MDEV-20612 Improve InnoDB lock_sys scalability
- Closed
-
MDEV-28800 SIGABRT due to running out of memory for InnoDB locks
- Closed
-
MDEV-28804 Increased lock objects in 10.6+ leading to significant slowdown when using a limited InnoDB buffer pool, including in release builds
- Stalled
-
MDEV-29565 Inconsistent read and write, which use the same predicate (WHERE clause)
- Closed
-
MDEV-30368 Locking behaviour is missing in documentation
- Open
-
MDEV-34719 Disable purge for LOAD DATA INFILE into empty table
- In Progress