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: | |
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
[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
.