Help us understand the problem. What is going on with this article?

MySQL - InnoDBのロック関連まとめ

More than 1 year has passed since last update.

メモ開放。InnoDBの行ロック関連について、それぞれの項目が必ずしも並列関係にあるわけではないが、以下のようにまとめていく。

  • 排他ロックと共有ロック
  • SELECT ~ FOR UPDATE
  • SELECT ~ LOCK IN SHARE MODE

排他ロックと共有ロック

読み取りを許すかどうかの違い。排他ロックは対象行を全てのクエリからロックするため、UPDATEやDELETEなどの更新クエリはもちろん、SELECTなどの読み取りクエリも通さない。共有ロックは更新クエリを通さないが、読み取りクエリは通す。 (追記:排他ロックは分離レベルによってはSELECTを通すとのこと。 公式

排他ロックは全てのクエリを通さず、共有ロックは排他ロックを伴うクエリを通さない、と言い換えたほうがいいかもしれない。

公式では共有ロックは同トランザクション内のselectを許し、排他ロックは同トランザクション内のupdateとdeleteを許すとあるが、共有ロックが同トランザクションのupdateやdeleteを許さないわけではないし、排他ロックが同トランザクションのselectを許さないわけではない。


INSERTは対象行に排他ロックをかけるが、重複キーエラーが起きた時は対象行に共有ロックをかける。それはINSERT IGNOREの場合も同じ。

mysql> insert ignore into products values (15, 'ipos', 23000); #Tx1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from products where product_id = 15 lock in share mode; #Tx1
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
|         15 | ipod         |   900 |
+------------+--------------+-------+
1 row in set (0.00 sec)

mysql> select * from products where product_id = 15 for update;
#待機

既存レコードに共有ロックをかけ、別トランザクションがその行に排他ロックをかけようとすると待機状態になる。この時、最初のトランザクション内で同じレコードに排他ロックをかけようとするとデッドロックになる。これは、別トランザクションでロック待機が存在していると、既に取得している共有ロックを排他ロックにアップグレードできず、待機状態になってしまうためである。これが共有ロックであるならばアップグレードは必要ないので、デッドロックは起きない。

mysql> select * from products where product_id = 15 lock in share mode; #Tx1
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
|         15 | ipod         |   900 |
+------------+--------------+-------+
1 row in set (0.00 sec)
mysql> select * from products where product_id = 15 for update; #Tx2
mysql> select * from products where product_id = 15 for update; #Tx1
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

SELECT ~ FOR UPDATE

排他ロックの一種。取得した値を利用して更新をかける際に使われることを想定しており、全てのクエリを通さない。

既存レコードに排他ロックをかけ合うことはできないが、固有値検索条件として存在しないレコードを指定するとギャップロックになる。ギャップロック同士は競合しないのでselect for updateをかけ合うことができるが、insertの挿入インテンションギャップロックと競合するので、その箇所にinsertをすることは許さない。 参考1 参考2

mysql> select * from products where product_id = 17 for update; #Tx1
Empty set (0.00 sec)
mysql> select * from products where product_id = 17 for update; #Tx2
Empty set (0.00 sec)
mysql> select * from products where product_id = 17 lock in share mode; #Tx1
Empty set (0.00 sec)
mysql> select * from products where product_id = 17 lock in share mode; #Tx2
Empty set (0.00 sec)
mysql> insert into products values (17, 'name', 773463); #Tx1
# 待機状態

SELECT ~ LOCK IN SHARE MODE

共有ロックの一種。排他ロックを伴うクエリを通さないため、UPDATEやDELETEはもちろん、SELECT ~ FOR UPDATEも通さない。SELECT ~ LOCK IN SHARE MODE同士のような、共有ロックを伴うクエリは通す。

mysql> select * from products where product_id = 8 lock in share mode; #Tx1
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
|          8 | thinkpad     |   150 |
+------------+--------------+-------+
1 row in set (0.00 sec)
mysql> select * from products where product_id = 8 for update; #Tx2

共有ロックしたレコードに別トランザクションが共有ロックする場合、ロックは共存する。結果、そのトランザクション外からの排他ロックを伴うクエリは弾かれる

mysql> select * from products where product_id = 8 lock in share mode; #Tx1
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
|          8 | thinkpad     |   150 |
+------------+--------------+-------+
1 row in set (0.00 sec)
mysql> select * from products where product_id = 8 lock in share mode; #Tx2
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
|          8 | thinkpad     |   150 |
+------------+--------------+-------+
1 row in set (0.00 sec)
mysql> select * from products where product_id = 8 for update; #Tx1

ここでTx2が排他ロックをかけた場合、デッドロックになる。
対象レコードが存在しない場合はギャップロックがかかり、ギャップロック同士は競合しないが、insertの挿入インテンションギャップロックと競合するので、insertは通らない。

mysql> select * from products where product_id = 17 for update; #Tx1
Empty set (0.00 sec)
mysql> select * from products where product_id = 17 for update; #Tx2
Empty set (0.00 sec)
mysql> select * from products where product_id = 17 lock in share mode; #Tx1
Empty set (0.00 sec)
mysql> select * from products where product_id = 17 lock in share mode; #Tx2
Empty set (0.00 sec)
mysql> insert into products values (17, 'name', 773463); #Tx1
# 待機状態
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした