記事のゴール

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

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

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

ロックとは

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

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

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

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

イメージは下の通り

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

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

.......

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

A. ロックの粒度

行ロック
テーブルロック

A1. 行ロック

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

次の users テーブルより

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

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

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

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

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

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

-- 別の 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 していないトランザクション発行中は

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

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

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

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

-- 別の MySQL プロセスより

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

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

B. ロックの種類

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

留意点

表にある「読取」とは

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

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

B1. 共有ロック

他のトランザクションが

読取 - 可能
書込 - 不可能

なロックのこと

次の users テーブルより

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

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

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

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

-- 別の 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 していないトランザクション発行中は

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

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

-- 別の 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 があるとする

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

質問A.

TX において

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

質問B.

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

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

----------回答。クリックしたら開きます----------

回答A.

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

排他ロック

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

行ロック

回答B.

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

発生する。なぜならば

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