MySQL Bugs: #60262: client side commit strangeness when autocommit disabled (with innodb)

Bug #60262	client side commit strangeness when autocommit disabled (with innodb)
Submitted:	26 Feb 2011 1:01	Modified:	26 Feb 2011 17:19
Reporter:	Thomas Farthing	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S2 (Serious)
Version:	5.5.9	OS:	Windows (Windows7 on server and client)
Assigned to:		CPU Architecture:	Any
Tags:	autocommit, fetch, innodb, multiple connections, MYSQL_NO_DATA

Description:
Using innodb tables and two connections with autocommit disabled, the query sequence below (how to repeat) results in the second select failing to return results even though the data has been committed correctly.

With autocommit enabled, or if the query sequence is performed on the same connection, or if the first select is not performed, the second select works correctly returning a single row.

How to repeat:
I will follow up with a code sample:

(1) open connection #1 with autocommit disabled

(2) open connection #2 with autocommit disabled

(3) create table_a using connection #1: "create table `table_a` (`id` int unsigned not null auto_increment, `name` varchar(40), primary key(`id`)) engine=innodb"

(4) create child table_b using connection #1: "create table `table_b` (`id` int unsigned not null, `attr` varchar(40) unique, primary key(`id`, `attr`), index(`attr`), foreign key(`id`) references `table_a` (`id`) on delete cascade) engine=innodb"

(5) insert a row into table_a using connection #1 and commit: "insert into table_a (name) values ('name')"

(6) select a row from table_a using connection #2: "select * from table_a where id = 1"

(7) insert a row into table_b using connection #1 and commit: "insert into table_b (id, attr) values (1, 'attr')"

(8) select a row from table_b using connection #2: "select * from table_b where attr='attr'"

Step #8 results in the fetch returning MYSQL_NO_DATA when a single row should be returned.

Breaking the program before step #8 and executing the same query in an external process results in a single row being returned as you would expect.

The steps above will not produce the incorrect behavior if:
- all of the queries are performed on the same connection
- if autocommit is active
- if step #6 is skipped
- if step #6 and step #7 are swapped

Suggested fix:
No suggestions

repro sample

Attachment: main.cpp (text/plain), 4.16 KiB.

Doesn't this manual page, http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html, explain the results you see:

"If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries."

So, your connection #2 sees data as they are at the moment of the first SELECT, that is, with no rows in that other table.

When you open other connection, it is a different transaction that sees data as they are at the moment of the first SELECT.

Wow, it does.  I'm very sorry for the bug submission.