RDBMSにおけるLockの挙動

by afaik

投稿日: 1月 3, 2022

RDBMSにおける排他ロックはデータの整合性を保ってくれるので非常に重要ですが、経年と共にデータ量が増加してくると色々な問題の原因になることも増えてくるので、出来るだけ競合が発生しないようにインデックスを適切に利用してピンポイントでデータを処理したり不要なデータは削除する事が必要になってきます。ここでは、MySQLとPostgreSQLにおけるロックの処理を振り返ってみます。

PostgreSQL

Transaction1　(T1のデータを削除）

POC=# begin transaction;
BEGIN
POC=*# delete from T1;
DELETE 10
POC=*# alter sequence m_id restart with 1;
ALTER SEQUENCE
POC=*# select pg_backend_pid();
 pg_backend_pid
----------------
            705
(1 row)

POC=*# select pg_backend_pid();
 pg_backend_pid
----------------
            705
(1 row)

Transaction 2 (上記のトランザクション中にT1のデータを参照≒問題なし）

POC=# begin transaction;
BEGIN
POC=*# select * from T1;
 id |            note
----+-----------------------------
  1 | This is Sequence Lock Check
  2 | This is Sequence Lock Check
  3 | This is Sequence Lock Check
  4 | This is Sequence Lock Check
  5 | This is Sequence Lock Check
  6 | This is Sequence Lock Check
  7 | This is Sequence Lock Check
  8 | This is Sequence Lock Check
  9 | This is Sequence Lock Check
 10 | This is Sequence Lock Check
(10 rows)

POC=*# select pg_backend_pid();
 pg_backend_pid
----------------
            713
(1 row)

上記の状態におけるLOCKステータス

POC=# select lock.locktype,class.relname,lock.pid,lock.mode from pg_locks lock
left outer join pg_stat_activity act on lock.pid = act.pid left outer join
pg_class class on lock.relation = class.oid where not lock.granted
order by lock.pid;
 locktype | relname | pid | mode
----------+---------+-----+------
(0 rows)

POC=# \x
Expanded display is on.
POC=# select * from pg_stat_activity where pid = 723;
-[ RECORD 1 ]----+------------------------------------------------
datid            | 16384
datname          | POC
pid              | 723
leader_pid       |
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 172.18.0.1
client_hostname  |
client_port      | 43204
backend_start    | 2021-12-27 09:34:49.047315+09
xact_start       | 2021-12-27 10:38:29.375363+09
query_start      | 2021-12-27 10:38:29.375363+09
state_change     | 2021-12-27 10:38:29.375366+09
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 797
query_id         |
query            | select * from pg_stat_activity where pid = 723;
backend_type     | client backend

POC=# select * from pg_stat_activity where pid = 705;
-[ RECORD 1 ]----+------------------------------
datid            | 16384
datname          | POC
pid              | 705
leader_pid       |
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 172.18.0.1
client_hostname  |
client_port      | 43184
backend_start    | 2021-12-27 09:27:55.812804+09
xact_start       | 2021-12-27 10:28:08.770287+09
query_start      | 2021-12-27 10:34:08.814365+09
state_change     | 2021-12-27 10:34:08.814484+09
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 797
backend_xmin     |
query_id         |
query            | select pg_backend_pid();
backend_type     | client backend

POC=# select * from pg_stat_activity where pid = 713;
-[ RECORD 1 ]----+------------------------------
datid            | 16384
datname          | POC
pid              | 713
leader_pid       |
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 172.23.172.157
client_hostname  |
client_port      | 35026
backend_start    | 2021-12-27 09:31:38.401269+09
xact_start       | 2021-12-27 10:28:19.051446+09
query_start      | 2021-12-27 10:37:52.141659+09
state_change     | 2021-12-27 10:37:52.141878+09
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      |
backend_xmin     |
query_id         |
query            | select * from T1;
backend_type     | client backend

トランザクション中にSELECT,INSERT,DELETEを実行しロックステータスを確認

Transaction1

POC=# begin transaction;
BEGIN
POC=*# delete from T1;
DELETE 1
POC=#

Transaction2 (SELECT、INSERTは問題なく、Transaction1と競合する行を削除する処理で排他ロック）

Truncateが含まれる場合の留意事項

TRUNCATEは操作対象の各テーブルに対するACCESS EXCLUSIVEロックを獲得します。これは、この他のそのテーブルに対する同時操作をすべてブロックします。 RESTART IDENTITYが指定された場合、初期化対象のシーケンスがあると、それは同様に排他ロックされます。テーブルへの同時アクセスが必要ならば、代わりに DELETEコマンドを使用しなければなりません。
Truncate

以下の様な順番で処理する場合、Transaction1に時間がかかる場合2も3も待ちになります。

Transaction1:　Begin Transaction; Insert into trains処理
Transaction2: Truncate table trains; (以下の画像では16分待ちになっている事が確認出来ます)
Transaction3: select * from trains; (Truncateにより16分待ちになっている)

Lock確認用SQL

granted：ロックが保持されている場合は真(T)、ロックが待ち状態の場合は偽(F)

\pset pager off

SELECT
pg_stat_activity.pid,
pg_locks.granted,
pg_class.relname AS "Table"
, CASE WHEN pg_locks.granted = 't' THEN 'Lock' ELSE 'Waiting for Lock' END AS "Lock Status"
, pg_locks.mode AS "Lock Level"
, pg_stat_activity.state AS "Transacton Status"
, left(pg_stat_activity.query,40) AS "Last Query"
, pg_stat_activity.query_start AS "Last Query Started Time"
, CASE WHEN pg_stat_activity.state = 'active' THEN current_timestamp - pg_stat_activity.query_start END AS "Duration"
FROM
pg_locks INNER JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
INNER JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_locks.locktype = 'relation';

AccessShareLockを除外して見やすくする場合

SELECT
pg_stat_activity.pid,
pg_locks.granted,
pg_class.relname AS "Table"
, CASE WHEN pg_locks.granted = 't' THEN 'Lock' ELSE 'Waiting for Lock' END AS "Lock Status"
, pg_locks.mode AS "Lock Level"
, pg_stat_activity.state AS "Transacton Status"
, left(pg_stat_activity.query,40) AS "Last Query"
, pg_stat_activity.query_start AS "Last Query Started Time"
, CASE WHEN pg_stat_activity.state = 'active' THEN current_timestamp - pg_stat_activity.query_start END AS "Duration"
FROM
pg_locks INNER JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
INNER JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_locks.locktype = 'relation' and  pg_locks.mode <> 'AccessShareLock';

13.2. Transaction Isolation

51.73. pg_locks

備考

以下の様にINSERTのトランザクションがTRUNCATEを待たせている状況でpg_cancel_backend()を実行してもINSERT処理はキャンセル出来ませんでした。pg_terminate_backend()は実行できましたが、kill -9と同等の処理になるのでリカバリー処理が入る事を考え,pg_terminate_backend()は利用しませんでした。TRUNCATE処理に関しては、pg_cancel_backend()でキャンセルする事は出来ました。以下の様に、ACTIVEになっているトランザクションのみしかCANCEL出来ないのかもしれません。

Transacton Status       | active

以下の様に、先にTRUNCATEをTRUNCATIONで囲って実行し、その後にINSERTした場合も上記と同じで、TRUNCATE処理はCANCEL出来ませんでした。PostgreSQLは他のRDBMSと異なり、TRUNCATEもROLLBACK出来るのですが、ここら辺の挙動はもう少し確認した方が良さそうですね。

LOCKの依存関係

参照：13.3. 明示的ロック

MySQL

MySQLのDefaultのトランザクション分離レベルはREPEATABLE-READなのでPostgreSQLのREAD-COMMITTEDと比べると若干トランザクション分離レベルが厳しく設定されています。勿論、変更する事は可能です。InnoDB Clusterの構成を組む場合は、READ-COMMITTEDで設定します。

REPEATABLE-READの場合には、ギャップロックやネクストキーロックが発生するので留意が必要です。ネクストキーロックでは、テーブルにある値で、明示的にロックを取得しようとした範囲の次の値までの範囲に対してロックを取得する挙動の事を指します。

Transaction1

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from T1;
Query OK, 2 rows affected (0.00 sec)

mysql>

Transaction2 (対象テーブルへのInsert処理もロックになってタイムアウト）

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T1;
+----+----------------------------------+------+
| id | note                             | who  |
+----+----------------------------------+------+
|  1 | This is data 1                   | NULL |
|  2 | This data is inserted during DDL | NULL |
+----+----------------------------------+------+
2 rows in set (0.00 sec)

mysql> insert into T1(note) values('insert during transaction');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql>

PostgreSQLはDefaultでロックタイムアウトが０で無制限に設定されていますが、MySQLに関してはDefaultで50秒が設定されています。その為、他のトランザクションと競合するとロックタイムアウトが発生して待たされているトラクションは上記のようにエラー1205になります。勿論、それぞれメリット・デメリットがあるので、それぞれの環境に応じて適切に選択してください。

Transaction1 (分離レベルをPostgreSQLと同じにして処理）

mysql> SET TRANSACTION ISOLATION LEVEL  READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from T1;
Query OK, 2 rows affected (0.00 sec)

mysql>

Transaction2 (INSERT処理はロックされず削除可能、タイムアウトは変更していないので発生）


mysql> SET TRANSACTION ISOLATION LEVEL  READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into T1(note) values('insert during transaction');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T1;
+----+----------------------------------+------+
| id | note                             | who  |
+----+----------------------------------+------+
|  1 | This is data 1                   | NULL |
|  2 | This data is inserted during DDL | NULL |
|  4 | insert during transaction        | NULL |
+----+----------------------------------+------+
3 rows in set (0.00 sec)

mysql> delete from T1 where id = 4;
Query OK, 1 row affected (0.00 sec)

mysql> delete from T1 where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

備考：分離レベルに関しては以下の方法でも確認可能です。

参照：

15.7.1 InnoDB ロック

15.7.2.1 トランザクション分離レベル

LOCKの依存関係

参照：15.7.1 InnoDB ロック

※　MySQLはStorage Engine (InnoDB, MyISAM, Archive, Memory等）によってもロックの粒度が異なります。また、Defaultでは、Repeatable Read がトランザクション分離レベルになっている為、ギャップロック等にも留意が必要です。PostgreSQLやOracleと同じ分離レベルにするには、READ COMMITTEDに設定してあげると良いでしょう。

※　ロックは、ロック待ちしている処理が取得しようとしているロックによっても、後続の処理がロックエスカレーションされる事にも留意しておきましょう。

カテゴリー:

RDBMS Tech

タグ:

Lock MySQL PostgreSQL ロック