Intro

In high concurrency environments, the database write operation will easily suffer due to data inconsistency issue. A simple way to resolve the issue is to use an exclusive lock. In this article, we are going to talk about the usage of SELECT ... FOR UPDATE statement which is an exlusive lock in MySQL InnoDB.

A Common Race Condition Issue

Suppose we have a table employee with the following schema.

mysql> desc employee;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(32)         | YES  | MUL | NULL    |                |
| salary | int(8)              | NO   |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT * From employee;
+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
|  1 | Alex  |   1300 |
|  2 | Amy   |   3000 |
|  3 | Bruce |   5500 |
|  4 | Jason |   NULL |
+----+-------+--------+
4 rows in set (0.00 sec)

There are two transaction,

// Trasaction A to increase salary
mysql>> set autocommit = 0;
mysql>> SELECT * From employee WHERE id = 1;
// a increase logic for salary
mysql>> UPDATE employee SET salary = new_salary WHERE id=1;
mysql>> commit;

// Trasaction B to decrease salary
mysql>> set autocommit = 0;
mysql>> SELECT * From employee WHERE id = 1;
// another calculation logic for salary
mysql>> UPDATE employee SET salary = new_salary_2 WHERE id=1;
mysql>> commit;

If transaction B execute the SELECT statement before Transaction A execute the UPDATE statement. The data read by Transaction B will still be the old data which is the salary without increased. To prevent the issue happened, we want to a lock to prevent someone read the data while the write Operation are still going.

SELECT … FOR UPDATE Statement

A SELECT … FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows. If a row is locked by a transaction, a SELECT … FOR UPDATE transaction that requests the same locked row must wait until the blocking transaction releases the row lock. This behavior prevents transactions from updating or deleting rows that are queried for updates by other transactions. However, waiting for a row lock to be released is not necessary if you want the query to return immediately when a requested row is locked, or if excluding locked rows from the result set is acceptable.

The lock can be a row-level lock or a table-level lock depends on the query.

Row-level v.s. Table-level

According to the Mysql InnoDB document, the conclusion is as belows

Case 1: row is not existed                        -> No lock
Case 2: query with index and hit the index        -> Row is locked
Case 3: query with index but index isn't hit      -> Table is locked
Case 4: query without index                       -> Table is locked

1. Row is not existed:

SELECT * FROM employee WHERE id=-1 FOR UPDATE;

2. Query with index and hit the index:

# statement 1
mysql>> SELECT * From employee WHERE id = 1 FOR UPDATE;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | Alex |   1300 |
+----+------+--------+
1 row in set (0.00 sec)
// wait for a while to release
mysql>> commit;

------------------------------------------------------------
// row is blocked
mysql>> SELECT * From employee WHERE id = 1 FOR UPDATE;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | Alex |   1300 |
+----+------+--------+
1 row in set (6.04 sec)

------------------------------------------------------------
// other row is not blocked
mysql> SELECT * From employee WHERE id = 2 FOR UPDATE;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  2 | Amy  |   3000 |
+----+------+--------+
1 row in set (0.00 sec)

3. Query with index but index isn’t hit

// Query with index but index isn't hit
mysql> SELECT * FROM employee WHERE salary is null FOR UPDATE;
+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
|  4 | Jason |   NULL |
+----+-------+--------+
1 row in set (0.00 sec)
// wait for a while to release
mysql>> commit;
------------------------------------------------------------
// row is blocked
mysql> SELECT * From employee WHERE id = 4 FOR UPDATE;
+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
|  4 | Jason |   NULL |
+----+-------+--------+
1 row in set (4.30 sec)
------------------------------------------------------------
// other row is blocked
mysql> SELECT * From employee WHERE id = 2 FOR UPDATE;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  2 | Amy  |   3000 |
+----+------+--------+
1 row in set (3.48 sec)

4. Query without index

// Query without index
mysql> SELECT * From employee WHERE salary=1300 FOR UPDATE;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | Alex |   1300 |
+----+------+--------+
1 row in set (0.00 sec)
// wait for a while to release
mysql>> commit;
------------------------------------------------------------
// row is blocked
mysql> SELECT * From employee WHERE id = 1 FOR UPDATE;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | Alex |   1300 |
+----+------+--------+
1 row in set (5.20 sec)
------------------------------------------------------------
// other row is blocked
mysql> SELECT * From employee WHERE id = 2 FOR UPDATE;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  2 | Amy  |   3000 |
+----+------+--------+
1 row in set (5.63 sec)