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

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 changedCode 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: 0Code 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: 0Code 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をコピーしました