MySQL Bugs: #63826: inserts get more priority than update when requesting for lock

Bug #63826	inserts get more priority than update when requesting for lock
Submitted:	21 Dec 2011 17:55	Modified:	23 Dec 2011 2:30
Reporter:	vishnu chanderraju (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.49,5.5.16	OS:	MacOS (10.7.1)
Assigned to:		CPU Architecture:	Any
Tags:	insert more priority update innodb

Description:
here is the scenario.

table engine = innodb
table has a primary key and secondary index.
transaction isolation level = read commited

Transaction 1 comes in and does an insert. 
Transaction 2 then comes in and tries to update the columns which are part of the secondary index (which blocks as transaction 1 has not committed & Txn 1 has locked the index)
After this, Transaction 3 then comes in & does an insert.

Now if Transaction 1 , commits -  the update query of Transaction 2 should complete as it was 2nd in line for the requesting to lock the index. but it still hangs.

if you commit  Transaction 3 , this will allow the update query to complete.

So from this, the inserts get priority over the update query when it comes to lock the index.

How to repeat:
Pre conditions:
=================

CREATE TABLE `t7_ibu` (
`id` int(11) NOT NULL,
`status` varchar(25) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `status_idx` (`status`)
) ENGINE=InnoDB

insert into t7_ibu values(4,'service_pending');

transaction isolation level = read committed
--------------------------------------------------------------

Test:
=====>

TXN1:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t7_ibu values(5,'service_pending');  ## STEP - 1
Query OK, 1 row affected (0.00 sec)

TXN2:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update t7_ibu set status='service_queued' where status='service_pending' ; ## STEP - 2

--->this blocks

TXN3:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t7_ibu values(6,'service_pending'); ## STEP - 3
Query OK, 1 row affected (0.00 sec)

TXN1:

commit;  -->## STEP - 4
Query OK, 0 rows affected (0.00 sec)

---------------------------------------------
Now notice, after step 4 ... the update query of Txn2 still hangs.
---------------------------------------------

if i commit Txn3, then only will the update query successfully execute.

although the update query of Transaction 2 should complete as it was 2nd in line for the requesting to lock the index. but it has to wait for a transaction that comes after to commit/rollback.

So from this, the inserts get priority over the update query when it comes to lock the index.

Suggested fix:
na

Please, send the output of:

explain select * from t7_ibu where status='service_pending' ;

Looks like index is not used in this UPDATE, so it tires to set X lock on every row in the table, and thus is blocked. Unlike INSERT, that does not try to lock row inserted by other INSERT.

hi Valeriy,

here is the explain output:

explain select * from t7_ibu where status='service_pending' ;
+----+-------------+--------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key        | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t7_ibu | ref  | status_idx    | status_idx | 28      | const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+------------+---------+-------+------+--------------------------+

do let me know if you need more info.

thanking you,

with regards,
vishnu rao

This is not a bug: 2 INSERT queries insert into different parts of index, so first query does not lock second, so it can run and lock UPDATE.

hi Sveta,

thanks for the clarification. Yes, the first and second insert modify different parts of the index and hence are allowed to run, but this could lead to starvation for the update query.

So in theory: the update can continue to wait for infinity if the inserts are infinite i.e. lead to starvation.

in our production set up, the update waits for a long time as there are many inserts happening at the time.

so ,one solution is to drop the index or do a select and do an update using primary key.

Thanking you,

with regards,
ch vishnu