Concurrency and Transaction Isolation in MySQL

Often, when a site allows users to add and update content, you run into concurrency issues. We recently ran into a problem where double clickers were causing two transactions to fire. For most of the system, this is fine, but many parts of our system use a "get_or_create" method to create records. We were getting several instances of duplicate records where two concurrent requests were calling get_or_create at the same time and both creating records. We first tried to solve this problem with a simple "select ... for update", but it turns out that in MySQL, transactions operate in an isolation mode that gives each transaction a snapshot of data at the point in time the transaction began. So, while the second transaction waited for the first to complete, the second transaction still had a stale copy of the table in question. The solution to this problem is to modify the transaction isolation level in MySQL with the following settings in my.cnf: [mysqld] transaction-isolation = READ-COMMITTED You can read more about the transaction isolation levels at: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html