MySQL Bugs: #53211: committed data is not visible to other sessions when autocommit is off

Bug #53211	committed data is not visible to other sessions when autocommit is off
Submitted:	27 Apr 2010 16:01	Modified:	28 Apr 2010 14:48
Reporter:	Kamran Saadatjoo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.42	OS:	Other (Fedora 11)
Assigned to:		CPU Architecture:	Any
Tags:	autocommit, commit

Description:
I've set autocommit=false globally.  I open two console sessions. Let's call them A and B.  In session A, I create a table with Engine type InnoDB.  In the same session (A), I insert a row in this table, and issue a COMMIT.

In session B, I select from the table I just created in the other session, and the result is empty, even though I've inserted a row into this table and have committed the insert in the other session.

This is already a problem, but what is even more strange is that the only way session B can see the row inserted is if I disconnect and reconnect session B, or issue a COMMIT in session B (not A, where the INSERT and COMMIT were issued).

This will cause a lot of data integrity problems for me. Please advise.

How to repeat:
1 - Open two console sessions (A and B).

2 - In session A, issue

mysql> create table test1(col1 varchar(10)) Engine=InnoDB;
Query OK, 0 rows affected (0.27 sec)

mysql> insert into test1 values ('testval');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> select * from test1;
+---------+
| col1    |
+---------+
| testval |
+---------+
1 row in set (0.00 sec)

3 - In session B, issue

mysql> select * from test1;
Empty set (0.00 sec)                          <== Before commit

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+---------+                                   <== After commit
| col1    |
+---------+
| testval |
+---------+
1 row in set (0.00 sec)

Thank you for the report.

Did you access table test1 in session B before running SELECT?

Sveta: Thank you for getting back with me. I don't know what you mean by "access"  The SQL statements I provided for this report are exactly as I entered them. In fact, I created table "test1" for this report. 

One other tidbit I should add here.  I cannot reproduce the same problem in the "test" database. But I can re-create it in several of the my own databases.  Thanks again.

Thank you for the feedback.

By "access" I mean if you run any query on the table in session B before session A commits.

Please also indicate which transaction isolation level you use.

OK: I just performed the following (table test1 was dropped prior to what I just did).

Session A: (Table created, a row is inserted, but no COMMIT is issued)

mysql> create table test1(col1 varchar(10)) Engine=InnoDB;
Query OK, 0 rows affected (0.25 sec)

mysql> insert into test1 values ('testval');
Query OK, 1 row affected (0.01 sec)

Session B:

mysql> select * from test1;
Empty set (0.00 sec)

And the tx isolation is REPEATABLE-READ.

Thanks again for looking into this.

Thank you for the feedback.

You wrote:

----<q>----
Session A: (Table created, a row is inserted, but no COMMIT is issued)

mysql> create table test1(col1 varchar(10)) Engine=InnoDB;
Query OK, 0 rows affected (0.25 sec)

mysql> insert into test1 values ('testval');
Query OK, 1 row affected (0.01 sec)
----</q>----

no COMMIT I assume

----<q>----
Session B:

mysql> select * from test1;
Empty set (0.00 sec)
----</q>----

In this case this is not a bug: no COMMIT in session A yet, so session B should not see rows.

So this is not a bug.

Yes, this is a bug. Please take a look at my original description of the problem.  Session B cannot see the inserted row, even when session A issues a COMMIT. You asked me to conduct my last test WITHOUT issuing a COMMIT in session A, so I did. But that is not the problem. The problem is that session B cannot see the row inserted in session A, even AFTER the COMMIT is issued in session A.

The only way session B can see the row inserted by session A is if session B reconnects, or if session B (not A, the originator of the insert) issues a COMMIT.  This, to me, is a bug.  Thanks again for your time.

This is not a bug. Please read about how REPEATABLE READ works:

If you have:

no COMMIT I assume

----<q>----
Session B:

mysql> select * from test1;
Empty set (0.00 sec)
----</q>----

Then

Session A:

COMMIT;

Session B:

still

mysql> select * from test1;
Empty set (0.00 sec)

until you issue commit in session B.

OK: Maybe this is not a bug, and this is working as designed. But no matter what isolation level you set, session B will have to issue a COMMIT after every SELECT (except maybe the first one, depending on the isolation level) before it can see the data inserted and already committed by session A. I've tested this with REPEATABLE READ, READ COMMITTED, and SERIALIZABLE.

The reason I posted this issue in the first place is because I'm in the process of migrating from another DBMS (the name of which I'm not allowed to mention) to MySQL, which may force me to change the way our applications interact with the current DBMS. Thanks again for your time.