MySQL Bugs: #60951: DELETE statement locks

Bug #60951	DELETE statement locks
Submitted:	22 Apr 2011 11:21	Modified:	5 Aug 2011 18:25
Reporter:	Vinod Sugur	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.1.55-community-log, 5.1.59, 5.6.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Innodb locking, regression

Description:
Delete holds on table level lock even if the WHERE clause criteria has different row selection criteria and next key locking is disabled.

How to repeat:
Steps:

1. innodb_locks_unsafe_for_binlog variable is enabled

mysql> show variables like '%unsafe%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | ON    |
+--------------------------------+-------+
1 row in set (0.00 sec)
 
2.  Table creation script:

CREATE TABLE `dml_lock_test` (
  `id` int(11) NOT NULL,
  `key_id` decimal(10,0) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_kid` (`key_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

3. Insert script:

INSERT INTO dml_lock_test values(1,1,null);
INSERT INTO dml_lock_test values(2,2,null);
INSERT INTO dml_lock_test values(3,3,null);
INSERT INTO dml_lock_test values(4,4,null);
INSERT INTO dml_lock_test values(5,5,null);
INSERT INTO dml_lock_test values(6,6,null);
INSERT INTO dml_lock_test values(7,7,null);
INSERT INTO dml_lock_test values(8,8,null);
INSERT INTO dml_lock_test values(9,9,null);
INSERT INTO dml_lock_test values(10,10,null);
INSERT INTO dml_lock_test values(11,11,null);
INSERT INTO dml_lock_test values(12,12,null);
INSERT INTO dml_lock_test values(13,13,null);
INSERT INTO dml_lock_test values(14,14,null);
INSERT INTO dml_lock_test values(15,15,null);
INSERT INTO dml_lock_test values(16,16,null);

5. Execute following statement in session 1

set autocommit = 0;

update dml_lock_test set name = 'test'  where key_id in (2,4);

6. Execute following statement in session 2

set autocommit = 0;

delete from dml_lock_test  where key_id in (7,8,9);

# try out for different values if you did not get error

The above delete statement generated following error
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

It seems delete statement is waiting for lock to be released by session 1. This seems to be an issue with DELETE statement as update works in parallel for same id's;

mysql> update dml_lock_test
    -> set name = 'test'
    -> where key_id in (7,8,9);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Suggested fix:
DELETE statement should work as next key locking is disabled.

In my case both DELETE and UPDATE wait for the lock, and this is the reason IMHO:

mysql> explain select name from dml_lock_test where key_id in (7,8,9);
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | dml_lock_test | ALL  | idx_kid       | NULL | NULL    | NULL |   16 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.03 sec)

As there are few rows in the table index is NOT used and all rows are scanned. As row is read for update during the scan, we get attempt to X lock it, that has to wait eventually on one of the rows locked by the first transaction, like this:

LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s)
MySQL thread id 4, query id 193 localhost root updating
delete from dml_lock_test  where key_id in (7,8,9)
------- TRX HAS BEEN WAITING 19 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 564 n bits 88 index `PRIMARY` of table `test`.`dml_lock_test` trx id 3D12 lock_mode X waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000003d11; asc     = ;;
 2: len 7; hex 120000022602ec; asc     &  ;;
 3: len 5; hex 8000000002; asc      ;;
 4: len 4; hex 74657374; asc test;;

Question is: what is the result of EXPLAIN for the SELECT with the same WHERE clause as DELETE (or UPDATE) in your case? If you see type = ALL there, this is not a bug.

Does the scenario mentioned by you holds true even if next key locking is disabled?

Surely, when ALL is used we lock every record, not "gaps", no matter if next key locking is used or not. We are waiting for X lock on the very first data record changed by other transaction we encounter while scanning the entire table.

In the below scenario update statement does execute in parallel even if it scans all rows.

The only scenario is when we execute delete it parallel it blocks. This is bug with DELETE statement.

=========================
mysql> explain select * from dml_lock_test where key_id in (2,4,14);
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | dml_lock_test | ALL  | idx_kid       | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Session 1:

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> update dml_lock_test set name = 'test'  where key_id in (2,4,14);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> update dml_lock_test set name = 'test'  where key_id in (6,10,12);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Session 2:

mysql> update dml_lock_test set name = 'test'  where key_id in (3,5,16);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
=========================

Please update on this issue.

Thank you for the feedback.

Verified as described: DELETE fails with lock timeout while UPDATE succeeds. This is regression as in version 5.0 both query fail.