ファイヤープロジェクト
トランザクションと隔離レベルとロック
2003-12-28T16:00+09:00   matsu
PostgreSQLでのトランザクションについて調査してみた.さらにトランザクションの並列実行時の問題とそれに対処するためのトランザクション隔離レベルとロックについても調査した.
あるトランザクションの範囲,すなわちそのトランザクションの開始から終了までをトランザクションブロックと呼ぶ.で,PostgreSQLでは明示しなければ1SQL文で1トランザクションが完結する(※).PostgreSQLでは,トランザクションブロックの開始と終了を指定するSQL文がある.トランザクションブロックを開始するには,
BEGIN;
を発行する.SQL92ではトランザクションの開始は暗黙,すなわち連鎖モードなのでBEGINはない.BEGINの後にWORKまたはTRANSACTIONを記述してもよい(意味は同じ).そしてUPDATEやINSERTなどのいくつかのSQL文を発行し,結果を実際に反映したければ,
END;
または
COMMIT WORK;
を発行する.これでBEGINしてからのSQL文の結果をDBに反映し,トランザクションブロックを終了することができる.両者は同じ意味だが,後者はSQL92の書式らしい.さらにWORKは省略可能である.BEGINしてからいくつかのSQL文を発行し,途中で失敗等して結果を破棄したい場合は
ABORT;
または
ROLLBACK WORK;
を発行する.これでBEGINしてからのSQL文の結果をDBに反映せずに破棄し,トランザクションブロックを終了することができる.両者は同じ意味だが,後者はSQL92の書式らしい.さらにWORKは省略可能である.
※ これを非連鎖モードと呼ぶ.自動コミットとも呼ばれるらしい.逆にトランザクションの開始を明示しなくても,COMMITしない限りトランザクションが完結しないモードを連鎖モードと呼ぶ.PostgreSQL7.4対応のpsqlでは自動コミットモードのon,offを切替えることができるようだ.
以下の例は思わずresult_linpackの全レコードをDELETEしてしまったが,ROLLBACKで復旧できたというものである.
benchmark=> BEGIN;
BEGIN
benchmark=> SELECT count(*) FROM result_linpack;
 count 
-------
    56
(1 row)

benchmark=> DELETE FROM result_linpack ;
DELETE 56
benchmark=> SELECT count(*) FROM result_linpack;
 count 
-------
     0
(1 row)

benchmark=> ROLLBACK ;
ROLLBACK
benchmark=> SELECT count(*) FROM result_linpack;
 count 
-------
    56
(1 row)
DELETE後ROLLBACK前にresult_linpackのレコード数は0だが,ROLLBACK後のレコード数はもとのままである.トランザクションはアボートされる場合がある.アボートされた場合は,COMMITあるいはROLLBACKするまで一切のSQL文を受け付けなくなる.以下はトランザクションブロックで過ったSQL文を発行したためにアボートされたという例である.
benchmark=> BEGIN ;
BEGIN
benchmark=> SELECT count(*) FROM result_linpack;
 count 
-------
    56
(1 row)

benchmark=> DELETE FROM result_linpack ;
DELETE 56
benchmark=> hoge;
ERROR:  parser: parse error at or near "hoge"
benchmark=> SELECT count(*) FROM result_linpack;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
benchmark=> ROLLBACK ;
ROLLBACK
benchmark=> SELECT count(*) FROM result_linpack;
 count 
-------
    56
(1 row)
hogeというデタラメなSQL文を発行してERRORが出力された.明示されていないが,このときトランザクションはアボートされている.実際次のSQL文は「アボートしてるから無視するよ」というメッセージとともに無視される.こうなるとトランザクションブロックを閉じるしかない.以下はトランザクションがうまくいったので,COMMITした場合の例である.
benchmark=> BEGIN;
BEGIN
benchmark=> SELECT * FROM cluster;
 id | name | cpunum | cpuclock | memory |   network   
----+------+--------+----------+--------+-------------
  3 | foo  |        |          |    256 | 
  4 | var  |    512 |        3 |   2048 | 
  2 | fuga |    256 |        1 |        | 1000BASE-TX
  1 | hoge |    256 |        1 |   1024 | 1000BASE-TX
(4 rows)

benchmark=> INSERT INTO cluster(id,name)
benchmark-> VALUES (5, 'foovar');
INSERT 25970 1
benchmark=> SELECT * FROM cluster;
 id |  name  | cpunum | cpuclock | memory |   network   
----+--------+--------+----------+--------+-------------
  3 | foo    |        |          |    256 | 
  4 | var    |    512 |        3 |   2048 | 
  2 | fuga   |    256 |        1 |        | 1000BASE-TX
  1 | hoge   |    256 |        1 |   1024 | 1000BASE-TX
  5 | foovar |        |          |        | 
(5 rows)

benchmark=> COMMIT ;
COMMIT
benchmark=> SELECT * FROM cluster;
 id |  name  | cpunum | cpuclock | memory |   network   
----+--------+--------+----------+--------+-------------
  3 | foo    |        |          |    256 | 
  4 | var    |    512 |        3 |   2048 | 
  2 | fuga   |    256 |        1 |        | 1000BASE-TX
  1 | hoge   |    256 |        1 |   1024 | 1000BASE-TX
  5 | foovar |        |          |        | 
(5 rows)
DMBSではACID特性が要求される.
Atomicity(原子性)
トランザクションブロック内の処理は「すべて実行される」か「すべて実行されない」かのいずれかである.
Consistency(整合性)
DBに整合性がとれているならば,トランザクション処理後も整合性がとれている.
Isolation(隔離性)
複数のトランザクションを同時実行した場合の結果と逐次実行した場合の結果は同じである.
Durability(耐久性)
COMMITされたデータは障害などで消滅しない.
PostgreSQLでは原子性はBEGIN,COMMIT,ROLLBACKで実現されている.Consistencyはテーブルの定義次第で保証される.Durabilityは別頁にて別途記述する.ここではIsolationの問題に触れる.複数のトランザクションが同時に走った場合,以下の整合問題がある.
Dirty read
以下の順番でイベントが発生し,トランザクションBが最終的にCOMMITされないデータを読み込んでしまう問題.
  1. トランザクションAがUPDATE
  2. トランザクションBで該当レコード(値はトランザクションAによる変更後の値)を読みだす.
  3. トランザクションAがROLLBACK
Non-repeatable read
以下の順でイベントが発生した場合,トランザクション内で同じ値であるはずのデータが変化してしまう問題.
  1. トランザクションAでレコードR1をSELECT
  2. トランザクションBでR1をUPDATEしてCOMMIT
  3. 再びトランザクションAでレコードR1をSELECT
トランザクションAが一回目のSELECTで読み込んだ値は二回目のSELECTでは読み込むことができない(読み込んだデータはトランザクションBによって更新された値).
Phantom
トランザクション開始後に他のトランザクションでINSERT,COMMITしたレコードが見えてしまう問題.
SQL92ではこれらの問題を防ぐ4つの隔離レベルを定義している.
READ UNCOMMITTED
対処なし.
READ COMMITTED
コミットされたデータのみを読み込む.
REPEATABLE READ
トランザクションブロック内で他トランザクションの更新の影響を受けない.
SERIALIZABLE
トランザクションブロック内で他トランザクションの挿入の影響を受けない.
三つの整合問題と隔離レベルの対応表を以下に示す.
Dirty readNon-repeatable readPhantom
READ UNCOMMITTEDありありあり
READ COMMITTED ありあり
REPEATABLE READ あり
SERIALIZABLE
READ COMMITTEDはPostgreSQLのトランザクションにおいてデフォルトの隔離レベルである.Dirty readが発生しないことを確認してみる.
benchmark=> BEGIN;
BEGIN
benchmark=> SELECT * FROM cluster;
 id |  name  | cpunum | cpuclock | memory |   network   
----+--------+--------+----------+--------+-------------
  3 | foo    |        |          |    256 | 
  4 | var    |    512 |        3 |   2048 | 
  2 | fuga   |    256 |        1 |        | 1000BASE-TX
  5 | foovar |        |          |        | 
  1 | hogee  |    256 |        1 |   1024 | 1000BASE-TX
(5 rows)

benchmark=> UPDATE cluster
benchmark-> SET name = 'hoge'
benchmark-> WHERE name = 'hogee';
UPDATE 1
benchmark=> SELECT * FROM cluster;
 id |  name  | cpunum | cpuclock | memory |   network   
----+--------+--------+----------+--------+-------------
  3 | foo    |        |          |    256 | 
  4 | var    |    512 |        3 |   2048 | 
  2 | fuga   |    256 |        1 |        | 1000BASE-TX
  5 | foovar |        |          |        | 
  1 | hoge   |    256 |        1 |   1024 | 1000BASE-TX
(5 rows)
今,トランザクションブロックは終了していない.この状態で別トランザクションで以下を実行した.
benchmark=> SELECT * FROM cluster;
 id |  name  | cpunum | cpuclock | memory |   network   
----+--------+--------+----------+--------+-------------
  3 | foo    |        |          |    256 | 
  4 | var    |    512 |        3 |   2048 | 
  2 | fuga   |    256 |        1 |        | 1000BASE-TX
  5 | foovar |        |          |        | 
  1 | hogee  |    256 |        1 |   1024 | 1000BASE-TX
(5 rows)
最初のトランザクションはCOMMITされていないので,最初のトランザクションのUPDATEの結果は見えない.ここで最初のトランザクションでCOMMITした後,SELECTしてみた.
benchmark=> SELECT * FROM cluster;
 id |  name  | cpunum | cpuclock | memory |   network   
----+--------+--------+----------+--------+-------------
  3 | foo    |        |          |    256 | 
  4 | var    |    512 |        3 |   2048 | 
  2 | fuga   |    256 |        1 |        | 1000BASE-TX
  5 | foovar |        |          |        | 
  1 | hoge   |    256 |        1 |   1024 | 1000BASE-TX
(5 rows)
最初のトランザクションの結果が見えている.
デフォルトのREAD COMMITTEDでは防げない問題を確認してみる.まずNon-repeatable read.トランザクションTaを実行する.
benchmark=> BEGIN ;
BEGIN
benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |   sum   |  avg   
-------+---------+--------
    56 | 399.644 | 7.1365
(1 row)
Taはまだトランザクションブロックを終了していない.ここで別のトランザクションTbを実行する.
benchmark=> DELETE FROM result_linpack
benchmark-> WHERE gflops = (    
benchmark(> SELECT min(gflops)
benchmark(> FROM result_linpack);
DELETE 1
TbはCOMMITした.ここで先程の終了していないトランザクションTaで再び同じSELECTを実行する.
benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |   sum   |       avg        
-------+---------+------------------
    55 | 396.402 | 7.20730909090909
(1 row)
同じTa内の同じSQL文で結果がかわってしまった.隔離レベルREAD COMMITTEDではCOMMITあるいはROLLBACKによって確定していない,いわば「不安定なデータ」を読み込む(Dirty read)ことを防ぐのみである.DELETE後COMMITしてしまった別トランザクションからは「隔離」されていない.同様にINSERT後COMMITしてしまった別トランザクションから「隔離」されてもいないので,Phantomの問題が生じる.再びTaを開始する.
benchmark=> BEGIN ;
BEGIN
benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |   sum   |       avg        
-------+---------+------------------
    55 | 396.402 | 7.20730909090909
(1 row)
ここで別トランザクションTbにてINSERTする.
benchmark=> INSERT INTO result_linpack (n, nb, cluster_id, gflops)
benchmark-> VALUES (10000, 200, 1, 30);
INSERT 25971 1
ここで先程の終了していないトランザクションTaで以下を実行する.
benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |   sum   |       avg        
-------+---------+------------------
    56 | 426.402 | 7.61432142857143
(1 row)

benchmark=> SELECT * FROM result_linpack  
benchmark-> WHERE gflops = 30;
   n   | nb  | cluster_id | gflops | exec_date 
-------+-----+------------+--------+-----------
 10000 | 200 |          1 |     30 | 
(1 row)
やはり同じTa内で結果がかわってしまった.Ta開始時には存在していない筈のレコードが見えてしまっている.これがPhantomである.隔離レベルをSERIALIZABLEに設定することで,Non-repeatable readとPhantomの問題を防ぐことができる.隔離レベルは以下で設定する.
benchmark=> \h SET TRANSACTION
Command:     SET TRANSACTION
Description: set the characteristics of the current transaction
Syntax:
SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
    { READ COMMITTED | SERIALIZABLE }
前者(SET TRANSACTION...)は隔離レベルの設定はBEGINの直後に実行する必要があり,その有効範囲はそれを実行したトランザクション内のみである.後者(SET SESSION...)はセッション全体に対して隔離レベルを設定するものである.では再びTaを開始する.
benchmark=> BEGIN ;
BEGIN
benchmark=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET VARIABLE

benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |   sum   |       avg        
-------+---------+------------------
    56 | 426.402 | 7.61432142857143
(1 row)
このTaを終了していない状態で,別トランザクションTbを実行する.
benchmark=> DELETE FROM result_linpack
benchmark-> WHERE  (n, nb, cluster_id, gflops) = (10000, 200, 1, 30);
DELETE 1
さらにTaにて.
benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |   sum   |       avg        
-------+---------+------------------
    56 | 426.402 | 7.61432142857143
(1 row)
TaにはCOMMITされたトランザクションTbのDELETE結果が見えていない.まだTaを終了させずにTc.
benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |   sum   |       avg        
-------+---------+------------------
    55 | 396.402 | 7.20730909090909
(1 row)
Tbのコミット後に開始されたトランザクションTcではTbのDELETE結果は見えている.これはつまり,TaのためにTbの変更前のデータを残しつつTcのためにTcの変更後のデータも存在していることになる.この用にPostgreSQLは複数の時点のデータを保持する.この仕組みをMVCC(Multi Version Concurrency Control)と呼ぶ.この仕組みにより,Taの終了をまたずにTb,Tcは実行を実行でき,並列性を高めている(※).
※ MVCCによりホットバックアップも可能となっている.
隔離レベルは隔離性の高いSERIALIZABLEをいつも選択すべきだろうか.否.SERIALIZABLEにはその隔離性故の問題がある.先のSERIALIZABLEの例では,一方が参照のみ,もう一方が更新のみであった.問題は両方に更新があった場合に生じる.まずTa
benchmark=> BEGIN ;
BEGIN
benchmark=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET VARIABLE
benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |   sum   |       avg        
-------+---------+------------------
    55 | 396.402 | 7.20730909090909
(1 row)
ここでTbを開始.
benchmark=> BEGIN ;
BEGIN
benchmark=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET VARIABLE
benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |   sum   |       avg        
-------+---------+------------------
    55 | 396.402 | 7.20730909090909
(1 row)
ここでTaにUPDATEをかける.
benchmark=> UPDATE result_linpack 
benchmark-> SET gflops = gflops + 100;
UPDATE 55
benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |   sum    |       avg        
-------+----------+------------------
    55 | 5896.402 | 107.207309090909
(1 row)
Tbでも更新をかける.
benchmark=> UPDATE result_linpack 
benchmark-> SET gflops = gflops + 100;
するとTbでは結果が出力されない.Taによってロックされているからである.ここでTaをROLLBACKするとTbの更新が正常に完了する.これは問題ない.問題はTaでCOMMITした場合である.TaでCOMMITすると,停止していたTbの更新処理はエラーで終了する.
ERROR:  Can't serialize access due to concurrent update
こうなると,Tbはアボート状態になり,トランザクションを終了するしかない.SERIALIZABLEはその名のとおり複数のトランザクションを同時に処理した結果が,逐次で処理した場合の結果と同じであることを保証する隔離レベルである.そのためにSERIALIZABLEなトランザクションは,BEGINした時点でのデータの参照を保証され,他のトランザクションによる更新の影響を受けない.これによりRepeatable READが保証され,Phantomの発生を防ぐことができる.だが,裏を返すと先行するはずのトランザクションでの更新結果を参照できないことも意味する.Taでgflops = gflops + 100してCOMMITしたら,後続のTbのgflops = gflops + 100は,Taの結果に対して100足すという処理でなければならない.すなわちTaの更新結果を参照する必要があるのだが,SERIALIZABLEではそれができない.このように問題を整理すると,SERIALIZABLEでもSELECTのみのトランザクションでは問題が発生せず,更新があっても先行するトランザクションの終了をまち,それがROLLBACKしたなら更新が無事終了する理由が分かる.
前節のSERIALIZABLEの問題に対処するには,隔離レベルをREAD COMMITTEDに下げ,ロックを使用する方法がある.DBのロック方式には以下の二つがある.
  • 行単位にロックをかける行ロック
  • テーブル単位にロックをかけるかテーブルロック
多くの並列処理と同様,より小さい単位である行ロックの方が衝突の確立が低く並列性が高い.PostgreSQLでは行ロックが採用されている.PostgreSQLでは,SQL文を発行した際に自動的にかけれられる暗黙的なロックと,ユーザが明示的に指定できる明示的なロックがある.前者の例を示す.READ COMMITTEDな二つのトランザクションTa,Tbを考える.まずTa.
benchmark=> BEGIN ;
BEGIN
benchmark=> UPDATE result_linpack 
benchmark-> SET gflops = gflops + 100;
UPDATE 55
つづいてTb.
benchmark=> BEGIN ;
BEGIN
benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |   sum   |       avg        
-------+---------+------------------
    55 | 396.402 | 7.20730909090909
(1 row)

benchmark=> UPDATE result_linpack 
benchmark-> SET gflops = gflops + 100;
Taにて更新対象のレコードが暗黙的にロックされているので,Tbの更新は待たされる.SERIALIZABLEと異なり,READ COMMITTEDではCOMMITした他トランザクションの結果を参照できるので,TaをCOMMITすることができる.以下,TaをCOMMIT後のTb.
benchmark=> UPDATE result_linpack 
benchmark-> SET gflops = gflops + 100;
UPDATE 55
benchmark=> SELECT count(gflops), sum(gflops), avg(gflops)
benchmark-> FROM result_linpack;
 count |    sum    |       avg        
-------+-----------+------------------
    55 | 11396.402 | 207.207309090909
(1 row)
平均値が200増加している.Taによるgflops = gflops + 100の結果に対してTbがgflops = gflops + 100しているからである.
READ COMMITTEDでの暗黙的なロックでは以下のような問題がある.
  1. TaでSELECT文を発行.レコードRaを選択.
  2. TbでRaを削除してCOMMIT.
  3. TaでRaを更新.
TbはCOMMITしているので,その結果がTaには見える.したがって最後のTaによるRaはもう存在していないことになるので,更新は行なわれない(UPDATE 0と出力される).仮にTaがSERIALIZABLEであればこのような問題は生じないが,そもそもの問題としてTaに先行するCOMMITしていないトランザクションで更新があると,ロックの話題に至った際の問題が生じる.そこでTaのSELECT文でRaを明示的にロックする.これにはSELECT文の最後に
FOR UPDATE
と記述する.ではSELECT FOR UPDATEを使用する例を示す.まずTaでSELECT FOR UPDATE.
benchmark=> BEGIN ;
BEGIN
benchmark=> SELECT * FROM result_linpack 
benchmark-> WHERE gflops = (
benchmark(> SELECT min(gflops) FROM result_linpack )
benchmark-> FOR UPDATE;
   n   | nb  | cluster_id | gflops | exec_date 
-------+-----+------------+--------+-----------
 12000 | 120 |          1 | 103.64 | 
(1 row)
次にTbで同レコードをDELETEする.
benchmark=> BEGIN ;
BEGIN
benchmark=> DELETE FROM result_linpack 
benchmark-> WHERE gflops = (
benchmark(> SELECT min(gflops) FROM result_linpack );
TaでロックされているレコードをDELETEしようとしているので,Tbはブロックされる.ここでTaを終了するとTbが再開された.
DELETE 1
benchmark=> SELECT min(gflops) FROM result_linpack;
   min   
---------
 103.904
(1 row)
Taが終了してロックが開放されたので,その行を削除できた.
SELECT FOR UPDATEで選択した行をロックすることで,その選択結果を他のトランザクションから削除されることなく更新することを保証するができた.だがSELECT FOR UPDATEでは逆のパターンに対応できない.
  1. Taで属性A1が最小値のレコードを選択.
  2. Tbで属性A1がさらに小さいレコードを挿入.
  3. Taで属性A1が最小値のレコードを更新
この場合,Taが更新するのはTbが挿入したレコードになってしまう.この問題に対処するには,Taはテーブルをロックするしかない.テーブルのロックにはLOCKを使用する.
benchmark=> \h LOCK
Command:     LOCK
Description: explicitly lock a table
Syntax:
LOCK [ TABLE ] name [, ...]
LOCK [ TABLE ] name [, ...] IN lockmode MODE

where lockmode is one of:

        ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE |
        SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
PostgreSQLでは8つのロックモードがある.これらの詳細は次節に示す.では例を示す.まずTaでテーブルロックをかける.
benchmark=> BEGIN ;
BEGIN
benchmark=> LOCK TABLE result_linpack
benchmark-> IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE
benchmark=> SELECT * FROM result_linpack 
benchmark-> WHERE gflops = (
benchmark(> SELECT min(gflops) FROM result_linpack );
   n   | nb  | cluster_id | gflops | exec_date 
-------+-----+------------+--------+-----------
 12000 | 120 |          1 | 103.64 | 
(1 row)
SHARE ROW EXCLUSIVE MODEはある時点で1トランザクションのみ取得可能で,INSERT,UPDATE,DELETE時に自動取得されるROW EXCLUSIVE MODEとコンフリクト(衝突)する.つまりSHARE ROW EXCLUSIVE MODEを取得したトランザクションがある間,他トランザクションはINSERT,UPDATE,DELETEを待たされる.この状態でTbを開始する.
benchmark=> BEGIN ;
BEGIN
benchmark=> INSERT INTO result_linpack (n, nb, cluster_id, gflops)
benchmark-> VALUES (100000, 300, 1, 1000);
Taでresult_linpackテーブル全体をロックしているので,TbはINSERTできない.ここでTaを終了するとTbが再開した.
前節でもすこし触れたが,PostgreSQLでは8つのロックモードがある.これらは異なるレベルを持ち,互いのレベル次第でコンフリクトしたりしなかったりする.いくつかのロックモードは特定の操作で自動ロックされる.以下にそれらの一覧を示す(※).
ACCESS SHAREROW SHAREROW Ex.SHARE UPDATE Ex.SHARESHARE ROW Ex.Ex.ACCESS Ex.自動ロック
ACCESS SHARE SELECT
ROW SHARE SELECT FOR UPDATE
ROW EXCLUSIVE INSERT,UPDATE,DELETE
SHARE UPDATE EXCLUSIVEVACUUM (FULLオプションなし)
SHARE CREATE INDEX
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE ALTER TABLE,DROP TABLE,VACUUM FULL
LOCK文でモードを指定しない場合はACCESS EXCLUSIVEとなる.主なSQL文で自動ロックされるロックは最初の三つである.したがって例えば他トランザクションのINSERTを待たせたければ,INSERT時に自動発行されるROW EXCLUSIVEとコンフリクトするSHARE以上のロックを取得すればよい.他トランザクションによるSELECT文を待たせたければ,SELECT時に自動ロックされるACCESS SHAREとコンフリクトするACCESS EXCLUSIVEを取得すればよい.
※ 縦の各ロックレベルは,横の○がついているロックレベルとコンフリクトする.例えば2行目の「ROW SHARE」はEx.(EXCLUSIVE)とACCESS Ex.(ACCESS EXCLUSIVE)とコンフリクトする.
前節の一覧表によりどのロックモードを採用すべきかはだいたいわかると思う.だが,サブクエリのあるSQL文を含むトランザクションでは注意が必要である.例えばUPDATE文でもサブクエリでSELECT文を使用していると思わぬ結果になったりする.具体例を一つ示す.まずTa.
benchmark=> BEGIN ;
BEGIN
benchmark=> SELECT * FROM result_linpack 
benchmark-> WHERE gflops = (
benchmark(> SELECT min(gflops) FROM result_linpack )
benchmark-> FOR UPDATE;
   n   | nb  | cluster_id | gflops  | exec_date 
-------+-----+------------+---------+-----------
 11000 | 120 |          1 | 103.467 | 
(1 row)
次にTbで同レコードをDELETEする.
benchmark=> BEGIN ;
BEGIN
benchmark=> DELETE FROM result_linpack 
benchmark-> WHERE gflops = (
benchmark(> SELECT min(gflops) FROM result_linpack );
Taでロックされているので,Taがロックを解除するまでTbは待たされる.そこでTaを更新してみる
benchmark=> UPDATE result_linpack 
benchmark-> SET gflops = gflops + 1000
benchmark-> WHERE gflops = (
benchmark(> SELECT min(gflops) FROM result_linpack )
benchmark-> ;
UPDATE 1
まだTbは待たされている.どうやらTaは終了するまでロックを開放しないようだ.TaをCOMMITするとTbが再開された.
benchmark=> DELETE FROM result_linpack 
benchmark-> WHERE gflops = (
benchmark(> SELECT min(gflops) FROM result_linpack );
DELETE 0
DELETEされた行はない.ここからTbのDELETEがどの状態でTaを待っているかを予測してみる.Taはgflopsの値を変更した.TbのDELETE文はそのサブクエリからして必ず1行以上削除する見えるが,削除されなかった.Tbはサブクエリ実行後(※)の親クエリのWHERE句が確定した段階でTaを待っていたようだ.つまりTaがCOMMITしてTbが再開したとき,TbはWHERE句のgflops = 確定値で実行するだけで,サブクエリはTaを待つ前に実行を終了していると考えられる.このように,他トランザクションを待つといっても,サブクエリでSELECT文まで実行したあとで待つケースもあるので,場合によってはこれが問題となる場合がある.
※ TaのSELECT FOR UPDATEで自動ロックされるROW SHAREはSELECTで自動ロックされるACCESS SHAREとコンフリクトしない.
matsu(C)
Since 2002
Mail to matsu