MySQL locking for the busy web developer

We web developers are mostly careless about database locks. It is something we throw in as a last measure, when we don’t want anyone to touch the record we are about to modify. I will try to set few things straight about MySQL locks in this article and feel free to add your own experience in comments:

Always acquire lock within a transaction

This one is simple really, because MySQL provides no way of explicitly releasing the lock and a transaction commit or rollback releases the lock, we should acquire lock within a transaction.
But also, when autocommit is enabled (which is usually the default) acquiring a lock outside the transaction has no effect whatsoever. Because a commit is made at the end of each statement and acquired lock immediately released.

An UPDATE implicitly locks the row

Rails in many ways has spoiled us. It is not very uncommon to see the code:

articles = Article.where(user_id: user.id).lock(true)
articles.update_attributes(project_id: user.project_id)

If you can forego callbacks that fire as a result of update_attributes it can be better rewritten as:

Article.where(user_id: user.id).update_all(project_id:     user.project_id)

The affected rows will be automatically locked as a result of update query and released at the end of transaction.

How much gets locked in SELECT … FOR UPDATE?

To understand this one, we need to understand that MySQL has three types of record level locks.

Record Locks

Lock on affected records themselves. Although these locks in truth are always acquired on index records, even if table has no index.

Next key lock

To prevent phantom records, MySQL uses Next Key locks which consist of index lock on matching records and gap that precedes the index.
Perhaps this MySQL document explains it best - Avoiding the Phantom Problem Using Next-Key Locking

Gap Lock

This is a lock on gap between index records, or a lock on the gap before the first or after the last index record.

We can’t do justice to the topic of different record level locks here but the long and short of this is that MySQL locks much more than just matching records to prevent phantom records and ensure REPEATABLE_READ isolation.
If your query does not use a unique index, MySQL will likely use a “Gap Lock” to prevent new rows. Without an index it will lock all gaps and hence block all INSERTs.
Let’s consider following query and assume there is no index on column name:

SELECT * FROM users WHERE name = 'steve' FOR UPDATE;

If you run this query within a transaction, it will block all INSERT or SELECT ... FOR UPDATE statements in any other transactions. Try inserting another row in this table from another MySQL session before the query has been committed and see how it blocks.
As explained earlier, all index gaps are locked in the above example, so no INSERTs can happen. What is more, without a unique index all records are locked as well (not just matching ones). If it didn’t lock all rows, another connection could update the name field of another row and then this query would return different results on different executions and thereby violate REPEATABLE_READ isolation.
Now let’s add a unique index on the name field and try the same INSERT statement again and it should work. That’s because with an index, MySQL will lock only the affected index record and use record locks.
Before we wrap up, let’s consider following query:

SELECT * FROM users WHERE id > 10 FOR UPDATE;

Without a unique index on id, this query will lock the table for all INSERTs and UPDATEs.
Let’s say we add a unique index on id in which case only the matching rows (index records in fact) will be locked and SELECT.. FOR UPDATE will work for all other rows. However, certain index record gaps will be locked as well. For example, in this case you will not be able to insert a row with id > 10 while the above statement is running. This is where “next key” locks kicks in.
Things get slightly more complicated when you join tables and acquire locks. Even with unique indexes, record locks may not work and hence you should always be careful when joining tables and acquiring locks.