Is there a way to GUARANTEE non-blocking reads in MySQL?
I tried to make the obvious "STATIONARY ISOLATION LEVEL", but my simple stored procedure still blocks when I execute SELECT MAX on the PRIMARY KEY during updates (while running with some complex update transactions that I don't want to modify) - eventually works in Deadlock and Lock Timeouts.
There must of course 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 job?
I know of all the dangers and academically unreasonable properties of READ-UNCOMMITTED, but it doesn't matter, for my particular application, sometimes phantom or missing a line here and there really doesn't matter much, but the delay or error caused by read locks is much more serious problem.
All tables in the database are InnoDB. Server version 5.0.67. The platform is a 32-bit Linux version.
UPDATE . Here is a simplified version of the "hello world" 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 (do not close CONSOLE 1 in a separate window)
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
source to share
Finally got:
"Is this a MySQL bug?" → Yes, I would call it a mistake. Others may call it constraint or "Gotcha". I would call this BUG because it is clear that the theoretical basis, as well as the practical ability to retrieve this data without blocking, is supported by the existence of a mostly syntactic workaround.
"Is there a workaround?" → Yes.
Rewriting this
set @test = (select max (a) from t1);
like this
select max (a) from t1 into @test;
gives the same result when no other transaction is executed; and produces the expected result (the value is retrieved successfully and immediately, rather than dying on blocking) when another transaction is executed.
source to share
If you want to make sure that your updates are never blocked by your choices, I would suggest having two databases where the master is where the inserts / updates take place and through replication the data is sent to the slave where you choose.
This should limit any selection issues as replication is very fast, so your choices can be as complex as you want, and it never affects updates.
Unfortunately, even if you only had row locks, you might have problems as one query is written to a table when you try to read from that table.
Update: . Before going down, he just posted requests and an error message, so now he can help with this problem, so my answers are not wrong as he started with.
source to share
InnoDB SELECT codes are usually not blocking by default (so they can run with as many DML statements as you can jot them down). So it looks like something different than normal SELECT and DML statements.
Perhaps you are doing an INSERT ... SELECT statement or something like that? Can you post your stored procedure?
source to share