Well, let me say if that happens it’s because there is a logic mistake in your application. But you need to know and understand what happens in MySQL to be able to avoid the problem. 

In short, the WHY of this article is to inform you about possible pitfalls and how to prevent them from causing you damage.

Let us start by having a short introduction to what Repeatable reads are about. Given I am extremely lazy, I am going to use (a lot) existing documentation from the MySQL documentation.

Transaction isolation is one of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing …

In relation to these two posts from Justin Swanhart and Anders Karlsson about transaction isolation levels, I thought it was interesting to do a little survey to get an idea of the most commonly used isolation levels.

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll. Related Posts :

• [UPDATED] Why you should go to Percona Live (London) ?
• Fatal timeout !
• …

Clients often ask what the differences are between the various InnoDB isolation levels, or what ACID means. Here are some simple explanations for those that have not yet read the manual and committed it to memory.

READ UNCOMMITTED
Every select operates without locks so you don’t get consistency and might have dirt reads, which are potentially earlier versions of data. So, no ACID support here.

READ COMMITTED
Has consistent reads without locks. Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.

REPEATABLE READ
The InnoDB default isolation level for ACID compliance. All reads within the same transaction will be consistent between each other – ie, the C in ACID. All writes will be durable, etc etc.

SERIALIZABLE
Same as REPEATABLE READ but MySQL converts regular select …