RDBMSにおけるLockの挙動

lock_timeout

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

PostgreSQL

Read Committed

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ステータス

pg_locks
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と競合する行を削除する処理で排他ロック)

X Lockの確認

13.2. Transaction Isolation

MySQL

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

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

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

lock_wait_timeout

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>
Transaction Isolation Level

カテゴリー:

最近のコメント

表示できるコメントはありません。