Description:
Session that uses READ COMMITED isolation level doesn't see changes made by separate session while IMO it should.
How to repeat:
Use two separate sessions (two mysql cmd line clients for example) and do:
CREATE TABLE `aaa` (
`id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
on session A:
SET AUTOCOMMIT=0;
TRUNCATE aaa;
BEGIN;
SELECT * FROM aaa;
on session B:
SET AUTOCOMMIT=0;
BEGIN;
INSERT INTO aaa (id,name) VALUES (0,'test');
COMMIT;
on session A:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM aaa;
EXAMPLE:
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> TRUNCATE aaa;
Query OK, 0 rows affected (0.01 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM aaa;
Empty set (0.00 sec)
mysql> /* now session B */
mysql2> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql2> /* session B here */
mysql2> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql2> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql2> INSERT INTO aaa (id,name) VALUES (0,'test');
Query OK, 1 row affected (0.00 sec)
mysql2> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql2>
mysql> /* back to session A */
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM aaa;
Empty set (0.00 sec)
mysql> SELECT * FROM aaa;
Empty set (0.00 sec)
mysql> SELECT * FROM aaa;
Empty set (0.01 sec)
mysql> SELECT * FROM aaa;
Empty set (0.00 sec)
mysql> SELECT * FROM aaa;
Empty set (0.00 sec)
mysql> /* Why it doesn't show what was commited in session B ? */
Suggested fix:
Make data readable when READ COMMITTED isolation level is in use.
