Details
-
Task
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
5.5.55
Description
CREATE TABLE `tu`(`id` int(11), `a` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, PRIMARY KEY(`id`), UNIQUE KEY `u`(`a`,`b`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0;
|
|
|
insert into tu values(1,1,'a','a'),(2,9999,'xxxx','x'),(3,10000,'b','b'),(4,4,'c','c');
|
|
|
set global innodb_status_output=ON;
|
set global innodb_status_output_locks=ON;
|
|
|
start transaction;
|
delete from tu where a = 9999 and b = 'xxxx';
|
show engine innodb status\G
|
In the output you'll see:
------------
|
TRANSACTIONS
|
------------
|
Trx id counter 636202
|
Purge done for trx's n:o < 636201 undo n:o < 0 state: running but idle
|
History list length 37
|
LIST OF TRANSACTIONS FOR EACH SESSION:
|
---TRANSACTION 636201, ACTIVE 4 sec
|
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
|
MySQL thread id 1, OS thread handle 0x7f9e513a7700, query id 90 localhost root init
|
show engine innodb status
|
TABLE LOCK table `test`.`tu` trx id 636201 lock mode IX
|
RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
|
RECORD LOCKS space id 11 page no 3 n bits 72 index `PRIMARY` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
|
--------
|
Normal and expected so far, record X locks on the unique index and primary key, we deleted and could delete one and only one row. Now, repeat the same delete in the same transaction:
delete from tu where a = 9999 and b = 'xxxx';
|
show engine innodb status\G
|
and you'll see this beautiful set of locks:
LIST OF TRANSACTIONS FOR EACH SESSION:
|
---TRANSACTION 636201, ACTIVE 202 sec
|
5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1
|
MySQL thread id 1, OS thread handle 0x7f9e513a7700, query id 92 localhost root init
|
show engine innodb status
|
TABLE LOCK table `test`.`tu` trx id 636201 lock mode IX
|
RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
|
RECORD LOCKS space id 11 page no 3 n bits 72 index `PRIMARY` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
|
RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X
|
RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X locks gap before rec
|
--------
|
Suggested fix:
Check if there is any good reason to ask for a gap X lock on a record in the secondary unique index when transaction already has the next key lock on it.
Alternatively, if this is just the way locks are reported, please, fix it so the output makes sense.
In any case, please, document in the manual what locks this kind of DELETE sets when it encountered a record already marked as deleted, and why.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-14776 InnoDB Monitor output generated by specific error is flooding error logs
-
- Closed
-
-
-
- Stalled
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-17598 InnoDB index option for per-record transaction ID
-
- Open
-
Activity
Another area for improvement is that InnoDB is unnecessarily creating explicit record locks when covering table locks already exist. That is, if the entire table is locked for reads, InnoDB would still acquire record locks for locking reads (such as INSERT…SELECT). Or if the entire table is locked for writes, UPDATE or SELECT…FOR UPDATE would still create record locks. For large transactions, the unnecessary record locks can fill the buffer pool and cause InnoDB to commit suicide:
2018-03-07 13:57:04 8000 [Warning] InnoDB: Over 67 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks. innodb_buffer_pool_size=128M. Starting the InnoDB Monitor to print diagnostics.
|
2018-03-07 13:57:09 8000 [ERROR] [FATAL] InnoDB: Over 95 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks, or review if innodb_buffer_pool_size=128M could be bigger.
|
In MDEV-16406 we could refactor the multiple record lock bitmaps per page into a single bitmap (multiple bits per record).
After MDEV-16232, also UPDATE and DELETE could mainly rely on implicit locks (page latch held between the lookup and the modification of a record), so the explicit locks would mostly be used when locking conflicts exist.
The unnecessarily created explicit lock objects can be causing warning messages in the error log, of the form "difficult to find free blocks".
Fixing MDEV-16675 Unnecessary explicit lock acquisition during UPDATE or DELETE
will reduce the amount of explicit locks, but not remove them completely.
We will still need fixes to MDEV-14479, MDEV-16232 and MDEV-16406.
During the development of
MDEV-14638we found out that apparently, if a transaction has acquired an implicit exclusive lock by inserting a record, on a subsequent locking read (such as updating the inserted record) it would convert that implicit lock into an explicit one. That can surely be avoided.