MySQL Bugs: #15582: Inserted data not found after commit until later

Bug #15582	Inserted data not found after commit until later
Submitted:	8 Dec 2005 11:27	Modified:	8 Jun 2009 17:17
Reporter:	Mattias Jiderhamn	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.15, 5.0.16	OS:	Windows (Windows, RedHat Linux)
Assigned to:		CPU Architecture:	Any

Description:
After updating from 4.1 to 5.0, we experience now and then that after new data has been inserted and the transaction commited, the data cannot be found until after a short delay.

To be more exact. We have a Java web application that insert data into InnoDB tables via JDBC. When the user has pressed "Save" and the data has been stored the (JTA) transaction is commited and a redirect is sent to the browser, so the user can view the changes. That is, a new HTTP request is being made and a new (or possibly the same) connection is being fetched from the connection pool. Although when trying to read the data, the record cannot be found.
When retrying (or looking directly in the database) the data is found.

This has happened both with auto increment primary keys and "manual" keys.

It seems somehow that there is some kind of delay, so that the data sometimes has not yet been properly flushed/written to the database before we try to read it.

How to repeat:
I have not yet found a way to repeat this. I have tried inserting and immediately reading 10000 times in a loop - both using the same and a new connection - without being able to reproduce the problem.
Possibly I inserted to little data, but I have seen the problem with as little as 2 tables with 1 and 3 columns respectivly - although other tables may have had rows read locked.

Suggested fix:
This obviously needs to work as in earlier versions. When a transaction is commited, the data should immediately be visible.

All in all it seems to risky for us to upgrade the production servers to 5.0.

Thank you for a problem report. 

We need a repeatable test case, and the fact that even you can not repeat the problem easily makes it hard to create one. But let us try. First of all, can you, please, provide a CREATE TABLE statement for the (simple) table that demonstrated the behaviour you described. Then I need a transaction you performed (what exact INSERT statement to execute). And, finally, because you use Java, please, inform, what version of MySQL Connector/J do you use. Are you sure transaction is really committed by your Java code? Can you check it using SHOW INNODB STATUS or SHOW PROCESSLIST?

I understand it is almost impossible to fix an error you are not able to reproduce. I was just hoping that maybe, maybe there was some known architectural change which the error could be related to.

Smallest example is tables created as such

CREATE TABLE pricatgroup (
  pricatgroup_id VARCHAR(6) NOT NULL,
  PRIMARY KEY  (pricatgroup_id)
) ENGINE=InnoDB;

CREATE TABLE pricatgroup_text (
  pricatgroup_id VARCHAR(6) NOT NULL,
  language_id char(3) NOT NULL,
  pricatgroup_text TEXT NOT NULL,
  PRIMARY KEY  (pricatgroup_id,language_id)
) ENGINE=InnoDB;

Data inserted using prepared statements and for values example as such
INSERT INTO pricatgroup (pricatgroup_id) VALUES ('FOO');
INSERT INTO pricatgroup_text (pricatgroup_id,language_id,pricatgroup_text) VALUES ('FOO','EN','BarEN');
INSERT INTO pricatgroup_text (pricatgroup_id,language_id,pricatgroup_text) VALUES ('FOO','SV','BarSV');

Isolation level SERIALIZABLE was used, and it is possible implicit read locks were aquired for for rows in other tables.

Version of Connector/J: 3.1.8.

I am positive the transaction was in fact commited, since the data was present when later trying to reload the web page trying to view it and/or looking at the data through MySQL-Front.

I will put some more work into reproducing this myself. Please let me know if there is any more information I can provide.

Please, keep trying to create a repeatable test case. Send the results of the SHOW PROCESSLIST and SHOW INNODB STATUS commands executed from the mysql command line client when you will see the same problem next time.

You may also try to update your Connector/J. Current version is 3.1.12.

I am now 99% sure the problem lies not with MySQL. Issue can be closed.

Sorry for commenting on such historical ticket, however, Mattias, what was the cause of this in your case ?We've got the similar behavior recently.

Thanks

In our case, it was the application server flushing a HTTP redirect (to a page reading the data) before the transaction was commited.