Bug #43722 Rows inserted into one transaction are not accessible in another

• Submitted: 18 Mar 2009 8:02
• Modified: 18 Mar 2009 9:10
• Reporter: Alexandr Nikulin
• Status: Not a Bug
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: Ver 14.12 Distrib 5.0.54
• OS: Linux (Ubuntu)
• CPU Architecture: Any
• Tags: insert, transaction

[18 Mar 2009 8:02] Alexandr Nikulin

Description:
Strange bug, see the example

How to repeat:
1. open two mysql clients.
2. prepare test database in any clients:
   create database bug_test;
   use bug_test;
   create table table1 (id integer)ENGINE = InnoDB;
   insert into table1(id)values(1);
   create table table2 (id integer, note varchar(32))ENGINE = InnoDB;
   create table table3 (id integer)ENGINE = InnoDB;
3. The sequence of queries:
   client1: use bug_test;begin; --OK
            SELECT id FROM table1 WHERE id = 1  FOR UPDATE; --OK
            INSERT INTO table2(note) VALUES (now()); --OK
   client2: use bug_test;begin; --OK
            SELECT id FROM table3 WHERE id = 1; --OK (ATTENTION !!!)
            SELECT id FROM table1 WHERE id = 1  FOR UPDATE; --OK
   client1: commit; --OK
   client2: SELECT * FROM table2; --Empty set (!!!)

Suggested fix:
I think, the last result is incorrect and must return 1 row that was inserted by client1. The main bug is query 'SELECT id FROM table3 WHERE id = 1; --OK (ATTENTION !!!)', if this query is absent the last result is not empty !

[18 Mar 2009 9:10] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is same transaction isolation level issue. See bug #43211 for explanation.