MySQL Bugs: #16636: Read-commited behavior is wrong when using query cache

Bug #16636	Read-commited behavior is wrong when using query cache
Submitted:	19 Jan 2006 15:14	Modified:	30 Jan 2006 16:32
Reporter:	Maxim M	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.9	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Configuration:
==========
Database version is "Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686)"
Isolation level - read-committed
query_cache_size = 32M

Description
========
I have 2 transactions running. Both transactions runs at the same time.
One of the transactions reads a record. The second transaction update this record and commit. Immediately after the second was committed, if the first transaction selects this record it gets an old record

When removing query cache (setting size =0), it is o.k.

How to repeat:
Both transaction must run together, because this bug is a synchronization bug.
I have 2 transactions running. Both transactions starts together. 
1. begin transaction 1
2. begin transaction 2
3. Transaction 1 selects a record, for example rec1 
4. Transaction 2  updates this record (rec1)
5. Transaction 2 - commit
6. Transaction 1 selects this record and get an old data (before transaction 1 commit)

Java jdbc sample to recreate this bug

Attachment: Bug16636.java (application/octet-stream, text), 5.63 KiB.

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour you described, even with query cache enabled, using 2 mysql client sessions with READ COMMITTED isolation levels on 5.0.x. I can try with 4.1.x, but please, check that you do not use default REPEATABLE READ isolation level in your tests. Read http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html for details.