Bug #49886 Concurrent INSERT visibility in LOCK ... READ LOCAL mode undefined/unclear
Submitted: 23 Dec 2009 8:04 Modified: 8 Jan 2010 9:15
Reporter: Vladimir Kolesnikov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.x, 5.1.x, 6.0.14 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[23 Dec 2009 8:04] Vladimir Kolesnikov
Description:
This is a least a documentation bug, but maybe this is also a bug in MyISAM implementation. 

If a session in auto-commit mode locks a MyISAM table in READ LOCAL mode then another session can insert rows into this table. However inserted rows are not visible to the locker session until UNLOCK TABLES was executed. This visibility limitation is not documented anywhere and it is non-standard as visibility is usually controlled by transaction boundaries (in auto-commit mode that would match statement boundaries).

A clarification/fix is needed.

How to repeat:
create table t1 (a int not null, primary key(a));
create table t2 (a int not null, b int not null, primary key(a,b));
insert into t1 values (1),(2),(3),(4),(5),(6);
insert into t2 values (1,1),(2,1);
lock tables t1 read local, t2 read local;
select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
# the following statement will not block in MyISAM
insert into t2 values(2,0);
disconnect root;
connection default;
# the following statement doesn't see the (2,0) tuple 
select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
drop table t1,t2;
[23 Dec 2009 8:46] Valeriy Kravchuk
Verified just as described with recent 5.1.43 from bzr:

openxs@suse:/home2/openxs/dbs/5.1/mysql-test> ./mysql-test-run.pl bug49886
Logging: ./mysql-test-run.pl  bug49886
100330 15:39:32 [Note] Plugin 'FEDERATED' is disabled.
100330 15:39:32 [Note] Plugin 'ndbcluster' is disabled.
MySQL Version 5.1.43
Checking supported features...
 - using ndbcluster when necessary, mysqld supports it
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /home2/openxs/dbs/5.1/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/home2/openxs/dbs/5.1/mysql-test/var'...
Installing system database...
Using server port 32769
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

main.bug49886                            [ fail ]
        Test ended at 2010-03-30 15:39:39

CURRENT_TEST: main.bug49886
--- /home2/openxs/dbs/5.1/mysql-test/r/bug49886.result  2010-03-30 15:39:15.000000000 +0300
+++ /home2/openxs/dbs/5.1/mysql-test/r/bug49886.reject  2010-03-30 15:39:38.000000000 +0300
@@ -0,0 +1,15 @@
+create table t1 (a int not null, primary key(a));
+create table t2 (a int not null, b int not null, primary key(a,b));
+insert into t1 values (1),(2),(3),(4),(5),(6);
+insert into t2 values (1,1),(2,1);
+lock tables t1 read local, t2 read local;
+select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
+a      a       b
+1      1       1
+2      2       1
+insert into t2 values(2,0);
+select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
+a      a       b
+1      1       1
+2      2       1
+drop table t1,t2;
[23 Dec 2009 8:52] Valeriy Kravchuk
On mysql-6.0-codebase I've got also:

CURRENT_TEST: main.bug49886
mysqltest: At line 14: query 'drop table t1,t2' failed: 1099: Table 't1' was locked with a READ lock and can't be updated

but this is expected.
[7 Jan 2010 16:21] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

In http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html
added "The results of a concurrent INSERT may not be visible immediately".
[7 Jan 2010 21:01] Vladimir Kolesnikov
Hi Paul,

from the point of view of someone who wants to understand how server (or an engine) works or should work this doc update doesn't clarify anything. Suppose I want to write an app that uses LOCK ... READ LOCAL. Should I expect the rows are visible or not? Is it possible to add a more deterministic specification?

Thanks.
[8 Jan 2010 9:15] Vladimir Kolesnikov
.