postgreSQL その35 組込関数と演算子 その3のつづき
postgreSQL その8 課題リストは2018/4/30時点でのオイラの所見
[P.312]
１．座学
【トランザクション】
DBMSでは、複数のSQL処理をBEGINやCOMMITなどのコマンドで囲み、グループ化することができる。 このグループ化した1つの処理をトランザクションという。
たとえば、銀行振り込みでは「片方の口座残高から振込額を引く」、「片方の口座残高に振込額を足す」という2つの処理（SQL）が行われている。
この2つの処理をトランザクションとすることで、論理的に1つの処理とすることができる。
【ACID特性】
トランザクションは、下記に示すACID特性と呼ばれる4つの特性を持つ。
ACIDとは、トランザクションの４つの特性を満たすためにDBMSは様々な機能を持っている。
- Atomicity （原子性）
トランザクションは実行が完了するか、あるいはまったく実行されないかのどちらかとなる
- Consistency （整合性）
トランザクションの開始と終了時には、データベースは整合性を保った状態となっている
- Isolation （分離性）
トランザクションは別のトランザクションによる処理の影響を受けない
- Durability （持続性）
トランザクションにより変更されたデータは確実に保持される
・原子性の役割
口座振り込みの例では振込元の口座残高から振込金額が引かれただけの中途半端な状態になることを防ぐ
・整合性の役割
口座振り込みの例では振込元の口座残高以上の振込を許すことで口座残高がマイナス値になってしまうようなデータ定義で許されていない状態になることを防ぐ
・分離性の役割
一つの口座に同時に複数の振込が行われたとき、最初の振込額を後の振込額で上書きしてしまうような干渉を防ぐ
・持続性の役割
２．トランザクションを制御するコマンド
【トランザクションを開始したり終了したりするためのコマンド】
・トランザクションの開始を制御するコマンドは、BEGIN と START TRANSACTION
※BEGIN と START TRANSACTION は同義。
・トランザクションの終了を制御するコマンドは、COMMIT、ROLLBACK、ABORT
処理を確定する場合はCOMMIT、ENDもしくはEND TRANSACTIONを発行する。
※COMMIT、ENDもしくはEND TRANSACTIONは同義。
※ENDを用いた方法はPostgreSQL独自の構文であり、用いられることは少ない。
・途中でエラーや不備があって、トランザクションを開始する前の状態に戻す場合は、
ROLLBACK もしくは、ABORT を発行する。
※ROLLBACK と ABORT は同義。
・DBMSが稼働しているマシンなどがクラッシュした場合、ROLLBACKは発行されないが、実行中のトランザクション処理は取り消される。
・PostgreSQLでは、トランザクション中にSQLなどがエラーとなったり、セッションの切断が発生した場合は、その時点でトランザクションはABORT扱いとなる。エラーとなったSQL以降の処理は、ABORTまたはROLLBACKが実行されるまで、SQLを実行しても無視される。
・BEGIN と COMMIT に挟まれた処理が一つのトランザクションになる。
・PostgreSQLの操作で多用するpsqlでは、デフォルトでautocommitが有効になっている。
autocommitが有効な場合、BEGINやCOMMITを明示的に発行しないと、SQLの実行前にBEGINが暗黙的に発行され、SQLがエラーなく終了すれば自動でSQLの実行結果がコミットされる。BEGINが明示的に発行された場合は、COMMITが発行されるまでトランザクションがコミットされることはない。
ミッションクリティカルな処理の場合は、データ消失を防ぐためロールバックできるように、明示的にBEGINを発行したほうがよい。
[書式]
begin;
・・・SQLなどによる処理・・・
commit;
または
rollback;
[例]
・トランザクションはネストできるか？
testdb=> begin;
BEGIN
testdb=> begin;
WARNING: there is already a transaction in progress
BEGIN
testdb=> rollback;
ROLLBACK
testdb=> rollback;
WARNING: there is no transaction in progress
ROLLBACK
トランザクションの中でトランザクションを開始できない（トランザクションをネストできない）。
・DDLのCOMMITとROLLBACK
・rollback
testdb=> begin;
BEGIN
testdb=> create table tb1 (c1 int);
CREATE TABLE
testdb=> rollback;
ROLLBACK
testdb=> \d tb1
Did not find any relation named "tb1".
・commit
testdb=> begin;
BEGIN
testdb=> create table tb1 (c1 int);
CREATE TABLE
testdb=> commit;
COMMIT
testdb=> \d tb1
Table "punisuke.tb1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | integer | | |
・DMLのCOMMITとROLLBACK
・commit
testdb=> begin;
BEGIN
testdb=> insert into tb1 values(1);
INSERT 0 1
testdb=> select * from tb1;
c1
----
1
(1 row)
testdb=> commit;
COMMIT
testdb=> select * from tb1;
c1
----
1
(1 row)
testdb=> begin;
BEGIN
testdb=> insert into tb1 values (2);
INSERT 0 1
testdb=> select * from tb1;
c1
----
1
2
(2 rows)
testdb=> rollback;
ROLLBACK
testdb=> select * from tb1;
c1
----
1
(1 row)
・DCLのCOMMITとROLLBACK
・トランザクション中のSQLエラー
testdb=> begin;
BEGIN
testdb=> select * from tb1;
c1
----
1
(1 row)
testdb=> insert into tb1 values (2);
INSERT 0 1
testdb=> select * from tb1;
c1
----
1
2
(2 rows)
testdb=> insert into うんこ values (3); ←文法間違いのSQLを起こす
ERROR: relation "うんこ" does not exist
LINE 1: insert into うんこ values (3);
^
testdb=> insert into tb1 values (3);
ERROR: current transaction is aborted, commands ignored until end of transaction block
testdb=> abort;
ROLLBACK
testdb=> select * from tb1;
c1
----
1
(1 row)
testdb=> insert into tb1 values (3);
INSERT 0 1
testdb=> select * from tb1;
c1
----
1
3
(2 rows)
【SAVEPOINT】
SAVEPOINTを使うことで、トランザクション中の特定の時点へ戻ることができる。
SAVEPOINTは、トランザクション中に部分的なロールバックをしたい場合に使用する。
任意の名称のSAVEPOINTをトランザクション中に作成し、必要があればその時点までロールバックできる。
（ただし、SAVEPOINTを実行したセッションが終了するタイプのエラーなど、SAVEPOINTから再開できない場合もある。）
また、１つのトランザクション内で多数のSQLを実施する場合、どこかでABORTしてしまった場合には最初から処理をやり直さなければならない。 それを回避する際にSAVEPOINT機能は有用。
SAVEPOINTの書式は下記の通り
[書式]
savepoint セーブポイント名;
rollback to 戻る時点のセーブポイント名;
・「セーブポイント」には数値と文字列を使用できる。
・SAVEPOINTは複数作成することができ、任意の点に状態を戻すことができる。
・不要となったSAVEPOINTは、RELEASE SAVEPOINTで削除することができる。
[書式]
release savepoint セーブポイント名;
トランザクション内で同じセーブポイント名を設定した場合、一時的に古い（先に設定した）方を上書きする。 その後、新しい方のSAVEPOINTをRELEASEすると、再び古いSAVEPOINTを利用できるようになる。
[例]
testdb=> begin;
BEGIN
testdb=> select * from tb1;
c1
----
1
3
(2 rows)
testdb=> insert into tb1 values (2);
INSERT 0 1
testdb=> savepoint sp1;
SAVEPOINT
testdb=> insert into tb1 values (4);
INSERT 0 1
testdb=> select * from tb1;
c1
----
1
3
2
4
(4 rows)
testdb=> rollback to sp1;
ROLLBACK
testdb=> select * from tb1;
c1
----
1
3
2
(3 rows)
testdb=> insert into tb1 values (4);
INSERT 0 1
testdb=> commit;
COMMIT
testdb=> select * from tb1;
c1
----
1
3
2
4
(4 rows)
・エラーとSAVEPOINT
testdb=> begin;
BEGIN
testdb=> select * from tb1;
c1
----
1
(1 row)
testdb=> insert into tb1 values (2);
INSERT 0 1
testdb=> savepoint sp1;
SAVEPOINT
testdb=> insert into ちんこ values (3);
ERROR: relation "ちんこ" does not exist
LINE 1: insert into ちんこ values (3);
^
testdb=> rollback to sp1;
ROLLBACK
testdb=> select * from tb1;
c1
----
1
2
(2 rows)
testdb=> commit;
COMMIT
testdb=> select * from tb1;
c1
----
1
2
(2 rows)
・複数のSAVEPOINT
testdb=> begin;
BEGIN
testdb=> select * from tb1;
c1
----
1
2
(2 rows)
testdb=> savepoint sp1;
SAVEPOINT
testdb=> insert into tb1 values (3);
INSERT 0 1
testdb=> savepoint sp2;
SAVEPOINT
testdb=> insert into tb1 values (4);
INSERT 0 1
testdb=> savepoint sp3;
SAVEPOINT
testdb=> insert into tb1 values (5);
INSERT 0 1
testdb=> select * from tb1;
c1
----
1
2
3
4
5
(5 rows)
testdb=> rollback to sp2;
ROLLBACK
testdb=> select * from tb1;
c1
----
1
2
3
(3 rows)
testdb=> commit;
COMMIT
testdb=> select * from tb1;
c1
----
1
2
3
(3 rows)
testdb=> release savepoint sp2;
ERROR: RELEASE SAVEPOINT can only be used in transaction blocks
【課題】
・