SQLのfor updateは行レベルでテーブルをロックする トランザクションで有効

SQLのfor updateについてまとめています。実際にロックする様子を解説。

for updateは、行レベルでテーブルをロックするSQL

select文にfor updateをつけて実行すると、更新行のみの行ロックをかけることができます。

mysql> select first_name from employees2 where emp_no="10001" for update;

以下は、MySQLのサンプルデータベースemployeesを使って行ロックをかけるサンプルSQLです。

// テーブル構造をコピー
mysql> create table employees2 like employees;
Query OK, 0 rows affected (0.03 sec)
// データをコピー
mysql> insert into employees2 select * from employees;
Query OK, 300024 rows affected (4.46 sec)
Records: 300024  Duplicates: 0  Warnings: 0
// データがコピーできたことを確認
mysql> select * from employees2 limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)
 
// トランザクション開始
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
// for update指定でselect実行
mysql> select first_name from employees2 where emp_no="10001" for update;
+------------+
| first_name |
+------------+
| Georgi     |
+------------+
1 row in set (0.00 sec)
// トランザクションが閉じていないので、行ロックが発生している

beginでトランザクションを開始したあとに、selectにfor update指定をすることで、行ロックが発生します。

上記のSQLはそのままで、別のセッションを開いて、行ロックが発生していることを確かめてみましょう。

// 通常のselect文は問題なく実行できる
mysql> select first_name from employees2 where emp_no="10001";
+------------+
| first_name |
+------------+
| Georgi     |
+------------+
1 row in set (0.00 sec)
// selectにfor update指定すると、ロック解除待ちになりタイムアウトになるとエラー
mysql> select first_name from employees2 where emp_no="10001" for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 
// ロックしている行とは別の行をfor updateでselectすると問題なく実行できる
mysql> select first_name from employees2 where emp_no="10002" for update;
+------------+
| first_name |
+------------+
| Bezalel    |
+------------+
1 row in set (0.00 sec)

ロックがかかっている行のみ、for updateをつけて実行すると待ちが発生します。（一定時間でタイムアウトになります）なお、単純なselectだと、問題なく実行できました。

select文の条件を変えて、別の行をfor update指定つきでselectすると、実行できました。

for updateは、複数テーブルの値をもとに、矛盾なくSQLを実行したい場合に使います。

一部のデータベースでは、for updateを利用できない

主要データベースのうち、MySQL、Oracle、PostgreSQLはfor updateが使えます。SQL Serverにはfor updateの指定がありません。

SQL Serverでは、from後のテーブル指定に、テーブルヒントとしてrowlockを指定することで行単位のロックをかけることができます。

参考）テーブル ヒント (Transact-SQL) – SQL Server | Microsoft Docs

まとめ

ポテパンダの一言メモ

• selectにfor update指定すると、行ロックをかけることができる
• begin内のトランザクション中でのみ有効
• 別セッション中のSQLで、ロック中の行を更新またはロックしようとすると待ち状態になる