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:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.9 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[19 Jan 2006 15:14] Maxim M
Database version is "Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686)"
Isolation level - read-committed
query_cache_size = 32M

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)
[19 Jan 2006 15:19] Maxim M
Java jdbc sample to recreate this bug

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

[30 Jan 2006 16:32] Valeriy Kravchuk
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.