| Bug #40536 | SELECT is blocked by INSERT DELAYED waiting on upgrading lock, even with low_pri | ||
|---|---|---|---|
| Submitted: | 6 Nov 2008 1:56 | Modified: | 18 Feb 21:16 |
| Reporter: | Matthew Montgomery | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.29, 5.1.23, 5.0.70 | OS: | Any |
| Assigned to: | Davi Arnaut | Target Version: | 5.1+ |
| Triage: | Triaged: D2 (Serious) | ||
[6 Nov 2008 5:43]
Valeriy Kravchuk
Thank you for a problem report. Verified on 5.1.29 also.
[6 Jan 13:25]
Bugs System
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.
[15 Jan 11:05]
Bugs System
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.
[3 Feb 18:03]
Bugs System
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.
[3 Feb 18:16]
Bugs System
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.
[4 Feb 12:19]
Davi Arnaut
Queued to 5.1-bugteam
[9 Feb 23:33]
Bugs System
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)
[14 Feb 13:59]
Bugs System
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)
[17 Feb 15:55]
Bugs System
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)
[17 Feb 17:43]
Bugs System
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)
[17 Feb 19:19]
Bugs System
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)
[18 Feb 21:16]
Paul DuBois
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.
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.