MySQLロック発生の仕組み

## 記事のゴール

ロック知識ゼロから「行ロック・テーブルロック・共有ロック・排他ロック」

の役割を理解できること。

初歩理解を促すことが目的なので、厳密性は割愛。

## ロックとは

**データの整合性を保つ仕組みのこと**

RDBMSは、データアクセスを「トランザクション」という単位で区切る。

複数のトランザクションが同じデータにアクセスした場合、

どのトランザクションがアクセス可能か、待機が必要か、ロックをもとに決定する。

イメージは下の通り

![lock.001.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/31223/724605d7-1ff9-8884-5232-d6ead9c57106.jpeg)

* 複数トランザクションが update アクセスした時
* 先発のトランザクションは「ロック」をし
* 後発のトランザクションは「先発のロック解放」を待つ

これでデータ整合性を保っている。

.......

前置きはここまで、以下から本題。

## A. ロックの粒度

* 行ロック
* テーブルロック

### A1. 行ロック

**行を特定していれば「行がロックされる」**

次の users テーブルより

| id | name  | weight |
| -- | --    | --     |
| 1  | alice | 40     |
| 2  | bob   | 65     |
| 3  | lisa  | 43     |

下の COMMIT していないトランザクション発行中は

```sql
START TRANSACTION;
update users set weight = 67 where id = 3; -- 特定行の更新

-- もしくは
START TRANSACTION;
delete from users where id = 3; -- 特定行の削除
```

**id = 3 の行がロック状態になる。**

id = 3 に向けた更新系のトランザクションは、待ち状態になる。

```sql
-- 別の MySQL プロセスより

START TRANSACTION;
update users set weight = 66 where id = 3;
-- ...
-- ロック解放待ち
-- ...
COMMIT;

START TRANSACTION;
delete from users where id = 3;
-- ...
-- ロック解放待ち
-- ...
COMMIT;
```

### A2. テーブルロック

**テーブル全体の一括操作は「テーブル全体がロックされる」**

次の users テーブルより

| id | name  | weight |
| -- | --    | --     |
| 1  | alice | 40     |
| 2  | bob   | 65     |
| 3  | lisa  | 43     |

下の COMMIT していないトランザクション発行中は

```sql
START TRANSACTION;
UPDATE users SET weight = weight + 10; -- テーブル全体の一括更新

-- もしくは
START TRANSACTION;
DELETE FROM users; -- テーブルの一括物理削除
```

**テーブル全体にロックがかかる**

テーブルに対する更新系のトランザクションは、待ち状態になる。

```sql
-- 別の MySQL プロセスより

START TRANSACTION;
update users set weight = 66 where id IN (1, 2);
-- ...
-- ロック解放待ち
-- ...
COMMIT;

START TRANSACTION;
delete from users where id = 4;
-- ...
-- ロック解放待ち
-- ...
COMMIT;
```

## B. ロックの種類

| .. | 共有ロック | 排他ロック |
| -- | -- | -- |
| 他トランザクションからのアクセス | 読取 - 可能<br />書込 - 不可 | 読取 - 不可<br /> 書込 - 不可 |
| 発生条件 | SELECT の末尾に `FOR SHARE` をつける | SELECT の末尾に `FOR UPDATE` をつける<br />UPDATE / DELETE / ALTER |

**留意点**

表にある「読取」とは

**SELECT 文の末尾に `FOR SHARE` をつける読取**のこと。

末尾に何もつけない単純な読取は対象外(各サンプルを参照)

### B1. 共有ロック

他のトランザクションが

* 読取 - 可能
* 書込 - 不可能

なロックのこと

次の users テーブルより

| id | name  | weight |
| -- | --    | --     |
| 1  | alice | 40     |
| 2  | bob   | 65     |
| 3  | lisa  | 43     |

下の COMMIT していないトランザクション発行中は

```sql
START TRANSACTION;
select * from users where id = 3 FOR SHARE;
```

**id = 3 に共有ロックがかかる**

```sql
-- 別の MySQL プロセスより

START TRANSACTION;
select name from users where id = 3 FOR SHARE;
-- ロックの影響を受けない
COMMIT;

START TRANSACTION;
select * from users where id = 3;
-- ロックの影響を受けない
COMMIT;

START TRANSACTION;
update users set name = 'LISA' where id = 3;
-- ...
-- ロック解放待ち
-- ...
COMMIT;

START TRANSACTION;
delete from users where id = 3;
-- ...
-- ロック解放待ち
-- ...
COMMIT;
```

### B2. 排他ロック

他のトランザクションが

* 読取 - 不可能
* 書込 - 不可能

なロックのこと。

次の users テーブルより

| id | name  | weight |
| -- | --    | --     |
| 1  | alice | 40     |
| 2  | bob   | 65     |
| 3  | lisa  | 43     |

下の COMMIT していないトランザクション発行中は

```sql
START TRANSACTION;
update users SET weight = weight + 10 where id = 3;
```

**id = 3 に対して排他ロックがかかる**

```sql
-- 別の MySQL プロセスより

START TRANSACTION;
select name from users where id = 3 FOR SHARE;
-- ...
-- ロック解放待ち
-- ...
COMMIT;

START TRANSACTION;
update users set weight = 45 where id = 3;
-- ...
-- ロック解放待ち
-- ...
COMMIT;

START TRANSACTION;
delete from users where id = 3;
-- ...
-- ロック解放待ち
-- ...
COMMIT;

START TRANSACTION;
select * from users where id = 3;
-- ロックの影響受けない
COMMIT;
```

# 理解度の確認

次の users テーブルより

| id | name  | weight |
| -- | --    | --     |
| 1  | alice | 40     |
| 2  | bob   | 65     |
| 3  | lisa  | 43     |

COMMIT していないトランザクション TX があるとする

```sql
START TRANSACTION; -- トランザクション TX
update users set weight = 45 where id = 3;
```

**質問A.**

TX において

* 共有ロックと排他ロック、どちらが発生していますか
* 行ロックとテーブルロック、どちらが発生していますか

**質問B.**

TX とは別のトランザクションにおいて

`update users set weight = 42 where id = 3` を実行すると、ロック解放待ちは発生しますか

<details>
  <summary>----------回答。クリックしたら開きます----------</summary>

**回答A.**

> * 共有ロックと排他ロック、どちらが発生していますか

排他ロック

> * 行ロックとテーブルロック、どちらが発生していますか

行ロック

**回答B.**

> * `update users set weight = 42 where id = 3` を実行すると、ロック解放待ちは発生しますか

発生する。なぜならば

* id = 3 の行に対して
* 行ロックかつ排他ロックがかかるから

</details>