トランザクションと行ロック

2つ以上の処理で排他制御したい

ユーザがアクセスしたとき、DBテーブルのstateカラムが1だったら「何かの処理」をして、2に更新する処理があります。

バッチ処理では、ユーザを横断して、この処理をします。

ところが、ユーザアクセスとバッチ処理を同時に処理すると「何かの処理」を2回してしまいます。「何かの処理」がメール送信や、在庫減少するような処理だと、まずいことになります。

ユーザアクセスバッチ処理
1id=1 AND state=1を検索id=1 AND state=1を検索
2id=1で何か処理id=1で何か処理
3state=99に更新state=99に更新

そうではなく、どちらか一つしか処理できないようにしたいんですね。

ユーザアクセスバッチ処理
1id=1 AND state=1を検索(待ち)
2id=1で何か処理
3id=1をstate=99に更新
4id=1 AND state=1を検索、ヒットしない

トランザクションと行ロックを使って、これを実現します。

実験用のデータベーステーブルの準備

ローカルのmysqlサーバに適当なデータベース(仮にfoo)を作成して、barテーブルを作成します。

ターミナルを開いて、mysqlに接続します。

$ mysql -u taro -p -h localhost Enter password:
Code language: Bash (bash)

fooデータベースを新規作成します。

mysql> CREATE DATABASE foo; Query OK, 1 row affected (0.01 sec) mysql> USE foo; Database changed
Code language: SQL (Structured Query Language) (sql)

barテーブルを新規作成します。

mysql> CREATE TABLE `bar` ( -> `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, -> `state` int(11) NOT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.02 sec)
Code language: SQL (Structured Query Language) (sql)

barテーブルに2行のデータを挿入します。

mysql> INSERT INTO `bar` (`id`, `state`) VALUES -> (1, 1), -> (2, 1); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
Code language: SQL (Structured Query Language) (sql)

barテーブルを表示して確認します。

mysql> SELECT * FROM bar; +----+-------+ | id | state | +----+-------+ | 1 | 1 | | 2 | 1 | +----+-------+ 2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

ここまでのターミナルを「ターミナル1」とします。

別のターミナルを開き「ターミナル2」とします。ターミナル2もfooデータベースに接続します。

$ mysql -u taro -p -h localhost foo Enter password: mysql> SELECT * FROM bar; +----+-------+ | id | state | +----+-------+ | 1 | 1 | | 2 | 1 | +----+-------+ 2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

トランザクションのみ

ターミナル1

トランザクションをstartします。

mysql> begin; Query OK, 0 rows affected (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

selectします。2件とも、state=1です。

mysql> select * from bar; +----+-------+ | id | state | +----+-------+ | 1 | 1 | | 2 | 1 | +----+-------+ 2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

ターミナル2

トランザクションをstartします。

mysql> begin; Query OK, 0 rows affected (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

selectします。2件とも、state=1です。

mysql> select * from bar; +----+-------+ | id | state | +----+-------+ | 1 | 1 | | 2 | 1 | +----+-------+ 2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

ターミナル1

id=1 を state=2 に更新します。

mysql> update bar set state=2 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Code language: SQL (Structured Query Language) (sql)

selectします。id=1は、state=2に変化しました。

mysql> select * from bar; +----+-------+ | id | state | +----+-------+ | 1 | 2 | | 2 | 1 | +----+-------+ 2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

ターミナル2

selectします。2件とも、state=1です。ターミナル1のトランザクションはcommitしていないので、変更内容が反映されていないからですね。

mysql> select * from bar; +----+-------+ | id | state | +----+-------+ | 1 | 1 | | 2 | 1 | +----+-------+ 2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

id=1 を state=2 に更新します。すると、返ってきません。ターミナル1の変更内容がcommitされるまで待たされます。

mysql> update bar set state=2 where id=1;
Code language: SQL (Structured Query Language) (sql)

ターミナル1

commitします。

mysql> commit; Query OK, 0 rows affected (0.01 sec)
Code language: SQL (Structured Query Language) (sql)

ターミナル2

ターミナル1でcommitしたと同時に、ターミナル2はupdateから返ってきました。

mysql> update bar set state=2 where id=1; --- ここで待たされていてた Query OK, 0 rows affected (32.27 sec) Rows matched: 1 Changed: 0 Warnings: 0
Code language: SQL (Structured Query Language) (sql)

すでにターミナル1で state=2 に変更済みなので、ターミナル2では「Changed: 0」と表示されました。

commitして、トランザクションを終了します。

mysql> commit; Query OK, 0 rows affected (0.01 sec)
Code language: SQL (Structured Query Language) (sql)

書き込みでは排他ロックがかかりましたが、もっと早い段階で、排他ロックをしなければいけません。

トランザクションと行ロック

ターミナル1

state=1 に戻します。

mysql> update bar set state=1; Query OK, 0 rows affected (0.00 sec) Rows matched: 2 Changed: 1 Warnings: 0
Code language: JavaScript (javascript)

トランザクションをstartします。

mysql> begin; Query OK, 0 rows affected (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

selectします。

mysql> select * from bar; +----+-------+ | id | state | +----+-------+ | 1 | 1 | | 2 | 1 | +----+-------+ 2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

id=1を行ロックします。

mysql> select * from bar where id=1 for update; +----+-------+ | id | state | +----+-------+ | 1 | 1 | +----+-------+ 1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

ターミナル2

トランザクションをstartします。

mysql> begin; Query OK, 0 rows affected (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

id=1 を行ロックします。すると、返ってきません。ターミナル1がid=1を行ロックしているので、ターミナル1がcommitするまで待たされます。

mysql> select * from bar where id=1 for update;
Code language: SQL (Structured Query Language) (sql)

ターミナル1

id=1 を state=2 に更新します。

mysql> update bar set state=2 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Code language: SQL (Structured Query Language) (sql)

selectします。id=1は、state=2に変化しました。

mysql> select * from bar; +----+-------+ | id | state | +----+-------+ | 1 | 2 | | 2 | 1 | +----+-------+ 2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

commitします。

mysql> commit; Query OK, 0 rows affected (0.01 sec)
Code language: SQL (Structured Query Language) (sql)

ターミナル2

ターミナル1でcommitしたと同時に、ターミナル2はselect for updateから返ってきました。

mysql> select * from bar where id=1 for update; --- ここで待ち状態だった +----+-------+ | id | state | +----+-------+ | 1 | 2 | +----+-------+ 1 row in set (16.27 sec)
Code language: SQL (Structured Query Language) (sql)

すでにstate=2になっているので「何か処理」をする必要はないと判別できます。

rollbackして、トランザクションを終了します。

mysql> rollback; Query OK, 0 rows affected (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

ターミナル1でid=1を行ロック、ターミナル2でid=2を行ロック

ターミナル1でid=1を行ロックして、ターミナル2でid=2を行ロックすることができます。

ターミナル1

mysql> begin; mysql> select * from bar where id=1 for update; +----+-------+ | id | state | +----+-------+ | 1 | 1 | +----+-------+ 1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

ターミナル2

mysql> begin; mysql> select * from bar where id=2 for update; +----+-------+ | id | state | +----+-------+ | 2 | 1 | +----+-------+ 1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

ターミナル1、ターミナル2ともに、commitしておきます。

where句でstateを含めて行ロック

行ロックするときのwhere句は、idだけでなく、id=1 AND state=1 のように指定することができます。

ターミナル1

id=1がstate=1であれば、行ロックします。

mysql> begin; mysql> select * from bar where id=1 AND state=1 for update; +----+-------+ | id | state | +----+-------+ | 1 | 1 | +----+-------+ 1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

id=1がstate=2にupdateして、

mysql> update bar set state=2 where id=1; mysql> select * from bar; +----+-------+ | id | state | +----+-------+ | 1 | 2 | | 2 | 1 | +----+-------+ 2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

id=1 AND state=1を行ロックしようとすると、行ロック対象がないので、行ロックしません。

mysql> begin; mysql> select * from bar where id=1 AND state=1 for update; Empty set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

ターミナル1、ターミナル2ともに、commitしておきます。

インデックスをはっていないカラムで行ロックすると

インデックスをはっていないカラムで行ロックすると、テーブル全体にロックがかかってしまいます。

ターミナル1

stateカラムにはインデックスはありません。id=1をstate=1、id=2をstate=2にしておきます。

mysql> update bar set state=1 where id=1; mysql> update bar set state=2 where id=2; mysql> select * from bar; +----+-------+ | id | state | +----+-------+ | 1 | 1 | | 2 | 2 | +----+-------+ 2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

state=1で行ロックをかけます。id=1だけがselect結果に表示されましたが...

mysql> begin; mysql> select * from bar where state=1 for update; +----+-------+ | id | state | +----+-------+ | 1 | 1 | +----+-------+ 1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

ターミナル2

state=1や、id=1で返ってこないのはもちろん、

mysql> begin; mysql> select * from bar where state=1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from bar where id=1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Code language: SQL (Structured Query Language) (sql)

state=2や、id=2でも返ってきません。ターミナル1がテーブル全体をロックしているからです。

mysql> begin; mysql> select * from bar where state=2 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from bar where id=2 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Code language: SQL (Structured Query Language) (sql)

ターミナル1、ターミナル2ともに、commitしておきます。

タイムアウト秒数の設定

タイムアウト秒数はデフォルトで30秒です。

DB接続セッションごとに、タイムアウト秒数を設定することができます。

mysql> set innodb_lock_wait_timeout = 1; mysql> set innodb_lock_wait_timeout = 0;
Code language: SQL (Structured Query Language) (sql)

MySQL 5.6.45では、0秒を設定してもすぐにタイムアウトせず、1秒を設定したときと同じぐらいでした。

まとめ

トランザクションとselect for updateで、行ロックすることができました。

ユーザアクセス処理では

// SQLとごちゃまぜの架空の言語 transaction start select * from id=ユーザID AND state=1 for update; if (該当レコードがあれば) { 何か処理 commit } else { rollback }
Code language: plaintext (plaintext)

バッチ処理では、ユーザを横断して検索するので、

// SQLとごちゃまぜの架空の言語 select * from id=ユーザID AND state=1; foreach (ユーザID) { transaction start select * from id=ユーザID AND state=1 for update; if (該当レコードがあれば) { 何か処理 commit } else { rollback } }
Code language: plaintext (plaintext)

とすることで、ユーザアクセス処理とバッチ処理の排他制御ができます。

タイトルとURLをコピーしました