Amazon Redshift のテーブルロックを理解する

アプリケーション/データベースエンジニアであれば、一度はDeadLockという忌まわしい出来事に遭遇したことがあることでしょう。Redshiftのロックについてはあまり詳細な仕様が公開されていませんが、ロックやデットロックの動作について検証した結果について報告します。

トランザクション分離レベルは、「SERIALIZABLE」

大量データのデータのロード（LOAD）と参照（SELECT）に最適化されたRDBといえます。そのようなユースケースを想定して、トランザクションを開始（BEGIN）するときのデフォルトのトランザクション隔離レベルが、「SERIALIZABLE」 に設計されているのではないかと考えています。「SERIALIZABLE」 なので、デフォルト動作では、ダーティリード、ファジーリード、ファントムリード は発生しません。

BEGIN

トランザクション分離レベルについて極力分かりやすく解説してみた

Lockの範囲は「テーブルロック」

では、トランザクションを開始し、更新系クエリを実行してテーブルをロックして、実際に動作を確認します。 ロックの状態は、先日追加された 実行中のトランザクションによるデータベースのロック状態を確認する事が出来る新しいビュー SVV_TRANSACTIONS ビューにて、参照します。

ロック前の状態は、以下のとおりです。

cmdb=> SELECT * FROM svv_transactions;
 txn_owner | txn_db |   xid   | pid  |         txn_start          |    lock_mode    | lockable_object_type | relation | granted
--------------------+-------------+---------+------+----------------------------+-----------------+----------------------+----------+---------
 cm_user　 | cmdb　 | 4884161 | 9212 | 2016-04-30 16:25:46.722836 | AccessShareLock | relation             |    16878 | t
 cm_user 　| cmdb　 | 4884161 | 9212 | 2016-04-30 16:25:46.722836 | AccessShareLock | relation             |    51790 | t
 cm_user 　| cmdb 　| 4884161 | 9212 | 2016-04-30 16:25:46.722836 | AccessShareLock | relation             |    51850 | t
 cm_user 　| cmdb 　| 4884161 | 9212 | 2016-04-30 16:25:46.722836 | AccessShareLock | relation             |   386549 | t
 cm_user 　| cmdb 　| 4884161 | 9212 | 2016-04-30 16:25:46.722836 | ExclusiveLock   | transactionid        |          | t
(5 rows)

トランザクションを開始し、検証用テーブル testtab1 に対して、更新系クエリを実行します。

cmdb=> -- 検証用テーブル testtab1
cmdb=> CREATE TABLE testtab1(id int primary key, name varchar(32));
CREATE TABLE
cmdb=> INSERT INTO testtab1 (id, name) VALUES(1, 'foo');
INSERT 0 1
cmdb=> INSERT INTO testtab1 (id, name) VALUES(2, 'bar');
INSERT 0 1

cmdb=> -- トランザクションを開始
cmdb=> BEGIN;
BEGIN
cmdb=> UPDATE testtab1 SET name='fff' WHERE id=1;
UPDATE 1

更に別の接続から、トランザクションを開始し、検証用テーブル testtab1 の別のレコードに対して、更新系クエリを実行します。今度のUPDATEはブロックされたままになります。

cmdb=> BEGIN;
BEGIN
cmdb=> UPDATE testtab1 SET name='bbb' WHERE id=2;

ロック後の状態は、以下のとおりです。

cmdb=> SELECT * FROM svv_transactions;
 txn_owner | txn_db |   xid   | pid  |         txn_start          |    lock_mode    | lockable_object_type | lockable_object_type | relation | granted
--------------------+-------------+---------+-------+----------------------------+-----------------------+----------------------+----------+---------
 cm_user　 | cmdb　 | 4884589 |  9212 | 2016-04-30 17:05:38.082584 | AccessShareLock       | relation             |   389742 | t
 cm_user 　| cmdb 　| 4884589 |  9212 | 2016-04-30 17:05:38.082584 | ShareRowExclusiveLock | relation             |   389742 | t
 cm_user 　| cmdb 　| 4884589 |  9212 | 2016-04-30 17:05:38.082584 | ExclusiveLock         | transactionid        |          | t
 cm_user 　| cmdb　 | 4884618 | 11189 | 2016-04-30 17:08:21.226412 | ShareRowExclusiveLock | relation             |   389742 | f
 cm_user 　| cmdb　 | 4884618 | 11189 | 2016-04-30 17:08:21.226412 | ExclusiveLock         | transactionid        |          | t
 cm_user 　| cmdb 　| 4884626 | 11200 | 2016-04-30 17:08:51.772214 | AccessShareLock       | relation             |    16878 | t
 cm_user 　| cmdb 　| 4884626 | 11200 | 2016-04-30 17:08:51.772214 | AccessShareLock       | relation             |    51790 | t
 cm_user 　| cmdb　 | 4884626 | 11200 | 2016-04-30 17:08:51.772214 | AccessShareLock       | relation             |    51850 | t
 cm_user 　| cmdb 　| 4884626 | 11200 | 2016-04-30 17:08:51.772214 | AccessShareLock       | relation             |   386549 | t
 cm_user 　| cmdb 　| 4884626 | 11200 | 2016-04-30 17:08:51.772214 | ExclusiveLock         | transactionid        |          | t
(10 rows)

ロックの発生が確認できます。"ShareRowExclusiveLock" といかにも「行レベルロック」のように見えますが、別の接続からのUPDATEがブロッキングしていることから、動作としては「テーブルレベルロック」であることが確認できます。「行」から「テーブル」に常にロックエスカレーションしていると思われます。

直列化可能分離（Serializable Isolation）

直列化可能分離では、２つのトランザクションが互いの影響を受けずに同じ結果が得られることが保証されます。 トランザクションT1とトランザクションT2があるとして、 T1 と T2 がこの順序で連続して実行されます T2 と T1 がこの順序で連続して実行されます

但し、TRUNCATE コマンドは、トランザクションの途中で実行すると、現在のトランザクションで行われたすべての未処理変更が自動的にコミットされますのでご注意ください。

トランザクションT1がテーブルをロックすると、トランザクションT2は更新できませんが参照は可能です。なので、データのロードや更新中に参照しても問題ありません。

参考：直列化可能分離

デットロックの発生と回避

デットロックとは複数のトランザクションが互いにテーブルをロック待ちの状態になり、クエリが途中で止まってしまう状態になることです。Redshiftでは、デットロックを自動的に検知して後からロックを引き起こしたクエリーのSQLコマンドをエラーにして、デットロックを回避します。

２つのトランザクションによるデットロックの検証

トランザクションT1とトランザクションT2があるとして、２つのテーブルを互いにロックします。

cmdb=> create table testtab1(id int primary key, name varchar(32));
CREATE TABLE
cmdb=> insert into testtab1 (id, name) values(1, 'foo');
INSERT 0 1
cmdb=>
cmdb=> create table testtab2(id int primary key, name varchar(32));
CREATE TABLE
cmdb=> insert into testtab2 (id, name) values(1, 'foo');
INSERT 0 1

1. T1がTESTTAB1テーブルを更新
2. T2がTESTTAB2テーブルを更新
3. T1がTESTTAB2テーブルを更新
4. T2がTESTTAB1テーブルを更新

１．T1がTESTTAB1テーブルを更新

cmdb=> begin;
BEGIN
cmdb=> update testtab1 set name='fff' where id=1;
UPDATE 1

２．T2がTESTTAB2テーブルを更新

cmdb=> begin;
BEGIN
cmdb=> update testtab2 set name='fff' where id=1;
UPDATE 1

３．T1がTESTTAB2テーブルを更新

cmdb=> update testtab2 set name='fff' where id=1;
UPDATE 1

４．T2がTESTTAB1テーブルを更新

デットロックでエラーとなり、自動的にロールバックします。T2トランザクションがロールバックで終了するので、testtab2テーブルのロックが開放され、「３．T1がTESTTAB2テーブルを更新」のクエリが実行されます。

cmdb=> update testtab1 set name='fff' where id=1;
ERROR:  deadlock detected
DETAIL:  Process 10358 waits for ShareRowExclusiveLock on relation 389746 of database 108258; blocked by process 10347.
Process 10347 waits for ShareRowExclusiveLock on relation 389750 of database 108258; blocked by process 10358.

３つのトランザクションによるデットロックの検証

トランザクションT1とトランザクションT2とトランザクションT3があるとして、３つのテーブルを互いにロックします。

cmdb=> create table testtab1(id int primary key, name varchar(32));
CREATE TABLE
cmdb=> insert into testtab1 (id, name) values(1, 'foo');
INSERT 0 1
cmdb=>
cmdb=> create table testtab2(id int primary key, name varchar(32));
CREATE TABLE
cmdb=> insert into testtab2 (id, name) values(1, 'foo');
INSERT 0 1
cmdb=>
cmdb=> create table testtab3(id int primary key, name varchar(32));
CREATE TABLE
cmdb=> insert into testtab3 (id, name) values(1, 'foo');
INSERT 0 1

1. T1がTESTTAB1テーブルを更新
2. T2がTESTTAB2テーブルを更新
3. T3がTESTTAB3テーブルを更新
4. T1がTESTTAB2テーブルを更新
5. T2がTESTTAB3テーブルを更新
6. T3がTESTTAB1テーブルを更新

６．T3がTESTTAB1テーブルを更新

１〜５は割愛しますが、３つのテーブルの循環参照でもデットロックがエラーとなって回避されるようです。T3トランザクションは自動的にロールバックします。

cmdb=> update testtab1 set name='fff' where id=1;
ERROR:  deadlock detected
DETAIL:  Process 11200 waits for ShareRowExclusiveLock on relation 389762 of database 108258; blocked by process 10347.
Process 10347 waits for ShareRowExclusiveLock on relation 389766 of database 108258; blocked by process 10358.
Process 10358 waits for ShareRowExclusiveLock on relation 389770 of database 108258; blocked by process 11200.

４つ、５つ、６つ、、、といった循環参照については検証しませんが、デットロックを検知するとエラーとなり、回避する仕様ということが確認できました。

まとめ

Redshiftは、トランザクションの分離レベルが「SERIALIZABL」なので、互いのトランザクションに影響を考えなくても済むよう、扱いやすい仕様となっています。更新クエリーによって、参照（SELECT）がブロックされることはありません。デットロックを検知するとエラーとなり、回避する仕様ということが確認できました。

トランザクションの中でテーブルをロックして放置してしまった場合、いつまでもテーブルがロックされたままになることがありえます。本来あってはならないことですが、このような問題は「SELECT * FROM svvtransactions;」を実行することで原因究明が可能です。また、この問題の影響を小さくするため、Redshiftのクエリーのタイムアウト（statementtimeoutパラメタ）を指定しておくことをお勧めします。