整合性をしっかりとらないといけない処理ではトランザクションをかけるのですが、どうもトランザクションのロック待ちでタイムアウトしてしまうことがあるようです。
java.sql.SQLException: Lock wait timeout exceeded; try restarting transactionトランザクションでテーブル全体にロックがかかってしまう
要するに、「トランザクションを実行するためにロックを獲得しなければいけないが、他のコネクションがロックを握っていて、ロックが獲得できない」ということです。
これの根本的な原因は何かと調べますと、InnoDBでトランザクションを使用するときに、行ロックではなく、テーブル全体にロックがかかってしまう場合がある、というところにたどり着きました。
「InnoDBで行ロック/テーブルロックになる条件」を見ながら、少し試してみます。
テーブル作成
以下のようにテストテーブルとテストデータを作りました。
create table lock_test (
id int primary key auto_increment,
name varchar(32) not null,
value int not null
) engine=innoDB default charset=utf8;insert into lock_test
(name, value)
values
('tanaka', 80),
('suzuki', 30),
('sato', 50),
('takahashi', 40);mysql> select * from lock_test;
+----+-----------+-------+
| id | name | value |
+----+-----------+-------+
| 1 | tanaka | 80 |
| 2 | suzuki | 30 |
| 3 | sato | 50 |
| 4 | takahashi | 40 |
+----+-----------+-------+
4 rows in set (0.00 sec)IDと、人物と、なにかしらの値みたいなテーブルです。
他のコネクションが行を更新する間、行ロックがかかる。
2つのコネクションの相互作用の話なので、少しわかりにくいのですが、Connection AとConnection Bにわけて、別のシェルからアクセスしています。
# Connection A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update lock_test set value = value + 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0まず、Connaction Aが、トランザクションを開始し、idが1のレコードを更新します。
# Connection B
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update lock_test set value = value + 10 where id = 1;
# ここで止まる･･･Connection Bが、このレコードを更新しようとすると、行ロックがかかっているので、更新クエリが待ち状態になってしまい、応答が返りません。
# Connection A
mysql> commit;
Query OK, 0 rows affected (0.00 sec)ロックを握っているConnection Aがトランザクションを終了します。
# Connection B
mysql> update lock_test set value = value + 10 where id = 1;
Query OK, 1 row affected (13.17 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)Connection Bがようやくロックを獲得し、更新クエリを進めることができます。
このようにInnoDBではトランザクション中に同一のレコードを更新されないようにロックをかけてくれます。
更新対象の行が異なればロック待ちの必要はない
同一のレコードを、2つのコネクションから同時に更新しようとすると、ロック解除を待つ必要があります。しかし、更新するレコードが異なれば、その心配はありません。
さきほどと同じようにConnection Aでトランザクションを開始して、idが1のレコードの行ロックを取得してから、Connection Bで違うレコードを更新してみます。
# Connection B
mysql> update lock_test set value = value + 10 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0この場合、idが2のレコードはロックされていないので、即座に更新をすることができます。
要注意！テーブル全体をロックしてしまう場合がある。
行でロックをする分には、更新が衝突する確率は低く、ロック待ち状態になることは少ないと思われます。
しかし、クエリの書き方によっては、行ではなくテーブル全体をロックしてしまう場合があるようです。この場合は、どうしても更新が衝突し、ロック待ち状態になってしまいます。
# Connection A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update lock_test set value = value + 10 where name = 'tanaka';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Connection Aが更新している行はさきほどと同じで、idが1のレコードです。しかし、where句の指定をnameで行なっています。このクエリでは、テーブル全体にロックがかかってしまいます。
# Connection B
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update lock_test set value = value + 10 where id = 2;
# ここで止まる･･･実際に、Connection Bから、別のレコードに更新をしようとしても、ロック待ちの状態になってしまいます。
# Connection A
mysql> commit;
Query OK, 0 rows affected (0.00 sec)# Connection B
mysql> update lock_test set value = value + 10 where id = 2;
Query OK, 1 row affected (13.19 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)Connection Aがトランザクションを終了すると、テーブル全体へのロックが解除され、ようやくConnection Bへの更新が実行されます。
テーブルロックさせないためには、インデックスのあるカラムで条件指定する
idカラムで条件指定をした場合は行ロックで、nameカラムで条件指定をした場合はテーブルロックがかかってしまう、ということでトランザクションの更新クエリの条件指定には注意する必要があります。
idカラムとnameカラムの違いは何かというとインデックスの有無で、インデックスでレコードを特定している場合は行ロックとなるとのことです。
トランザクションの分離レベルなどもそうなのですが、整合性を上げすぎると、性能に限界が生じるので、アプリケーションごとに良い落とし所を見つけなくてはいけません。
コメント
[…] […]