MySQL Bugs: #36618: myisam insert not immediately visible to select from another client

Bug #36618	myisam insert not immediately visible to select from another client
Submitted:	9 May 2008 9:36	Modified:	8 Feb 2010 22:30
Reporter:	Sven Sandberg	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.1, 6.0	OS:	Linux (ubuntu)
Assigned to:		CPU Architecture:	Any
Tags:	client, insert, insert vs select causality failure, linux, myisam, race condition, regression, valgrind

Description:
After INSERT in one client has returned, I can do SELECT in another client without seeing the inserted row. The inserted row reappears shortly afterwards.

I'm using Ubuntu 7.10 on my laptop, HP Compaq 8710w. It occurs sporadically, but more often if I do something heavy with the disk in the background. With the attached C program running in the background, I can reproduce it maybe every 10th run (but note that it varies a lot, and sometimes it can run 100 times without problems). I'm using ext3fs.

This is a minimized version of rpl_insert_id, which fails sporadically on pushbuild.

How to repeat:
======== bug.test ========
connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,);

connection master;

USE test;

CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1);

connection master1;
INSERT INTO t1 VALUES (2);

connection master;
SELECT * FROM t1;

SLEEP 0.1;

SELECT * FROM t1;

DROP TABLE t1;
======== end of bug.test ========

======== begin slow_disk.c ========
#include <stdio.h>
#include <stdlib.h>
#include <time.h>

#define BUF_SIZE     (1024*1024)
#define BUF_PER_FILE (64)
#define NUM_FILES    (6)

long long seed;
int next(void) {
  seed = (seed * 0x5DEECE66DLL + 0xBLL) & ((1LL << 48) - 1);
  return (int)(((unsigned long long)seed) >> (16));
}

void write_buf(FILE *f) {
  static char buf[BUF_SIZE];
  int i;
  for (i=0; i<BUF_SIZE; i+=sizeof(int)) {
    *(int *)(buf+i) = next();
  }
  if (fwrite(buf, 1, BUF_SIZE, f) != BUF_SIZE) {
    printf("Error writing to file");
    exit(1);
  }
}

void write_file(int i) {
  char file_name[10];
  FILE *f;

  sprintf(file_name, "file-%02d", i);
  f = fopen(file_name, "wb");
  for (i=0; i<BUF_PER_FILE; i++)
    write_buf(f);
  fclose(f);
}

int main(int argc, char **argv) {
  int i;

  seed = time(NULL);
  while (1) {
    for (i=0; i<NUM_FILES; i++)
      write_file(i);
    next();
  }
}
======== end slow_disk.c ========
to compile:
$ gcc -O3 -W -Wall -o slow_disk slow_disk.c
$ gcc -v
Using built-in specs.
Target: i486-linux-gnu
Configured with: ../src/configure -v --enable-languages=c,c++,fortran,objc,obj-c++,treelang --prefix=/usr --enable-shared --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --enable-nls --with-gxx-include-dir=/usr/include/c++/4.1.3 --program-suffix=-4.1 --enable-__cxa_atexit --enable-clocale=gnu --enable-libstdcxx-debug --enable-mpfr --enable-checking=release i486-linux-gnu
Thread model: posix
gcc version 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)

Suggested fix:
.

I forgot to say, I'm building with BUILD/compile-pentium-valgrind-max

Reproduced also when built with BUILD/compile-pentium-debug-max

I was unable to repeat this in 4.1 and 5.0 (running the test case 1000 times), but have repeated it in both 5.1 and 6.0.

We had internal discussions about MyISAM durability. The question was if the data reach the disk when the INSERT returns, or if the disk cache might defer the physical write.

This discussion does not have anything to do with the reported problem. The test case does not involve an operating system crash, disk crash, or power outage. As long as the OS keeps running, it will always provide the correct data from the file system buffers and only reads from disk when the buffers have been flushed. But even then the disk will provide the data from its write cache if it is there. Unless the disk firmware is heavily broken.

Here we have the table open throughout the test. Most probable the same table object will be used by the connections alternaltely. The MyISAM index file and data file are open all the time. Provided this is true, the question of flushing to disk or not is totally irrelevant to this case.

There is no parallelity/concurrency in this test case. Each statement starts only after the preceding one finished. There is no obvious reason why a SELECT, following an INSERT could not see the result of the INSERT. Even if the INSERT is processed as a "concurrent INSERT", after it completely terminates, its result must be seen by any statement starting after its termination.

One possible cause of this bug could be that we forget to update the internal statistics at the end of the INSERT statement so that the following SELECT thinks the file ends before the last row. However, this would not qualify for a sporadic effect.

Other possibilities could be bugs in the operating system/file system or disk firmware. The heavy load on the machine might cause the flush of table file buffers between INSERT and SELECT. If the file system reloads old data during an interval, or the disk does not deliver data from its write cache, but reads old data as long as the new block is in the write cache, then there is nothing we can do about it. In this case we won't easily find another machine where it happens at all.

All my guesses are void. It turned out that the server acknowledges the completion of an SQL statement (COM_QUERY) before it releases the table locks acquired by this statement.

This does of course allow for all kinds of non-repeatable problems, including this one.

I conclude this bug is not a MyISAM bug.

We are currently discussing internally if/how to fix it.

On Tue, May 13, 2008 at 07:51:17PM +0200, Sven Sandberg wrote:
> The email thread was inconclusive. I and Andrei have been discussing
> with Kostja today on IRC, and he insists that the missing row is
> expected behavior with MyISAM and @@concurrent_inserts=1. That would
> imply it's a bug in the test case and in the documentation. I'd like
> to have this confirmed from Ingo before we make a decision.

On Tue, May 13, 2008 at 10:55:09PM +0200, Ingo Strüwing wrote:
> I understand that the order of acknowledge to the application and unlock
> of tables improves performance in many environments.
> 
> Care is taken that this does not destabilize transactions.
> 
> The consequence is that non-transactional statements may not be complete
> when they are acknowledged by the server. Non-transactional statements
> do not make any guarantees in that area. So we have to live with the
> situation.
> 
> For test cases and where needed by applications one can either disable
> concurrent inserts or place a "lock barrier" behind the insert or before
> the select: LOCK TABLE t1 WRITE; UNLOCK TABLES;
> 
> It would be nice to document the behavior, but I don't know where to put
> this information.

So, it seems that:

1. It is expected behaviour that MyISAM is not durable and thus this
   is "Not a bug",

2. The LOCK-UNLOCK trick should be documented in the test manual,

3. The LOCK-UNLOCK trick should probably be added as a function
   "flush_myisam_to_disk_to_make_it_durable()" to the test
   framework so that people understand why there can be a strange
   LOCK-UNLOCK in the middle of the a case.  (Alternatively each
   use of the trick should have a comment about it, but it is too 
   easy to forget to comment, so better with the function, in my mind)

If this is not a server bug, then it is a documentation bug. The section about @@concurrent_insert lists possible situations where concurrent_insert=1 changes the behavior of queries. However, that section does not mention the situation when SELECT after INSERT does not see all inserted rows. So it should be added.

We identified unexpected, though not strongly wrong behavior. It could be fixed by a small change in the MySQL code. But this has possible performance implications. A decision from the architects is required. Architects, please see the internal email thread.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

While inserting many rows, I run into the above described behavior every now and then. I was wondering what the status of this issue is. Has this been fixed? Or is there a workaround?

As far as I understand the problem, the server sends OK for the INSERT to the client, before it unlocks the table. The new row(s) may not be "registered" in the table when the SELECT starts in another session.

There is no decision to change this yet.

Possible work-arounds:

If you don't need the synchronization between INSERT and SELECT too often, you could run LOCK TABLE table_name; UNLOCK TABLES; after the insert and only report the INSERT to be complete after UNLOCK.

If you need it often, you could disable concurrent inserts. SET GLOBAL concurrent_insert=0.

Thanks a lot for the status update and the described workaround. Just to be sure: Executing 'LOCK TABLE table_name; UNLOCK TABLES;' after the INSERT has the effect that 'UNLOCK TABLES;' returns only after all the previously inserted rows are 'registered' and therefore available in a SELECT. Correct?

Yes, but please note a small mistake: The statement must be LOCK TABLE table_name WRITE; UNLOCK TABLES;. So it must be a write lock. (see also the comment [16 May 2008 7:10] Lars Thalmann above). Pending inserts need to be visible, before a new write lock can be taken. So it is in fact the LOCK statement, which does the trick, but it is advisable to get rid of the write lock as quickly as possible. Any other statement that takes a write lock should also have the effect, but those do usually modify data.

Bug #45864 was marked as duplicate of this one.

Set back to "Verified" as serious regression bug which has 2 duplicates should not be silently ignored.

Problem is not repeatable with version 5.0 - regression

Problem is not repeatable with concurrent_insert=0.

This is a serious issue that can cause unintended side effects, the early acknowledgment should be an option that applications can use if they know it's safe.

Jess, I agree.

Bug #37521 is the same story.

Closed as a duplicate of Bug#37521. It's the same root issue.

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:20)

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (pib:20)