MySQL Bugs: #28587: SELECT is blocked by INSERT waiting on read lock, even with low_priority

Bug #28587	SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates
Submitted:	22 May 2007 9:26	Modified:	23 Oct 2007 15:56
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.0.46-BK, 5.1.19-BK, 5.0.40, 4.1.19	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	bfsm_2007_05_31, bfsm_2007_07_19

Description:
SELECT can be blocked by connection waiting for a write lock because of explicit READ table lock set.

How to repeat:
connection 1) create table foo2 (a int, b int, unique key foo2$a (a));
connection 1) lock table foo2 read;
connection 2) insert into foo2 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;
connection 3) select * from foo2;

INSERT is waiting for lock. But why SELECT in connection 3 is waiting for lock? We have the following situation (mysqladmin debug):

Thread database.table_name          Locked/Waiting        Lock_type

1       test.foo2                   Waiting - write       High priority write lock
6       test.foo2                   Waiting - read        Low priority read lock
7       test.foo2                   Locked - read         Read lock  without concurrent inserts

mysql> show processlist;
+----+------+----------------+------+---------+------+--------+-----------------
-----------------------------------------+
| Id | User | Host           | db   | Command | Time | State  | Info
                                         |
+----+------+----------------+------+---------+------+--------+-----------------
-----------------------------------------+
|  1 | root | localhost      | test | Query   |    4 | Locked | insert into foo2
 values(1,4) on duplicate key update b=2 |
|  6 | root | localhost      | test | Query   |    3 | Locked | select * from fo
o2                                       |
|  7 | root | localhost      | test | Query   |    0 | NULL   | show processlist
                                         |
+----+------+----------------+------+---------+------+--------+-----------------
-----------------------------------------+
6 rows in set (0.00 sec)

Without INSERT in connection 2 concurrent SELECT from READ locked table is surely possible.

Suggested fix:
Do not create a "lock waiting queue" where wait for write lock blocks following read lock requests. Or use high priority read locks for SELECTs by default.

Why is it a bug ?
Locks are usually stored in a queue and processed in fifo order to prevent lock starvation. One can use SELECT HIGH_PRIORITY or INSERT LOW_PRIORITY to alter that. One can even make LOW_PRIORITY the default for INSERTs.

I absolutely agree with storing locks in queue. But why connection 1 requested a low level read lock if it had already got a higher level read lock? This is a bug, IMHO. 

If I locked a table for reads already, all my further SELECT statements does NOT really need any more read locks on that table. Otherwise LOCK TABLE becomes almost useless. It is not always possible to add HIGH_PRIORITY/LOW_PRIORITY to SQL statements (it is MySQL's extension of SQL), and default LOW_PRIORITY for all INSERTs may be also bad option in some cases. SQL standard gives us LOCK TABLE as an easy way to get exclusive access for the table (with minimal resources wasted).  We should just respect locks set explicitely with LOCK TABLE.

> But why connection 1 requested a low level read lock
> if it had already got a higher level read lock?

What do you mean ? In you test case you have

connection 1> LOCK TABLE
connection 2> INSERT
connection 3> SELECT

So, yes, after a LOCK TABLE, the first connection can do selects, no problem. But connection 3 will have to wait for connection 2.

Let me try to explain how I understand the situation.

At a point in time thread1 has a shared read lock on the table.
Thread2 wants to have an exclusive write lock and has to wait.
Thread3 wants to have a shared read lock.

By default, read locks have low priority, write locks have high priority.

When thread3 comes with its low priority lock request, it finds an incompatible high priority lock request. It queues up until the incompatible high priority lock is gone.

If I understand Valeriy correct, then he suggests to let the second read lock bypass. The existing read lock is compatible and so thread3 could also get its lock and read the table.

If we did so, we would also allow the following. Thread1 may unlock his read lock, while thread3 continues to work with its read lock. Thread4 comes in with another read lock. We would let it bypass the write lock request for the same reason as above. As long as there are read requests, they could go ahead and the write lock request would starve.

The confusion might result from the assumption that a LOCK TABLE takes relatively long, while most implicit read locks finish relatively fast. If we knew with some probability that thread3 would finish before thread1 releases its lock, we could indeed let it go without defering thread2.

However, MySQL does not have that knowlegde. The application developer, however, could know which of his statements takes how long. And if he thinks that thread3 can finish within the lock time of thread1, he can use HIGH_PRIORITY to bypass the write lock. That's what Sergei suggested, if I understood correctly.

Updated description: SELECT is blocked by INSERT ... ON DUPLICATE KEY UPDATE even when server is started with --low_priority_updates. See initial how to repeat:

Connection 1:

openxs@linux:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.46-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table foo2;
Query OK, 0 rows affected (0.02 sec)

mysql> create table foo2 (a int, b int, unique key foo2$a (a));
Query OK, 0 rows affected (0.03 sec)

mysql> lock table foo2 read;
Query OK, 0 rows affected (0.00 sec)

Connection 2:

openxs@linux:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.46-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'low%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| low_priority_updates   | ON    |
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
3 rows in set (0.00 sec)

mysql> insert into foo2 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;

It is bloacked.

Connection 3 (with id 1):

mysql> select * from foo2;

It is also blocked. In connection 1:

mysql> show processlist;
+----+------+-----------+------+---------+------+--------+--------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State  | Info                                                         |
+----+------+-----------+------+---------+------+--------+--------------------------------------------------------------+
|  1 | root | localhost | test | Query   |   37 | Locked | select * from foo2                                           |
|  2 | root | localhost | test | Query   |   41 | Locked | insert into foo2 values (1, 2) ON DUPLICATE KEY UPDATE b = 2 |
|  3 | root | localhost | test | Query   |    0 | NULL   | show processlist                                             |
+----+------+-----------+------+---------+------+--------+--------------------------------------------------------------+
3 rows in set (0.00 sec)

So, this IS a serious bug. We have no way to prevent INSERT ... ON DUPLICATE KEY UPDATE from blocking further SELECTs it if was blocked.

The bug as formulated by the last test case by Valeriy is valid and can be fixed.
Below is the patch:
===== sql_insert.cc 1.248 vs edited =====
--- 1.248/sql/sql_insert.cc	2007-07-30 19:27:30 +04:00
+++ edited/sql_insert.cc	2007-08-08 15:13:48 +04:00
@@ -417,7 +417,7 @@ void upgrade_lock_type(THD *thd, thr_loc
   if (duplic == DUP_UPDATE ||
       duplic == DUP_REPLACE && *lock_type == TL_WRITE_CONCURRENT_INSERT)
   {
-    *lock_type= TL_WRITE;
+    *lock_type= TL_WRITE_DEFAULT;
     return;
   }

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/33465

ChangeSet@1.2508, 2007-08-30 14:53:17-03:00, davi@moksha.local +3 -0
  Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates
  
  The problem is that a SELECT on one thread is blocked by INSERT ... ON
  DUPLICATE KEY UPDATE on another thread even when low_priority_updates is
  activated.
  
  The solution is to possibly downgrade the lock type to the setting of
  low_priority_updates if the INSERT cannot be concurrent.

Pushed into 5.1.23-beta

Pushed into 5.0.50

Noted in 5.0.50, 5.1.23 changelogs.