MySQL Bugs: #6161: read-committed should be the default tx isolation level

Bug #6161	read-committed should be the default tx isolation level
Submitted:	19 Oct 2004 17:56	Modified:	20 Oct 2004 15:28
Reporter:	Andres Salomon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.0.21	OS:	Linux (Debian GNU/Linux)
Assigned to:		CPU Architecture:	Any

Description:
I initially reported this here: http://bugs.debian.org/277138

Basically, when a server thread reads (in a loop) from an InnoDB table, and another thread updates the table, the other server thread never sees the update.  Any new threads that connect see the updated table, but the initial thread has the old values cached somewhere.  The cache should be invalidated.  This may be due to the query I'm using having a left join.

How to repeat:
Here are logs (note the two different threads):

041018 14:53:24     352 Query       SELECT * FROM domains AS dom LEFT
JOIN domain_access ON dom.domain_id=domain_access.domain_id  WHERE
user_id='1' 041018 14:54:02     353 Query       INSERT INTO domains
(name, owner) VALUES ('test.com', '1')
041018 14:54:12     353 Query       INSERT INTO `domain_access` VALUES
('1', 1, 'subwww', 'edit')
041018 14:54:26     353 Query       LAST_INSERT_ID()
041018 14:54:30     353 Query       SELECT LAST_INSERT_ID()
041018 14:54:47     353 Query       COMMIT
041018 14:54:54     353 Query       SELECT * FROM domain_access LEFT
JOIN domains ON domains.domain_id=domain_access.domain_id WHERE
domain_access.domain_id=1 AND domain_access.user_id='1' 
041018 14:54:57     352 Query       SELECT * FROM domain_access LEFT
JOIN domains ON domains.domain_id=domain_access.domain_id WHERE
domain_access.domain_id=1 AND domain_access.user_id='1'
041018 14:55:01     352 Quit
041018 14:55:02     353 Quit

Check the debian bug report for additional info, such as a ruby script that will reproduce it, and the table schema I'm using.

Hi,

Thank you for your bug report!

Are you referring to the mysql query cache?  From what I see it looks like you are just seeing
the effects of the default transaction isolation level which is repeatable read.  This provides a 
consistent, non-lock reads for selects.  This versioning allows you to work if the same version of 
a table throughout the duration of your transaction.  The reason you see it w/o auto commit on is 
that a new transaction, and thus a new "snap-shot", is taken with each statement.  

If you do not like this, you can change the tx_isolation variable to 'read-committed'.

Best Regards,

That doesn't make sense.  Why wouldn't you want the latest data in the table?  When an insert is committed, the cache should be invalidated for all threads reading from that table (or however MySQL happens to do that; on a per-row level, or whatever).  Returning *stale* information results in unexpected results; for example, two processes using the database to communicate (process A inserts data into a table, process B reads the data) are essentially broken by the query-cache.  I do not believe it should be the default behavior, and still seems like a bug.

Transactions have so called ACID properties. Where A is for atomicity, C for consistency,
I for isolation and D for durability. Your issue is about isolation. Isolation
means that even though transactions execute concurrently, it appears to each
transaction, T, that others executed either before T or after T, but not both.
This means that query should see only those rows which were committed before
the query started. This also means that transactions may not communicate.
What you are seeing is expected behaviour of transactions.

Hi,

We appreciate your thoughts.  Do you not feel that this should be the default transaction isolation 
level?  Innodb tables support all of the ANSI standard transaction isolation levels.  This is one of 
them, consistent read.  This is also the default transaction isolation level in DB 2 and PostgreSQL.
If you would like to use read committed which is the default in MS SQL and Oracle then you can 
simple set tx_isolation = 'READ-COMMITTED'

Best Regards

Thanks for clearing this up.  I was expecting read-committed behavior (other longtime MyISAM users who I talked to about this expected the same).  If Postgres and DB 2 default to repeatable-read, I guess it makes sense to keep MySQL in line w/ that.

The default isolation level in PostgreSQL is actually "read committed". (And FWIW, the SQL spec mandates that "serializable" ought to be the default isolation level.)

The PostgreSQL docs would appear to agree with Neil: http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html

Given that information, I would recommend defaulting to read-committed.

I changed the synopsis as this has turned into a feature request to change the default
isolation level.