同時実行制御

複数のトランザクションが同時に同じデータを変更すると矛盾が起こる可能性がある。
そのようなことが起こらないように制御することを同時実行制御という。

準備

次のＳＱＬをファイルとして作成。（setup141.sql）


oddtbs=> \i setup141.sql
...
oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
(2 rows)




以降、２画面から操作


各項目終了後にsetup141.sqlを実行する



ダーティーリード



あるトランザクションで確定してない途中経過は、ロールバックする可能性がある。

よってそのトランザクションの外からは見えなくなっている。

未確定な途中経過がトランザクションの外から見えてしまう現象をダーティーリードという。

ダーティーリードが起こらないことを確認する。






画面①画面②






oddtbs=> start transaction;
START TRANSACTION
oddtbs=> insert into t1 values(3, 30);
INSERT 0 1
oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
  3 |  30
(3 rows)









oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
(2 rows)

※ INSERTの結果は見えない











oddtbs=> commit;
commit;









oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
  3 |  30
(3 rows)

※ 確定したので見える










分離レベル



トランザクションの分離レベルとは、他のトランザクションの影響をどのくらい受けるかのレベルである。

PostgreSQLのトランザクションには次の２つの分離レベルがある。

・リードコミッティド

・シリアライザブル




リードコミッティド



リードコミッティドは、他のトランザクションが確定した内容は見えるというレベル。

分離レベルを指定しなければ、通常はリードコミッティドになる。




どちらかの画面でsetup141.sqlを実行しておく。




画面①画面②





oddtbs=> start transaction;
START TRANSACTION

※ リードコミッティドで開始











oddtbs=> start transaction;
START TRANSACTION
oddtbs=> insert into t1 values(3, 30);
INSERT 0 1
oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
  3 |  30
(3 rows)









oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
(2 rows)

※ 未確定なので見えない











oddtbs=> commit;
COMMIT









oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
  3 |  30
(3 rows)

※ 確定すると見える

oddtbs=> commit;
COMMIT










シリアライザブル



シリアライザブルは自分が全データを独占しているかのように振舞う。

他のトランザクションが更新して確定したデータも、自分の振る舞いと矛盾する場合は見えない。

分離レベルは、ISOLATION LEVELで指定する。




START TRANSACTION ISOLATION LEVEL 分離レベル;



今回は分離レベルでSERIALIZABLEを指定する。


どちらかの画面でsetup141.sqlを実行しておく。




画面①画面②





oddtbs=> start transaction isolation level serializable;
START TRANSACTION

※ シリアライザブルで開始











oddtbs=> start transaction;
START TRANSACTION
oddtbs=> insert into t1 values(3, 30);
INSERT 0 1
oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
  3 |  30
(3 rows)









oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
(2 rows)

※ 未確定なので当然見えない











oddtbs=> commit;
COMMIT









oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
(2 rows)

※ 自分ではSELECTから何もしていないので追加が見えない！

oddtbs=> commit;
COMMIT










シリアライザブルでの更新



シリアライザブルは参照（SELECT）しかしないトランザクション向きである。

更新を行うトランザクションでは実質使用できない。

更新を行うトランザクションでシリアライザブルを使用した場合どのようなことが起こるかを確認する。




どちらかの画面でsetup141.sqlを実行しておく。




画面①画面②





oddtbs=> start transaction isolation level serializable;
START TRANSACTION

※ シリアライザブルで開始











oddtbs=> start transaction;
START TRANSACTION
oddtbs=> update t1 set num = num + 200 where id = 1;
UPDATE 1
oddtbs=> select * from t1;
 id | num
----+-----
  2 |  20
  1 | 210
(2 rows)









oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
(2 rows)

※ 未確定なので当然見えない











oddtbs=> commit;
COMMIT









oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
(2 rows)

※ 確定しても見えない

oddtbs=> update t1 set num = num + 100 where id = 1;
oddtbs=> update t1 set num = num + 100 where id = 1;
ERROR:  could not serialize access due to concurrent update

※ 結果が実際の状態と矛盾するためエラーとなった！

oddtbs=> select * from t1;
ERROR:  current transaction is aborted, commands ignored ...

※ エラー以降は何も受け付けない

oddtbs=> commit;
ROLLBACK

※ 実際にはロールバックされる










ロック



同時実行制御を実現するため、テーブルの各行にはロックというものが備わっている。

その行を更新するトランザクションは、先立ってロックを獲得する必要がある。

ロックを獲得できるトランザクションは同時には１つに限られる。

ロックの獲得を試みたとき、他のトランザクションがロックを保持している場合は、通常待ち状態になる。

トランザクションがロックを獲得した場合、トランザクション終了までロックを保持する。




暗黙的なロック


データを更新する場合、トランザクションは自動的にロックの獲得を試みる。


どちらかの画面でsetup141.sqlを実行しておく。




画面①画面②





oddtbs=> start transaction;
START TRANSACTION











oddtbs=> start transaction;
START TRANSACTION
oddtbs=> update t1 set num = 200 where id = 1;
UPDATE 1
oddtbs=> select * from t1;
 id | num
----+-----
  2 |  20
  1 | 200
(2 rows)









oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
(2 rows)

※ 未確定なので見えない

oddtbs=> update t1 set num = 100 where id = 1;

※ ロックが獲得できず待ちの状態になる











oddtbs=> commit;
COMMIT









※ 処理を再開

UPDATE 1
oddtbs=> select * from t1;
 id | num
----+-----
  2 |  20
  1 | 100

※ 先ほどのUPDATEが実行されている

oddtbs=> commit;
COMMIT










更新時の問題点


SELECTの結果は、その直後に他のトランザクションが更新する可能性があり、信頼できない。


どちらかの画面でsetup141.sqlを実行しておく。




画面①画面②





oddtbs=> start transaction;
START TRANSACTION
oddtbs=> select * from t1 where id = 1;
 id | num
----+-----
  1 |  10
(1 row)











oddtbs=> start transaction;
START TRANSACTION
oddtbs=> select * from t1 where id = 1;
 id | num
----+-----
  1 |  10
(1 row)
oddtbs=> update t1 set num = num - 8 where id = 1;
UPDATE 1
oddtbs=> select * from t1;
 id | num
----+-----
  2 |  20
  1 |   2
(2 rows)
oddtbs=> commit;
COMMIT









oddtbs=> update t1 set num = num - 8 where id = 1;
UPDATE 1
oddtbs=> select * from t1;
 id | num
----+-----
  2 |  20
  1 |  -6
(2 rows)

※ 先のSELECTではnumが10だったのに負になった！

oddtbs=> commit;
commit;










SELECT FOR UPDATE



上記の問題を解決する方法としてSELECT FOR UPDATE構文がある。

SELECT文にFOR UPDATEを付加すると、参照された行のロックを取得する。


どちらかの画面でsetup141.sqlを実行しておく。




画面①画面②





oddtbs=> start transaction;
START TRANSACTION
oddtbs=> select * from t1 where id = 1 for update;
 id | num
----+-----
  1 |  10
(1 row)

※ この行のロックを獲得しているので信頼できる











oddtbs=> start transaction;
START TRANSACTION
oddtbs=> select * from t1 where id = 1 for update;

※ ロックが獲得できず待ち状態になる









oddtbs=> update t1 set num = num - 8 where id = 1;
UPDATE 1
oddtbs=> select * from t1;
 id | num
----+-----
  2 |  20
  1 |   2

※ 絶対負にはならない

oddtbs=> commit;
COMMIT











※ 再開後、先ほどのSELECTの結果を表示

 id | num
----+-----
  1 |   2
(1 row)

※ numが8未満なのでUPDATEは実行しない

oddtbs=> commit;
COMMIT









挿入時の問題点



新たな行を追加する際、ある列の値をその時点での最大値プラス１となるようにする。

一見重複しないように見えるが、複数のトランザクションが同時に動作した場合を確認する。




どちらかの画面でsetup141.sqlを実行しておく。




画面①画面②





oddtbs=> start transaction;
START TRANSACTION
oddtbs=> insert into t1 values((select max(id) from t1) + 1, 30);
INSERT 0 1
oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
  3 |  30
(3 rows)











oddtbs=> start transaction;
START TRANSACTION
oddtbs=> insert into t1 values((select max(id) from t1) + 1, 40);
INSERT 0 1
oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
  3 |  40
(3 rows)

※ 他のトランザクションの経過は見えないので、id列の値は3となる。

oddtbs=> commit;
COMMIT









oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
  3 |  30
  3 |  40
(4 rows)

※ id列が3で重複してしまった！

oddtbs=> commit;
COMMIT










テーブルロックの使用



そもそも、行を追加する以前には、行そのものが存在していないので、

上記のような場合、各行に備わっているロックを操作しても解決はできない。

そのような場合は、テーブル全体として１つ備わっているテーブルロックを使用する。

テーブルロックを獲得する方法は次の通り。




LOCK テーブル名;




テーブルロックもトランザクション終了まで保持する。

前回の例では、新たな行を追加するときはテーブルロックを獲得するという約束にする。




どちらかの画面でsetup141.sqlを実行しておく。




画面①画面②





oddtbs=> start transaction;
START TRANSACTION
oddtbs=> lock t1;
LOCK TABLE
oddtbs=> insert into t1 values((select max(id) from t1) + 1, 30);
INSERT 0 1
oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
  3 |  30
(3 rows)











oddtbs=> start transaction;
START TRANSACTION
oddtbs=> lock t1;

※ ロックが獲得できず待ち状態になる









oddtbs=> commit;
COMMIT











再開

oddtbs=> insert into t1 values((select max(id) from t1) + 1, 40);
INSERT 0 1
oddtbs=> select * from t1;
oddtbs=> select * from t1;
 id | num
----+-----
  1 |  10
  2 |  20
  3 |  30
  4 |  40
(4 rows)

id列は重複していない

oddtbs=> commit;
COMMIT









デッドロック



複数のテーブルロックを同時に獲得するトランザクションが複数ある場合、

お互いがお互いを待ち、処理が進まないデッドロックという現象が発生する可能性がある。

PostgreSQLはデッドロックを監視しており、発生すれば異常終了し、ロールバックがかかる。

なお、全てのトランザクションが同じ順番でロックを獲得すれば、デッドロックは発生しない。




準備


oddtbs=> drop table t1;
oddtbs=> create table t1(id integer);
oddtbs=> drop table t2;
oddtbs=> create table t2(id integer);





画面①画面②





oddtbs=> start transaction;
START TRANSACTION
oddtbs=> lock t1;
LOCK TABLE











oddtbs=> start transaction;
START TRANSACTION
oddtbs=> lock t2;
LOCK TABLE
oddtbs=> lock t1;
LOCK TABLE

※ ロックが獲得できず待ち状態になる









oddtbs=> lock t2;
ERROR:  deadlock detected
DETAIL:  Process 3096 waits fo ...

※ デッドロックが発生したためエラーになった！

oddtbs=> commit;
ROLLBACK











再開

oddtbs=> commit;
COMMIT