MySQL Bugs: #4343: ResultSet has results from a previous query

Bug #4343	ResultSet has results from a previous query
Submitted:	30 Jun 2004 14:54	Modified:	30 Jun 2004 18:48
Reporter:	Dave Oxley	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	All	OS:	Windows (WinXP)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
Environment:
WinXP SP1, MySQL 4.0.20/4.0.16, Connector/J 3.1.2/3.0.14/2.0.14

Using PreparedStatements a query is executed, then an insert, then the query again. The second query doesn't bring back the new record from the insert, but using a second (unused) connection brings back the correct results.

How to repeat:
I have a test case that I will attach to this bug.

The test case

Attachment: mysql-test.zip (application/x-zip-compressed, text), 4.44 KiB.

The output from the test case is:

select name from testtable;
Original

insert into testtable values ('new');

insert into testtable values ('new1');

Query with used connection
select name from testtable;
Original

Query with unused connection
select name from testtable;
new
new1
Original

delete from testtable where name='new';

delete from testtable where name='new1';

select name from testtable;
Original

The expected output is:
select name from testtable;
Original

insert into testtable values ('new');

insert into testtable values ('new1');

Query with used connection
select name from testtable;
new
new1
Original

Query with unused connection
select name from testtable;
new
new1
Original

delete from testtable where name='new';

delete from testtable where name='new1';

select name from testtable;
Original

I've found a solution to this. Doing a rollback on the connection after the query makes the correct results come back for the next query. Surely this shouldn't be necessary though?

This is expected behavior for a transaction isolation level of REPEATABLE READ with autocommit disabled.  You are in a transaction, so until you rollback/commit your SELECT will remain consistent.

http://dev.mysql.com/doc/mysql/en/InnoDB_consistent_read.html