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

Answer 1

If you want to make certain that your updates will never be blocked by your selects, then I would suggest having two databases, where the master is where inserts/updates take place, and through replication the data is sent to the slave, where you do your selects.

This should limit any problems from selects, as the replication is very fast, so your selects can be as complicated as you want, and it will never impact the updates.

Unfortunately, even if you just had row locking, you may still have problems, as one query is writing to a table while trying to read from that table.

Update: Before you downvote, he just recently put up the queries and error message, so now he can help help on the problem, so my responses aren't wrong, for what he started with.

Answer 2

InnoDB SELECTs are normally non-locking by default (such that they can run with as many DML statements as you can throw at it). So it seems like something other than just normal SELECTs and DML statements are going on.

Perhaps you are doing an INSERT...SELECT statement or something like that? Can you post your stored procedure?

Answer 3

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.