Is there a way to GUARANTEE non-blocking reads in MySQL?

Question

I have tried the obvious "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED", but my simple stored procedure still gets blocked while doing a SELECT MAX on a PRIMARY KEY while updates are going on (when running simultaneously with certain complex update transactions that I do not want to modify) -- ultimately running into Deadlocks and Lock Timeouts.

Surely there must be a way to GUARANTEE a non-blocking read... And I thought that was the purpose of READ-UNCOMMITTED. But I was wrong... Is this a MySQL bug? Is there a work-around?

I am aware of all the dangers and academically unsound properties of READ-UNCOMMITTED, but that doesn't matter, for my particular application an occasional phantom or missing row here and there is really no big deal, but the delay or error caused by the read-locks is a much more serious matter.

All tables in the database are InnoDB. Server version is 5.0.67. Platform is Linux 32-bit.

UPDATE Here's a simplified "hello world" version of the problem description (my actual queries are too complex and ugly to post):

CONSOLE 1:

mysql> create table t1(a int primary key) engine=innodb;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql> insert into t1 values (4);
Query OK, 1 row affected (0.01 sec)

mysql> update t1 set a=5 where a=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CONSOLE 2 (in separate window, do not close CONSOLE 1)

mysql> select max(a) from t1;
+--------+
| max(a) |
+--------+
|      3 | 
+--------+
1 row in set (0.00 sec)

mysql> set @test = (select max(a) from t1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Mitch: the simple query which is at the focus of my investigation is indexed enough to cover the WHERE conditions, but not all the returned columns. — Alex R
Your reads are non-blocking here, it's the INSERTs and UPDATEs that are blocking. There's no way to get around this without a master-slave setup or similar, or modifying the long-running UPDATEs as James suggests. — Ben Regenspan
Ben: Your assumption is incorrect. I have got to the bottom of this with "show engine innodb status", and it is absolutely the case, that my reads are not non-blocking (i.e. THE READS ARE IN FACT BEING BLOCKED). Everything we have ever read about InnoDB is wrong :( — Alex R

Alex R Alex R · Accepted Answer · 2009-10-26T22:39:56

Finally got it:

"Is this a MySQL bug?" --> Yes, I'd call it a bug. Others may call it a limitation or a "Gotcha". I'd call it a BUG because clearly the theoretical basis as well as practical capability of retrieving this data without a lock is evidenced by the existence of a mostly syntactical workaround.

"Is there a workaround?" --> Yes.

Rewriting this

set @test = (select max(a) from t1);

as this

select max(a) from t1 into @test;

produces the same result when the other transaction is not running; and produces the expected result (value is retrieved successfully and immediately, instead of dying on a lock) when the other transaction IS running.

Is there a way to GUARANTEE non-blocking reads in MySQL?

3 Answers