ある一連の処理の、開始から終了までの区間をトランザクションと呼ぶ。
もちろん、区間の開始と終了を知らせる必要はある。

そして、その処理の間に他の処理が割り込んできて
同じデータベースのレコードに更新などの処理を行っても
割り込めないようにしている。

上の処理の流れは普通預金から定期預金へ10万円を移す処理だ。
この一連の流れ(処理)の事を「トランザクション」という。

「ROLLBACK」を行うと、トランザクションの開始時点まで
処理が巻き戻されるため、全て最初から処理をやり直す事になる。

トランザクションの途中にセーブポイントという印をつけて置く。
その後、異常が発生し「ROLLBACK」を行う際、
セーブポイントまで戻る事を指示しておけば、
巻き戻しがセーブポイントの時点のため、
最初から処理をやり直す必要はなくなる。

トランザクションの途中にセーブポイントを、いくつもつける事ができる。
これにより、異常終了が発生した時に、異常終了の内容に応じて戻す位置を
指定する事ができる。

セーブポイントSP2に巻き戻したが、それより前のセーブポイントSP1まで
巻き戻す事が可能なのだ。

ただし、一度、SP1まで巻き戻すと、今度は、SP2へ前進させる事はできない。

トランザクションの途中で、更新した場合、正常終了の「COMMIT」と
異常終了の「ROLLBACK」の両方に備えて、更新前のレコードと
更新後のレコードの両方を持っている必要がある。

この方法でOracleやMySQLでは、トランザクション中に
更新前と更新後のレコードを取り出せるようにしている。

PostgreSQLでは、更新前のレコードに印をつけておく。
そして、更新後のレコードを後ろに追加する形をとる。

追加する形態なので「追記型」と呼ぶ。

この方法でPostgreSQLでは、トランザクション中に
更新前と更新後のレコードを取り出せるようにしている。

使わなくなった古いレコードが溜っていく。
ディスクの占有の問題が生じるだけでなく、
データベースの検索等の速度にも影響してくる。

不要になったレコードは、放置しておくと、削除されずに溜っていく。
その不要なレコードが溜ると、ディスクの占有だけでなく、
レコードの検索等でファイル走査する場合、不要なレコードまで
走査するため、性能が悪くなる。

そこで、VACUUMを行って、不要レコードを削除していた。

VACUUM中、データベースに接続したくても、
テーブルにロックがかかるため、接続できなくなる。

小規模なデータベースなら、1秒もかからないので、問題にならないが
大規模の場合、大量接続が考えられる上、VACUUMの作業にも
時間がかかる事から、接続できない時間帯ができてしまい、
24時間運用ができないという問題があった。

不要になったレコードは、放置しておくと、削除されずに溜っていく。

VACUUMを行うと、不要なレコードが何かを共有メモリ上に記録する。
この時点では、不要レコードは削除されない。

さて、PostgreSQLに複数の接続があったら、それと同じだけの数の
PostgreSQLのプロセスが発生する。
共有メモリとは、その複数のPostgreSQLのプロセスが共有している
メモリ領域の事だ。

共有メモリについて詳しい事を書き出したら、長くなりますので
今回は割愛させていただきます m(--)m

trandb=> VACUUM;
WARNING:  Skipping "pg_group" --- only table or database owner can VACUUM it
WARNING:  Skipping "pg_shadow" --- only table or database owner can VACUUM it
WARNING:  Skipping "pg_database" --- only table or database owner can VACUUM it
VACUUM
trandb=>

[suga@postgres]$ vacuumdb trandb
WARNING:  Skipping "pg_group" --- only table or database owner can VACUUM it
WARNING:  Skipping "pg_shadow" --- only table or database owner can VACUUM it
WARNING:  Skipping "pg_database" --- only table or database owner can VACUUM it
VACUUM
[suga@postgres]$ 

共に同じVACUUMの処理を行う。

「WARINIG Skipping "pg_group"」といった感じの警告が出ている。
それは、データベースファイルの中に、ユーザーが作成したテーブル以外に
データベースを管理するテーブルもあり、それらの所有者が管理者に
なっているため、管理者所有のテーブルは、VACUUMの処理ができないと
警告しているのだ。

PostgreSQLは追記型データベースなので、新規レコードや
レコードの更新がある場合、テーブルに追加される形になる。

VACUUMを行うと、不要レコードの場所が記録されるため、
新規や更新するレコードは、テーブルに追加する形ではなく、
不要になったレコードの所に上書きされる。

つまり、不要なレコードの領域を再利用する形になる。

この上書きにより、レコード数が増える事がなくなるため、
ディスクを食う事もなければ、レコード検索等に時間が
かかる事もなくなる。

これだと、不要レコードの削除のために、
テーブルをロックする必要がなくなるので、
VACUUM中にデータベースに接続できなくなる問題が解消され
24時間運用が可能になるというわけだ。

trandb=# VACUUM FULL;
VACUUM
trandb=# 

[kanri@postgres]$ vacuumdb -f trandb
VACUUM
[kanri@postgres]$ 

SQL文では後ろに「FULL」をつけ、コマンドでは「-f」オプションをつける。
管理者権限を持ったユーザーで実行させたため、警告は出てこない。

7.2以降では、VACUUMに「FULL」オプションをつける。
すると、不要レコードが削除される。

それと同時に、不要レコードを記した共有メモリの部分も更新される。
なぜなら、VACUUMによって不要レコードが削除されるため、
不要レコードを記録した部分も、更新する必要があるからだ。

複数のトランザクションを並行の処理していけば、
1つのトランザクションの動き止まってしまっても、
トランザクションの渋滞が起こる危険はなくなる。

トランザクションT1が、テーブルを読み込む。
この時、トランザクションT1は「a」の値を「100」と認識する。

その次に、トランザクションT2が、「a」の値を読み込む。
この時、トランザクションT2は「a」の値を「100」と認識する。

トランザクションT2は、読み込んだ「a」の値に1.05をかけた値を更新する。
この時、トランザクションT2は、「a」の値を「105」と認識する。

ここで厄介な問題が起こる。
(3)で、トランザクションT2によって「a」の値が変更されている。
しかし、トランザクションT1は、それに気づかずに「a」の値を
「100」と認識したままで、100を加算するため、「a」の値を「200」で
更新していまう。

つまり、トランザクションT2の処理が全く反映されないのだ。

ここでデータの不整合が発生してしまうのだ。

トランザクションT1が「a」の値を読み込む。
この時、「a」の値は「100」と認識される。
その後、データベースにロックをかける。

トランザクションT2が「a」の値を読み込みにいくのだが、
ロックがかかっているため、ロックが解除されるまで待つ。

トランザクションT1が読み込んだ「a」に「100」を加算した値を更新する。
この時、「a」の値は「200」になる。
そして、ロックを解除する。

これだと順番にトランザクションの処理を行った形がとれるため、
全ての処理が反映され、データの整合性が保たれる。

トランザクションの途中は、個々に必要なロックをかけていくのだが、
ロックを解除する事ができない。
トランザクションの終了時に、全てのロックの解除を行う。

このようなロックの事を「二相ロック」と言う

「二相ロック」は英語の「2 Phase Lock」を翻訳した物だ。
「相」は英語の「Phase」から由来している。
個人的には「相」よりも「段階」の方がわかりやすいと思うので
「段階」という言葉で説明します。

第一段階は、トランザクションの途中の段階を意味する。
第二段階は、トランザクション終了時の段階を意味する。

ロックをかける段階と、ロックを解除する段階の2段階あるので
「二相ロック」というのだ。

トランザクションT1がテーブルA、テーブルBの順にロックをかけ、
トランザクションT2がテーブルB、テーブルAの順にロックをかける
場合を考える。

まず、トランザクションT1がテーブルAにロックをかける。
そして、トランザクションT2がテーブルBにロックをかける。

次に、トランザクションT1がテーブルBにロックをかけようとするが
トランザクションT2がかけたロックの解除待ちになる。

同時に、トランザクションT2がテーブルAにロックをかけようとするが
トランザクションT1がかけたロックの解除待ちになる。

この時、お互いが、お互いのロック解除待ちの状態に陥り
ニッチモサッチもいかなくなる。
これがデッドロックというのだ。

トランザクション開始時に、実行時刻(通し番号)を付け
その順番を使って、強制的に実行を制御しようとするもの

2つのトランザクションT1、T2があるとする。

トランザクションT1が先に開始される。
その後、トランザクションT2が開始される

トランザクションT2が更新したデータを
トランザクションT1が読み込まないようにする

トランザクションT2が読み込んだデータを
トランザクションT1が更新しないようにする。

トランザクションT2が更新したデータを
トランザクションT1が読み込まないようにする。

つまり、トランザクションT1が開始した時は、
T1はテーブルAの情報は更新されていない状態だと認識している。

だが、T1の後で開始されたトランザクションT2が
テーブルAを更新する。

そして、T1がテーブルAを読み込む場合、古い値だと認識しているので
T2が更新した値を読み込むと矛盾が生じる。
そのため、矛盾を回避するために、T1は読み込めないようにしている。

トランザクションT2が読み込んだデータを
トランザクションT1が更新しないようにする。

トランザクションT2が読み込みを行った時、
T2は更新前の状態のデータだと認識する。

その後で、トランザクションT1がテーブルAを更新すれば、
T2が読み込んだデータとの整合性が保てないために、
T1は更新できないような仕掛けになっている。

先にトランザクションT1を開始して、その後で、トランザクションT2を開始する。

実験では、後発のT2がデータの更新した後に、先発のT1が閲覧した場合、
どういう結果が表示されるのかを確かめる事にした。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)
trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> UPDATE tran1 SET b = 20 ;
UPDATE 1
trandb=>

トランザクションT2で、テーブルの「b」の値を「20」に変更した後で、
トランザクションT1で、テーブルの「b」の値を閲覧する。青い部分。
しかし、青い部分の結果は、「b」の値は「10」のままで、
トランザクションT2の更新結果が反映されていない。

明示的にトランザクション開始の「BEGIN」を出したT1なのだが、
その後で、明示的に「BEGIN」を出していない別のトランザクションが
更新作業を行うと

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 20
(1 row)
trandb=>

trandb=> UPDATE tran1 SET b = 20 ;
UPDATE 1
trandb=>

別のプロセスが更新する前後で、トランザクションT1が
レコードを閲覧した結果を比較すると、更新影響を受けているのがわかる。

深く調べていないので、何とも言えないが、お互いに明示的に
トランザクションの開始の「BEGIN」を出さないと、トランザクションの
同時実行制御はできないと思う。

先にトランザクションT1を開始して、その後で、トランザクションT2を開始する。

後続のT2がデータ更新した後で、先発のT1がデータ更新した場合、
どういう事が起こるのか確かめてみる事にした。

途中のSELECT文は、テーブルの値の変化を見るための物です。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> UPDATE tran1 SET b = 30 ;

(トランザクションT2が終了するまで待ち続ける)

trandb=> BEGIN ;
BEGIN
trandb=> UPDATE tran1 SET b = 20 ;
UPDATE 1
trandb=>
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 20
(1 row)

trandb=>

後続のトランザクションT2がデータ更新した後に、先発のトランザクションT1が
データ更新を行おうとするが、トランザクションT2の終了待ち状態になる。

先にトランザクションT1を開始して、その後で、トランザクションT2を開始する。

T2がテーブルのデータを消去した後で、T1がデータを更新する。
そして、2つのトランザクションを「COMMIT」で終了させる。

テーブルのデータはどうなっているのか確かめてみる。
先発のT1は開始時に、テーブルのデータが存在していると認識しているので、
その更新データが反映された形になるのか、それともT2が
データを消去したため、T1が何をしても、データは存在しない形になるのか

trandb=> BEGIN ;
BEGIN
trandb=> UPDATE tran1 SET b = 30 ;
trandb=> COMMIT;
COMMIT
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
(0 rows)
trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> DELETE FROM  tran1 ;
DELETE 1
trandb=> COMMIT;
COMMIT
trandb=>

トランザクションT2でデータを消去(赤い部分)
その後で、トランザクションT1でデータを更新(青い部分)

両方のトランザクションを「COMMIT」で終了させて、
テーブルの中身を見てみる(緑の部分)
結果は、データが消去されていた。トランザクションT2の結果が反映され
トランザクションT1の更新は全く反映されていなかった。

先にトランザクションT1を開始して、その後で、トランザクションT2を開始する。

トランザクションT2がデータ更新を行った後、
トランザクションT1がデータの中身を閲覧する。

そして、T2を「COMMIT」で終了させた後、T1がデータの閲覧を行う。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 30
(1 row)
trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> UPDATE tran1 SET b = 30 ;
UPDATE 1
trandb=> COMMIT;
COMMIT
trandb=>

トランザクションT2でデータを更新させた後、トランザクションT1で
データを閲覧した場合、データの変更前の値が出てきた。

その後、T2を「COMMIT」で終了させた後、T1でデータを閲覧すると
更新が反映された後の値が出てくる。

先にトランザクションT1を開始して、その後で、トランザクションT2を開始する。

トランザクションT2が「b」の値を「20」に更新を行った後、
トランザクションT1がデータの中身を閲覧する。
そして、T2は「COMMIT」で終了させる。
T1は「b = b + 10」という形でデータの更新を行い、
再度、T1は「b」の値を読み込む。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> UPDATE tran1 SET b = b + 10 ;
UPDATE 1
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 30
(1 row)
trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> UPDATE tran1 SET b = 20 ;
UPDATE 1
trandb=> COMMIT ;
COMMIT
trandb=>

先にトランザクションT1を開始して、その後で、トランザクションT2を開始する。

トランザクションT2が「b」の値を「20」に更新を行った後、
トランザクションT1がデータの中身を閲覧する。
この時、トランザクションT1はT2の更新前の値を読む込むため、
T1は古い値「b = 10」と認識する。

そして、T2を「COMMIT」で終了させる。
この時、T2が「b = 20」にした値がデータに反映される。

だが、そんな事を知らないT1は、T1は「b = 10」と認識した状態で
「b = b + 10」とする。
T1としては「b = 20」に更新されると思うのだが、結果は、
「b」の値が「30」と表示される。

明らかに、データに矛盾が生じる問題が発生するのだ！

各レコードには、そのレコードを作成・更新したトランザクションのID
即ち、XIDが記録される。
それによって、どのトランザクションが作成・更新したかがわかる上、
現在、動いているトランザクションのXIDと比較して、
レコードを作成したトランザクションが、自分よりも先か後かがわかる。

ちなみに、OIDとは、オブジェクトIDの意味で、レコード別に
固有のIDが割り当てられる。

XIDが4300のトランザクションが、りんごの価格を300円に値上げするため、
りんごのレコードを更新する。
PostgreSQLは追記型なので、テーブルの後ろに更新レコードを追加する形になる。

この時、OIDは、りんごのレコードなので、更新する前のOIDと同じ物を使うが
XIDは、更新したトランザクションのIDを書き込むため、4300が入る。

まだ、トランザクションの途中で、終了の「COMMIT」を出していないため、
不要レコードの部分には印は入らない。
なぜなら、「COMMIT」を出して、初めて、更新したレコードの値を
確定するわけであって、この時点では確定したわけでないからだ。

この状態だと、更新前のレコードも、更新後のレコードも取り出せる。

2つのトランザクションが開始される。
後続のトランザクション(XID:4300)が、りんごの値上げをするため
レコードの更新を行う。
この時点ではトランザクションの終了の「COMMIT」を出していないため、
更新前と更新後の両方のレコードが取り出せる。

先発のトランザクション(XID:4250)は、トランザクション開始前の
レコードを読み込むのが前提なので、レコードに記録されているXIDの値と
自分自身のXIDとを比較して、古い方(更新前)のレコードを読み込む。

これによってデータの整合性を保てる仕掛けになっている。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)
trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> UPDATE tran1 SET b = 20 ;
UPDATE 1
trandb=>

後発のトランザクションT2がレコードを更新しても、
「COMMIT」をしていないため、更新したレコードは、未確定の状態で、
更新前のレコードも生きている状態だ。

先発のトランザクションT1は、自分自身が開始された時のレコードを
データの整合性を保つため、読み込む前提になっている。

この時点では、トランザクションT2がレコードを更新しているが、
更新前のレコードも生きているので、更新前のレコードを読み込む。
そのため、更新前の値が取り出せるのだ。

「Multi Version Concurrency Control」の完全日本語訳は
「多版型同時実行制御」だが、これでは意味がわからない。

そこで英英辞典を取り出して調べてみた。
「Version」の訳し方次第で意味がわかりやすくなるのか、
それともわかりにくくなるのかの分かれ目になる気がした。

「Multi」は「多数」の意味なので「多」という訳になる。
「Concurrency」は「同時に発生している2つ以上の出来事」という意味だ。
「Control」は「制御」の意味だ。

さて、問題は「Version」だ。
「版」と訳しているのは「発行された個々のトランザクション」の
意味合いだと思われる。
だが、「版」だと、普段使わない言葉だけに、わかりづらい。
しかし、他に良い訳がないので、「版」とならざる得ない気がする。

補足をいれた長い翻訳文にすると「複数のトランザクションを
同時に実行させるための制御方法」という文になるのだが、
こんな物は用語としては使えない。

なので、外国で生まれた用語を上手に短く訳す難しさを感じる今日この頃。

トランザクションは「COMMIT」を出す事によって、
更新したレコードの値を確定させる。

この時、初めて、更新前のレコードに「不要フラグ」に印をつける。
こうなると、更新前のレコードが取り出せなくなる。

2つのトランザクションが動いていて、後続のトランザクション(XID:4300)が
りんごの値上げのために、レコードの更新を行う。
この時は「COMMIT」を出していないため、更新前のレコードも取り出せる。
なので、先発のトランザクション(XID:4250)は、更新前のレコードを読み出せる。

しかし、後続のトランザクション(XID:4300)が「COMMIT」をしてしまうと
更新前のレコードに不要フラグが立ち、更新前のレコードが取り出せなくなる。

そして、先発のトランザクション(XID:4250)が、もう一度、レコードを
読み込む際には、更新後のレコードを読み込んでしまうため、
先発のトランザクションは、何もしていないにも関わらず、
全く違う値を読み込んでしまう事態が起こってしまう。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> UPDATE tran1 SET b = b + 10 ;
UPDATE 1
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 30
(1 row)
trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> UPDATE tran1 SET b = 20 ;
UPDATE 1
trandb=> COMMIT ;
COMMIT
trandb=>

後発のトランザクションT2がレコードを更新しても、
「COMMIT」していない場合、更新レコードは未確定なため、
更新前と更新後のレコードが共存している。
そのため、先発のトランザクションT1が、レコードを読み込んでも
問題なく更新前のレコードを読み込める。

だが、後続のトランザクションT2が「COMMIT」をしてしまうと、
更新レコードが確定してしまうため、更新前のレコードが取り出せなくなる。
そのため、先発のトランザクションT1がレコードを読み込むと
更新後のレコードの値を読む事態になる。
トランザクションT1は何もしていないのに、全く違い値を読むため
データの整合性の問題が生じるのだ。

このロックをかけると、他のトランザクションは
書き込みはできないが、読み込みはできる。

読み込みは、どのトランザクションも可能なので
「共有ロック」という。

このロックをかけると、他のトランザクションは
読み書き不可になる。

読み書きできない排他的な状態なので「排他ロック」という。

先発のトランザクションT1が開始したと同時に、レコードに
共有ロックをかける。

そして、後発のトランザクションが開始を明示的に表示する
「BEGIN」を出す前と後で、SELECTを行い、レコードが読めるかどうかを
見てみる事にした。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 FOR UPDATE ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=>

trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=>

先発のトランザクションT1で、行ロックをかけたのだが、
共有ロックのため、後発のトランザクションT2では
問題なく、テーブルを見にいく事ができる。

トランザクションT1でレコードに共有ロックをかけてみる。
その後で、トランザクションT2が共有ロックがかかったレコードに
更新をかけてみると、どうなるのか見てみる事にした。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 FOR UPDATE;
 a | b  
---+----
 1 | 10
(1 row)

trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> UPDATE tran1 SET b = 20 ;

(T1が終了するまで待ち状態が続く)

いくらトランザクションT2がレコードを更新したくても
トランザクションT1がかけたロックのお陰で更新できない状態になる。

トランザクションT1の終了(COMMITかROLLBACK)をしない限り、
ロックが解除されないため、T2は待ちの状態が続く。

トランザクションT1で、「a」の値が「2」に該当するレコードに
行ロックをかけてみた。
その次に、トランザクションT2で、ロックがかかっていないレコードの
更新を行ってみた。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM  tran1 WHERE a = 2 FOR UPDATE;
 a |  b  
---+-----
 2 | 100
(1 row)

trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> UPDATE tran1 SET b = 20 WHERE a = 1;
UPDATE 1
trandb=>

行ロックがかかっていないレコードは、問題なく更新できるのがわかる。

トランザクションT1は「a」の値が「2」に該当するレコードに行ロックをかける。
そして、別のトランザクションT2は、「a」の値が「2」に該当するレコードの
更新を行ってみる。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 WHERE a = 2 FOR UPDATE;
 a | b  
---+----
 1 | 10
(1 row)

trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> UPDATE tran1 SET b = 20 WHERE a = 2 ;

(T1が終了するまで待ち状態が続く)

トランザクションT2が、行ロックがかかっているレコードを更新したくても
更新できずに、待ち状態になっている。
トランザクションT1の終了(COMMITかROLLBACK)まで、ロックが解除は解除されず
更新は待たされるのだ。

トランザクションT1でテーブルロックをかけてみた。
そして、トランザクションT2でテーブルを閲覧できるかどうかを
確かめて見る事にした。

trandb=> BEGIN ;
BEGIN
trandb=> LOCK TABLE tran1 IN SHARE MODE;
LOCK TABLE
trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM  tran1 ;
 a |  b  
---+-----
 1 |  10
 2 | 100
(2 rows)

trandb=>

トランザクションT1で、テーブルロック(共有ロック)をかけたが
共有ロックのため、別のトランザクションT2が閲覧しても
問題なく閲覧は可能だとわかる。

ちなみに、この時、トランザクションT2が更新を行う場合は、
トランザクションT1が終了するまで、待ち状態になる。

トランザクションT1で、テーブルロック(排他ロック)をかけてみる。
そして、別のトランザクションT2で、テーブルのレコードの閲覧を行ってみる。

trandb=> BEGIN ;
BEGIN
trandb=> LOCK TABLE tran1 IN ACCESS EXCLUSIVE MODE ;
LOCK TABLE
trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM  tran1 ;

(T1が終了するまで待ち状態が続く)

排他ロックのため、別のトランザクションT2が閲覧したくても
T1の終了(COMMITかROLLBACK)を待つ状態になっている。

複数のトランザクションが同じテーブルのレコードを見にいく場合、
個々の行にロックをかける場合だと、同じ行を見にいかない限り
衝突する事がないため、ロック解除待ちが起こらず、
同時に複数のトランザクションが円滑に処理できる。

あるトランザクションがテーブルロック(共有ロック)をかけたら
閲覧以外の処理を行うトランザクションは、ロック解除待ちの状態になり
処理ができなくなる。

あるトランザクションがテーブルロック(排他ロック)をかけたら
他の全てのトランザクションは、ロック解除待ちの状態になり
全く処理ができなくなる。

トランザクションT1がテーブルA、テーブルBの順にロックをかけ、
トランザクションT2がテーブルB、テーブルAの順にロックをかける
場合を考える。

まず、トランザクションT1がテーブルAにロックをかける。
そして、トランザクションT2がテーブルBにロックをかける。

次に、トランザクションT1がテーブルBにロックをかけようとするが
トランザクションT2がかけたロックの解除待ちになる。

同時に、トランザクションT2がテーブルAにロックをかけようとするが
トランザクションT1がかけたロックの解除待ちになる。

この時、お互いが、お互いのロック解除待ちの状態に陥り
ニッチモサッチもいかなくなる。
これがデッドロックというのだ。

trandb=> BEGIN;
BEGIN
trandb=> LOCK TABLE tableA IN  SHARE ROW EXCLUSIVE MODE ;
LOCK TABLE
trandb=> LOCK TABLE tableB IN  SHARE ROW EXCLUSIVE MODE ;
LOCK TABLE
trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> LOCK TABLE tableB IN  SHARE ROW EXCLUSIVE MODE ;
LOCK TABLE
trandb=> LOCK TABLE tableA IN  SHARE ROW EXCLUSIVE MODE ;
ERROR:  deadlock detected
DETAIL:  Process 7005 waits for ShareRowExclusiveLock 
         on relation 25123 of database 25122; blocked by process 7003.
Process 7003 waits for ShareRowExclusiveLock 
         on relation 25127 of database 25122; blocked by process 7005.
trandb=>

PostgreSQLにはデッドロックを検出する仕組みがあるため、
赤い部分で示しているように、デッドロックが発生したら
エラーがでるようになっている。

エラーを検出した側のトランザクションは異常終了の形をとって
トランザクションを終了させる。

もし、デッドロックを検出する仕組みがなければ、
お互いロック解除待ち状態に陥り、ニッチモサッチモいかなくなる。

#
#       Misc
#
#autocommit = true
#dynamic_library_path = '$libdir'
#search_path = '$user,public'
#datestyle = 'iso, us'
#timezone = unknown             # actually, defaults to TZ environment setting
#australian_timezones = false
#client_encoding = sql_ascii    # actually, defaults to database encoding
#authentication_timeout = 60    # 1-600, in seconds
#deadlock_timeout = 1000        # in milliseconds
#default_transaction_isolation = 'read committed'
#max_expr_depth = 10000         # min 10
#max_files_per_process = 1000   # min 25
#password_encryption = true
#sql_inheritance = true
#transform_null_equals = false
#statement_timeout = 0          # 0 is disabled, in milliseconds
#db_user_namespace = false

#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
 
#deadlock_timeout = 1s
#max_locks_per_transaction = 64         # min 10
                                        # (change requires restart)
# Note: each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.

7.3系と8.2系では、設定ファイル(postgresql.conf)の記述は異なる。
両方とも赤い部分が設定箇所だ。

初期状態では1秒(1000ミリ秒)になっている。

つまり、初期状態ではロックがかかってから、1秒後には
デッドロックが発生しているかどうかを調べるのだ。

後発のトランザクションT2がテーブルの更新を行った後、
「COMMIT」をしない状態であっても、先発のトランザクションT1は
更新した値を読んでしまう現象の事を言う。

ダーティーリードが起こる「UNCOMMITTED READ」の状態だが、
「UNCOMMITTED READ」を直訳すると「未終了状態での読み込み」で
つまりテーブルを更新したトランザクションが「COMMIT」で
終了をしていなくても、他のトランザクションが更新した値を
読み込んでしまうという事だ。

trandb=> SET TRANSACTION ISOLATION LEVEL 隔離レベル;
SET 
trandb=>

ここで注意が必要なのは隔離レベルの指定を行う際に、
「READ UNCOMMITTED」の場合は、「READ UNCOMMITTED」ではなく
「UNCOMMITTED READ」と指定するのだ。順序が逆になっている。

同様に「READ COMMITED」の場合も、「READ COMMITED」ではなく
「COMMITED READ」と指定する。

この事を知らなかっただけに、エラーが出ても何故かわからず
考え込んでしまったのだ (^^;;

あとの「REPEATABLE READ」と「SERIALIZABLE」は、
そのまま指定すれば問題はない。

trandb=> BEGIN;
BEGIN
trandb=> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SET
trandb=> SELECT * FROM tran1 ;
 a | b
---+----
 1 | 10
(1 row)
 
trandb=>

trandb=> BEGIN;
BEGIN
trandb=> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SET
trandb=> UPDATE tran1 set b = 200 ;
UPDATE 1
trandb=>

トランザクションT1の部分で、テーブルの値(赤い部分)を見てみる。
本来なら、ダーティーリードの場合、トランザクションT2が行った
更新の影響(青い部分)で、「b」の値は「200」なのだが、
実験結果は、どういうわけか「10」のままなのだ

trandb=> SHOW default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 read committed
(1 row)
 
trandb=>

後発のトランザクションT2が更新を行っても、「COMMIT」をしない限り、
先発のトランザクションT1は、更新前の値を読み込む。

この図は、MVCCの部分でも出てきました。
そう、後発のトランザクションがデータを更新しても
「COMMIT」していない限り、先発のトランザクションは
更新前のデータを読み込む仕掛けになっているのだ。

つまり、MVCCの実装のお陰で、隔離レベル「READ COMMITED」が
確保できているのだ。

これもMVCCの所で出てきました話と同じです。

後発のトランザクションT2が更新作業を行った後、
「COMMIT」で終了してしまうと、先発のトランザクションT1は
更新後のデータを読み込んでしまう現象なのだ。

これを「反復不能読み取り」という。
「反復不能」は、トランザクションT1は何もしていないのに、
トランザクションT2が更新して「COMMIT」する前と後では
読み込む値が同じでない、つまり反復読み込みできない状態の事を
意味するのだ。

トランザクションの隔離レベルが「READ COMMITED」なのは
「COMMIT」してしまった物を読み込んでしまうという意味なのだ。

トランザクションの隔離レベルを「REPEATABLE READ」に設定すれば
上のように後発のトランザクションT2が更新を行い「COMMIT」しても
先発のトランザクションは更新前のデータを読み込むはず。

なので、実験を行ってみた。

trandb=> BEGIN;
BEGIN
trandb=> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
SET
trandb=> SELECT * FROM tran1 ;
 a | b
---+----
 1 | 10
(1 row)
 
trandb=> SELECT * FROM tran1 ;
 a | b
---+----
 1 | 10
(1 row)
 
trandb=>

trandb=> BEGIN;
BEGIN
trandb=> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
SET
trandb=> UPDATE tran1 set b = 200 ;
UPDATE 1
trandb=> COMMIT;
COMMIT
trandb=>

実験の結果、後発のトランザクションT2が更新を行い、
「COMMIT」をしても、先発のトランザクションT1は影響を受けずに
更新前のデータを読み込んだ。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> SELECT * FROM tran1 ;
 a | b 
---+---
(0 rows)

trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> DELETE FROM tran1 ;
DELETE 1
trandb=> COMMIT ;
COMMIT
trandb=>

最初、先発のトランザクションT1がデータを閲覧する際は問題がない。
レコード数(赤い部分)だが問題なく「1」になっている。

だが、後続のトランザクションT2がデータ消去を行い「COMMIT」をした後で
もう一度、トランザクションT1がデータを閲覧した時、
レコード数(赤い部分)が「0」になっている。データが読めなくなる現象だ。

トランザクションT1自身は、何もしていないのに、見えていたはずの物が
見えなくなる現象なので、ファントムリードという。

trandb=> BEGIN ;
BEGIN
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> SELECT * FROM tran1 ;
 a |  b  
---+-----
 1 |  10
 2 | 100
(2 rows)

trandb=> 

trandb=> BEGIN ;
BEGIN
trandb=> INSERT INTO tran1 VALUES ( 2 , 100 );
INSERT 733645 1
trandb=> COMMIT ;
COMMIT
trandb=>

最初、先発のトランザクションT1がデータを閲覧する際は問題がない。
赤い部分のレコード数が「1」なので問題はない。

だが、後続のトランザクションT2がレコードの追加を行い
「COMMIT」をした後で、もう一度、トランザクションT1がデータを閲覧した時、
追加したレコードも見えてしまう現象だ。

追加されたレコードの明細(青い部分)と、レコード数(赤い部分)が
「2」になっている。

トランザクションT1自身は、何もしていないのに、いつの間に
レコードが増えている現象の事も、ファントムリードという。

trandb=>  BEGIN ;
BEGIN
trandb=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> 

trandb=> BEGIN ;
BEGIN
trandb=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET
trandb=> DELETE FROM tran1 ;
DELETE 1
trandb=> COMMIT ;
COMMIT
trandb=> 

後発のトランザクションT2がレコードを消去して「COMMIT」しても
御札(最強の隔離レベル)にしたため、先発のトランザクションT1は
T2が行った消去の影響を受けずにレコードが閲覧できる。

trandb=>  BEGIN ;
BEGIN
trandb=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=>

trandb=> BEGIN ;
BEGIN
trandb=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET
trandb=> INSERT INTO tran1 VALUES ( 2 , 100 );
INSERT 733643 1
trandb=> COMMIT ;
COMMIT
trandb=> 

後発のトランザクションT2がレコードを追加して「COMMIT」しても
御札(最強の隔離レベル)にしたため、先発のトランザクションT1は
T2が行った追加の影響を受けずにレコードが閲覧できる。

trandb=>  BEGIN ;
BEGIN
trandb=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET
trandb=> SELECT * FROM tran1 ;
 a | b  
---+----
 1 | 10
(1 row)

trandb=> UPDATE tran1 SET b = 20 ;          
ERROR:  Can't serialize access due to concurrent update
trandb=> 

trandb=> BEGIN ;
BEGIN
trandb=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET
trandb=> DELETE FROM tran1 ;
DELETE 1
trandb=> COMMIT ;
COMMIT
trandb=> 

後発のトランザクションT2がレコードを消去し「COMMIT」をしたため
レコードが消えてしまっている。

隔離レベルが最強の場合、先発のトランザクションT1は
T2が消去したレコードの閲覧は可能なのだが、更新を行おうとしても
エラーが発生する。

売上処理を行う際、売上明細テーブルに明細レコードを追加するだけでなく
在庫データから売上した商品の数だけ、在庫の数を引く必要がある。

<?php

$con = pg_connect("dbname=URIAGEDB");

if ( !$con )
   {
   echo "データベースにつながらへん";
   exit ;
   }

// 売上明細の追加のSQL文
$SQL1 = "INSERT INTO URIAGE VALUES ( $ID , $SHID , $KAZU , $DATE )";

// 在庫データの数を引くためのSQL文
$SQL2 = "UPDATE ZAIKO SET ZAKAZU = ZAKAZU - $KAZU";

//  SQL文の処理
pg_exec($con , $SQL1 );
pg_exec($con , $SQL2 );

pg_close($con);

?>

明示的にトランザクションの開始「BEGIN」や正常終了「COMMIT」、
異常終了「ROLLBACK」は使っていない。
異常終了が起こった際の、エラー処理がないため、エラーが発生しても、
プログラムは動き続けるという代物を書いていたのだった。

個々のテーブルに接続して更新をかけるプログラムなため、
もし、在庫データへの接続に失敗した時、売上明細データは追加されたが
在庫データで、売上個数分の在庫を引く作業の処理が行われないため、
データの整合性が取れなくなる。

在庫が合わなくなるため、データに信頼性がなくなってしまう問題が起こる。

<?php

$con = pg_connect("dbname=URIAGEDB");

if ( !$con )
   {
   echo "データベースにつながらへん";
   exit ;
   }

//  BEGINを明示的に宣言
pg_exec($con , "BEGIN" );

// 売上明細の追加のSQL文
$SQL1 = "INSERT INTO URIAGE VALUES ( $ID , $SHID , $KAZU , $DATE )";

// 在庫データの数を引くためのSQL文
$SQL2 = "UPDATE ZAIKO SET ZAKAZU = ZAKAZU - $KAZU";

// 売上明細データのSQL実行処理
$result1 = pg_exec($con , $SQL1 );
if ( $result1 )  // エラー処理
   {
   pg_exec($con , "ROLLBACK" );
   die("売上明細データに接続できず、コケたのらー！！");
   }

// 在庫データのSQL実行処理
$result2 = pg_exec($con , $SQL2 );
if ( $result2 )  // エラー処理
   {
   pg_exec($con , "ROLLBACK" );
   die("在庫データに接続できず、コケたのらー！！");
   }

//  COMMITを明示的に宣言
pg_exec($con , "COMMIT" );

pg_close($con);

?>

明示的にトランザクションの開始や終了、異常時のエラー処理を追加したので
例え、片方のデータへの接続が失敗してもトランザクションの開始前に巻戻す
「ROLLBACK」が働く。そのため、データの不整合が起こるのを防ぐ。

大抵の場合、連番になるので、上の図の状態だと、
次の伝票を発行する際は、「4」の伝票番号を付ける

そのため、伝票番号を割り当てる方法としては、
テーブル内の伝票番号の最大値を取得して、
その値に「1」を足した物にする。

トランザクション機能は使っていなかった上、
ただ単に、SELECT文でテーブル内の伝票番号の最大値を取得し
その値に「1」を足した物を新規の伝票番号として割り当てていた。

上の図のように、ほぼ同時刻に2つの受注があった場合、
ロックをかけていないと、同じ受注番号を取得してしまう
問題が発生してしまう。

まずは、トランザクション機能を使う。
そして、伝票番号を取得する前にテーブルロック(排他ロック)をかける。
共有ロックの場合だと、複数のトランザクションの同時閲覧は可能なため
同じ伝票番号を取得していまう。そのため排他ロックを行う。
このテーブルロックは早い者勝ちなので、上図ではトランザクションT1が
先にロックをかける。そして、伝票番号を取得する。

トランザクションT2は、T1の終了待ちを行うため、
同じ伝票番号を取得する危険性を回避する事ができる。

文房具店などで文具品を販売する際、在庫のあるなしを把握する必要がある。
「入荷」があれば在庫数は増え、出荷(販売)すれば在庫数は減る。

トランザクション機能は使わず、単に出荷があれば、
上図のように、その度、SELECTで該当品目の在庫数を見て
在庫数に問題がなければ、UPDATEで更新作業を行っていた。

ほぼ同時刻に出荷依頼がある場合を考えてみる。
トランザクションT1は3本出荷、T2は4本出荷依頼を行う。

ロックがかかっていない場合、ほぼ同時刻に2つのトランザクションが
在庫確認のために在庫数を読み込むと、同じ数字を読み込んでしまう。
共に「在庫は5本」と認識するために、在庫はあると判断して
共に在庫数を出荷数だけ減少させる。
その結果、在庫切れに気づかず、在庫数が「-2」で計上される。

在庫数を確認すると同時に行ロックをかけてしまう。
SELECT文の後ろに「FOR UPDATE」をつければ良いのだ。
ロックは早い者勝ちなので、早くロックをした人が処理を進められる。

最初に処理を進めたトランザクションT1は出荷が3本なので、
在庫数は大丈夫なので出荷が認められる。そして在庫数が3本引かれる。
T1がCOMMITで終了した後、ロックが解除されるので、T2がロックをかけて
処理を進める。
読み込んだ在庫数が「2」なので、4本出荷する場合、2本足らなくなる。
そこで「在庫が足らない」という事で、出荷を断念するように
知らせる事ができる。