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で、ロック中の行を更新またはロックしようとすると待ち状態になる
