MySQL Bugs: #40536: SELECT is blocked by INSERT DELAYED waiting on upgrading lock, even with low

Bug #40536	SELECT is blocked by INSERT DELAYED waiting on upgrading lock, even with low_pri
Submitted:	6 Nov 2008 0:56	Modified:	18 Feb 2009 20:16
Reporter:	Matthew Montgomery	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.29, 5.1.23, 5.0.70	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any

Description:
This bug is very similar to 28587 where INSERT block selects even while low_priority_updates=1 is set.

This was corrected in 5.0.50 and 5.1.23

Instead here the low_priority_updates variable is not recognized by the delayed insert thread. 

How to repeat:
Set low_priority_updates=1 in my.cnf

mysql> show variables like 'low_pri%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| low_priority_updates | ON    | 
+----------------------+-------+
1 row in set (0.00 sec)

Connection 1
create table foo (a int, b int) engine=myisam;
lock table foo read;

Connection 2
insert delayed into foo values(1,2); 

Connection 3
select * from foo;
(this connection blocks)

+----+---------+-----------+------+----------------+------+----------------+-------------------+
| Id | User    | Host      | db   | Command        | Time | State          | Info              |
+----+---------+-----------+------+----------------+------+----------------+-------------------+
|  1 | root    | localhost | test | Sleep          |  220 |                | NULL              | 
|  2 | root    | localhost | test | Query          |    0 | NULL           | show processlist  | 
|  3 | DELAYED | localhost | test | Delayed insert |  214 | upgrading lock |                   | 
|  4 | root    | localhost | test | Query          |  210 | Locked         | select * from foo | 
+----+---------+-----------+------+----------------+------+----------------+-------------------+
4 rows in set (0.00 sec)

Suggested fix:
Create a new mysqld option

low_priority_delayed_updates

With this variable set to 1, the delayed insert thread would perform low priority updates.  The reason I suggest a new variable is to allow the possibility of having regular high priority updates for non-delayed threads.

Thank you for a problem report. Verified on 5.1.29 also.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62523

2728 Davi Arnaut	2009-01-06
      Bug#40536: SELECT is blocked by INSERT DELAYED waiting on
                 upgrading lock, even with low_pri
      
      The problem is that there is no mechanism to control whether a
      delayed insert takes a high or low priority lock on a table.
      
      The solution is to add a new global variable which specifies
      whether a delayed insert thread should take high or low priority
      locks. The name of the variable is low_priority_delayed_updates.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63334

2734 Davi Arnaut	2009-01-15
      Bug#40536: SELECT is blocked by INSERT DELAYED waiting on
                 upgrading lock, even with low_pri
      
      The problem is that there is no mechanism to control whether a
      delayed insert takes a high or low priority lock on a table.
      
      The solution is to modify the delayed insert thread ("handler")
      to take into account the global value of low_priority_updates
      when taking table locks.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65028

2768 Davi Arnaut	2009-02-03
      Bug#40536: SELECT is blocked by INSERT DELAYED waiting on
                 upgrading lock, even with low_pri
      
      The problem is that there is no mechanism to control whether a
      delayed insert takes a high or low priority lock on a table.
      
      The solution is to modify the delayed insert thread ("handler")
      to take into account the global value of low_priority_updates
      when taking table locks. The value of low_priority_updates is
      retrieved when the insert delayed thread is created and will
      remain the same for the duration of the thread.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65035

2768 Davi Arnaut	2009-02-03
      Bug#40536: SELECT is blocked by INSERT DELAYED waiting on
                 upgrading lock, even with low_priority_updates
      
      The problem is that there is no mechanism to control whether a
      delayed insert takes a high or low priority lock on a table.
      
      The solution is to modify the delayed insert thread ("handler")
      to take into account the global value of low_priority_updates
      when taking table locks. The value of low_priority_updates is
      retrieved when the insert delayed thread is created and will
      remain the same for the duration of the thread.

Queued to 5.1-bugteam

Pushed into 5.1.32 (revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (version source revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (merge vers: 5.1.32) (pib:6)

Pushed into 6.0.10-alpha (revid:matthias.leich@sun.com-20090212211028-y72faag15q3z3szy) (version source revid:aelkin@mysql.com-20090204120909-2ri3xnburkifbsl5) (merge vers: 6.0.10-alpha) (pib:6)

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090216083408-rmvyaxjt6mk8sg1y) (merge vers: 5.1.32-ndb-6.3.23) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090216083646-m8st11oj1hhfuuh5) (merge vers: 5.1.32-ndb-6.4.3) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090211111208-wf0acl7c1vl5653e) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)

Noted in 5.1.32, 6.0.10 changelogs.

SELECT statements could be blocked by INSERT DELAYED statements that
were waiting for a lock, even with low_priority_updates enabled.