Bug #45975 DELETE by key column locks whole table
Submitted: 6 Jul 2009 15:24 Modified: 18 Dec 2009 16:56
Reporter: Dmitry Goncharov Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:4.1, 5.0, 5.1, azalea OS:Any
Assigned to: Paul Dubois CPU Architecture:Any
Tags: gap locking, innodb, lock escalation
Triage: Needs Triage: D3 (Medium)

[6 Jul 2009 15:24] Dmitry Goncharov
Description:
DELETE by key column lock the whole table until the end of transaction. 
DELETE by other key in other transaction will be blocked.

innodb_locks_unsafe_for_binlog=1 did not help.

How to repeat:
#
# Preparation
#
DROP PROCEDURE IF EXISTS T_G;

DELIMITER $$
CREATE PROCEDURE T_G(COUNT INT)
BEGIN
DECLARE N INT DEFAULT 0;

DROP TABLE IF EXISTS T;

CREATE TABLE t (
  id int(11) NOT NULL auto_increment,
  key_id int(11) NOT NULL,
  PRIMARY KEY  (id)
);

CREATE INDEX idx_t_key_id on t(key_id);

WHILE N < COUNT DO

INSERT into t (key_id) values (1),(2),(3);
SET N = N + 1;

END WHILE;

END$$
DELIMITER ;

# 11 Rows cause to whole table lock on MySQL 5.0.67 32bit (Windows XP). 10 rows don't cause to lock.
CALL T_G(11);

/***************************************************

####################################################
# 1st example
# Opens two mysql console.
# Run separatelly A, then B.
# B will be blocked by A
#

###
# Scenario A

START TRANSACTION;
DELETE FROM t WHERE key_id = 2;

###
# Scenario B

START TRANSACTION;
DELETE FROM t WHERE key_id = 3;

****************************************************/
[6 Jul 2009 16:33] Valeriy Kravchuk
Please, send the results of:

explain SELECT * FROM t WHERE key_id = 2;
explain SELECT * FROM t WHERE key_id = 3;

for cases when you see the lock and don't see the lock. I think that because of too few rows in a table or wrong statistics you will get ALL as access type in case where entire table is locked...
[7 Jul 2009 8:33] Dmitry Goncharov
Server version: 5.0.67-community-nt MySQL Community Edition (GPL)
innodb_locks_unsafe_for_binlog=1

Don’t lock case. (CALL T_G(2))
##############################
#
# Session A
#
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT * FROM t WHERE key_id = 2;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | idx_t_key_id  | idx_t_key_id | 4       | const |    2 | Using index | 
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> DELETE FROM t WHERE key_id = 2;
Query OK, 2 rows affected (0.00 sec)

############
# 
# Session B
#
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT * FROM t WHERE key_id = 3;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | idx_t_key_id  | idx_t_key_id | 4       | const |    2 | Using index | 
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE key_id = 3;
Query OK, 2 rows affected (0.00 sec)

Lock case. (CALL T_G(3))
########################
#
# Session A
#
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT * FROM t WHERE key_id = 2;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | idx_t_key_id  | idx_t_key_id | 4       | const |    3 | Using index | 
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE key_id = 2;
Query OK, 3 rows affected (0.00 sec)

#############
# 
# Session B
#
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT * FROM t WHERE key_id = 3;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | idx_t_key_id  | idx_t_key_id | 4       | const |    3 | Using index | 
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE key_id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

#########################################################################################
#########################################################################################
#########################################################################################

Server version: 5.0.67-community-nt MySQL Community Edition (GPL)
innodb_locks_unsafe_for_binlog is OFF

Don’t lock case. (CALL T_G(3))
##############################
#
# Session A
#
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT * FROM t WHERE key_id = 2;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | idx_t_key_id  | idx_t_key_id | 4       | const |    3 | Using index | 
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE key_id = 2;
Query OK, 3 rows affected (0.02 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

#############
#
# Session B*
#
mysql> explain SELECT * FROM t WHERE id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

Lock case. (CALL T_G(3))
########################
#
# Session A
#
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT * FROM t WHERE key_id = 2;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | idx_t_key_id  | idx_t_key_id | 4       | const |    4 | Using index | 
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE key_id = 2;
Query OK, 4 rows affected (0.00 sec)

##############
#
# Session B*
# 
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT * FROM t WHERE id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[13 Jul 2009 10:03] Sveta Smirnova
Thank you for the feedback.

After running procedure provided I do SELECT:

select * FROM t WHERE key_id = 2;
id      key_id
2       2
5       2
8       2
11      2
14      2
17      2
20      2
23      2
26      2
29      2
32      2
select * FROM t WHERE key_id = 3;
id      key_id
3       3
6       3

(following rows are deleted to get minimum rows problem repeatable with)

So you have row with key_id=2 and id=2 and id=5 while you have row with key_id=3 and id=3 and id=6. InnoDB locks gaps before row selected, this is why you meet deadlock. See also http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html
[13 Jul 2009 16:17] Dmitry Goncharov
So, it's only one way to avoid gap lock: 
DELETE every row by id in separate statement?

Others don't work:
* innodb_locks_unsafe_for_binlog=1
* DELETE FROM t WHERE id IN (ids,...);
* DELETE FROM t WHERE id = id1 or id = id2 ...;
[13 Jul 2009 16:23] Dmitry Goncharov
Status changed to Open to clarify situation with gap lock for DELETE.
Actual documentation (http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html) don't provide clear case to avoid gap locking.
[14 Jul 2009 5:55] Sveta Smirnova
Thank you for the feedback.

You are right: this should be at least clarified.
[20 Oct 2009 19:25] Paul Dubois
"Actual documentation (http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html) don't
provide clear case to avoid gap locking."

The first paragraph of that section says this, which appears to cover the situation (note end of para):

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. The locks are normally next-key locks that also block inserts into the “gap” immediately before the record. However, gap locking can be disabled explicitly, which causes next-key locking not to be used. For more information, see Section 13.6.8.4, “InnoDB Record, Gap, and Next-Key Locks”.
[20 Oct 2009 19:26] Paul Dubois
Section 13.6.8.4 says:

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.
[21 Oct 2009 10:21] Dmitry Goncharov
OK
1. I’ve disable gap lock explicitly (innodb_locks_unsafe_for_binlog=1).
2. Run two sessions.

Case A:
Transaction 1
mysql> delete from t where id in (1, 4, 7, 10, 13, 16, 19 );
Query OK, 7 rows affected (0.00 sec)

Transaction 2
mysql> delete from t where id in (2, 5, 8, 11, 14, 17 );
Query OK, 6 rows affected (0.00 sec)

Case B:
Transaction 1
mysql> delete from t where id in (1, 4, 7, 10, 13, 16, 19 );
Query OK, 7 rows affected (0.00 sec)

Transaction 2
mysql> delete from t where id in (2, 5, 8, 11, 14, 17, 20 );
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

* Gap locking disabled explicitly.
* Transaction manipulates with different set of records. 
* Manipulation performed by primary key.

Why INNODB allow to delete 7 & 6 rows & don’t allow to delete 7 & 7 rows?
What kind of lock(s) is preventing to delete 7 & 7 rows?

In fact, it’s only one possible way to delete 7 (and more) rows in different transactions:

Use statement "DELETE FROM t WHERE id = ?" 7 (and more) times.

All others are not work.
[28 Oct 2009 14:42] Paul Dubois
Dmitry, your previous comment switches the scenario from the one that you originally reported. Let's deal with the original scenario first. Does the documentation I referenced explain the behavior you originally reported, and does enabling innodb_locks_unsafe_for_binlog result in ability to perform the transaction without timeout?
[28 Oct 2009 16:42] Dmitry Goncharov
innodb_locks_unsafe_for_binlog=1 does not allow to perform concurrent transactions.

It isn’t clear to me what kind of lock is preventing to concurrent transaction execution.

Sveta Smirnova says that "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction" is caused by gap lock.

For this case documentation says:
* gap lock (with innodb_locks_unsafe_for_binlog=1) is used only for foreign-key constraint checking and duplicate-key checking.
*  InnoDB should set lock on every index record that is scanned and InnoDB knows which index ranges were scanned.

I expect that index range for "DELETE FROM t WHERE key_id = 2" does not intersect with index range for "DELETE FROM t WHERE key_id = 3".
[16 Nov 2009 11:11] Marko Mäkelä
This could be caused by Bug #19762. Until delete-marked records have been purged, both transactions could be attempting to lock them. I don’t think that InnoDB is reporting the offending lock when a lock wait timeout occurs. It could be a worthwhile addition.
[1 Dec 2009 8:50] Alexandr Kormushin
I've encountered same problem. 

Try to use multiple delete as workaround. 

DELETE t T FROM t T join t T1 ON (T.id = T1.id) WHERE T1.key_id = 2;

It doesn't lock whole table and work faster that select by key_id and then deleting by id.
[10 Dec 2009 11:59] Dmitry Goncharov
Thanks you, Alexandr!

Looks like, your solution works fine in my case.

Paul, will be very nice, if MySQL documentation will have better description of gap lock for original and workaround cases.
[15 Dec 2009 16:33] Martin Martin
Dmitry,

thanks a lot for your report, it helped us a lot. There is one issue, CREATE statement you provided defaults to MyISAM, where of course problem is not reproducible. For reference I am including CREATE command which led to successful reproduction:

CREATE TABLE t (
  id int(11) NOT NULL auto_increment,
  key_id int(11) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=InnoDB;

Also, for 'innodb_locks_unsafe_for_binlog is OFF' Lock Case, it should say CALL T_G(4) (not CALL T_G(3)).

Thanks also goes to Paul for explanation. Cheers,
Martin
[16 Dec 2009 4:10] Paul Dubois
GIven that this is a server bug per Marko's remarks, I'm not going to treat this as a docs bug.
[18 Dec 2009 10:49] Dmitry Goncharov
If this is a server bug, then it's very strange to close it as `Won't fix`.
An my opinion it should be assigned to some developer | closed as duplicated of some existed server bug.

Actually it's not clear when this verified server bug will be fixed.
[18 Dec 2009 16:21] Paul Dubois
The server bug is Bug#19762, not the present bug. The present bug is "won't fix" because the issue reported here will be dealt with by fixing Bug#19762.
[18 Dec 2009 16:56] Dmitry Goncharov
Link to related bug is enough. Thank you.