title: Is there a way to GUARANTEE non-blocking reads in MySQL?
body: <p>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.</p>
  
  <p>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?</p>
  
  <p>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.</p>
  
  <p>All tables in the database are InnoDB.
  Server version is 5.0.67. Platform is Linux 32-bit.</p>
  
  <p><strong><em>UPDATE</em></strong> Here's a simplified "hello world" version of the problem description (my actual queries are too complex and ugly to post):</p>
  
  <p>CONSOLE 1:</p>
  
  <pre>
  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
  </pre>
  
  <p>CONSOLE 2 (in separate window, do not close CONSOLE 1)</p>
  
  <pre>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
  </pre>