D.Horiyama's output

OSS-DB試験対策 緑本 ch10 トランザクション

OSS-DBPostgreSQL勉強メモ

出典: 

トランザクションの概念

トランザクションについて

  • 複数の処理をグループ化して論理的に1つの処理とするやつ

ACID特性について

  • 略

トランザクションを制御するコマンド

BEGIN / START TRANSACTION / COMMIT / ROLLBACK / ABORT

  • 公式/BEGIN

  • 同義語

    • BEGINとSTART TRANSSACTION

    • ROLLBACKとABORT

      • 【補】トランザクション自体はRDBMSの専売特許ではなく、独立した分野

        • トランザクション分野では「ABORT」と呼ぶ。はず。

  • BEGINを行わないデフォルトではautocommitモード

    • 毎文自動COMMIT
postgres=# \echo :AUTOCOMMIT
on

  • トランザクションブロック内での処理のほうが速い

    • 毎回トランザクションを開始・コミットするのはCPU,IOを食う
  • COMMIT
postgres=# CREATE TABLE tbl (c1 int);
CREATE TABLE

postgres=# BEGIN;
BEGIN

postgres=# INSERT INTO tbl VALUES (1);
INSERT 0 1

postgres=# SELECT * FROM tbl;
 c1 
----
  1
(1 row)

postgres=# COMMIT;
COMMIT


postgres=# SELECT * FROM tbl;
 c1 
----
  1
(1 row)
  • ROLLBACK
postgres=# BEGIN;
BEGIN

postgres=# INSERT INTO tbl VALUES (2);
INSERT 0 1

postgres=# SELECT * FROM tbl;
 c1 
----
  1
  2
(2 rows)

postgres=# ROLLBACK;
ROLLBACK


postgres=# SELECT * FROM tbl;
 c1 
----
  1
(1 row)

SAVEPOINT

  • 公式
  • 部分的にROLLBACKしたいときに使用

  • 同名のSAVEPOINTはstackされる

    • RELEASEでpopする
postgres=# BEGIN;
BEGIN

postgres=# INSERT INTO tbl VALUES(1);
INSERT 0 1

postgres=# SAVEPOINT sp1;
SAVEPOINT

postgres=# INSERT INTO tbl VALUES(2);
INSERT 0 1

postgres=# SAVEPOINT sp1;
SAVEPOINT

postgres=# INSERT INTO tbl VALUES(3);
INSERT 0 1

postgres=# SELECT * FROM tbl;
 c1 
----
  1
  2
  3
(3 rows)
  • sp1まで戻す
postgres=# ROLLBACK TO sp1;
ROLLBACK

postgres=# SELECT * FROM tbl;
 c1 
----
  1
  2
(2 rows)

  • ここで(新しいほうの)sp1に戻す:何も起きない

    • すでにsp1にいるから
postgres=# ROLLBACK TO sp1;
ROLLBACK

postgres=# SELECT * FROM tbl;
 c1 
----
  1
  2
(2 rows)
  • sp1をpopして、古いsp1に戻す
postgres=# RELEASE sp1;
RELEASE

postgres=# ROLLBACK TO sp1;
ROLLBACK

postgres=# SELECT * FROM tbl;
 c1 
----
  1
(1 row)
  • トランザクション全体を戻す
postgres=# ROLLBACK;
ROLLBACK

postgres=# SELECT * FROM tbl;
 c1 
----
(0 rows)

トランザクション分離性

  • ACIDのIの話

    • 分離性レベルに応じてトランザクションがどのような振る舞いを見せるか
    • 分離性の設定はどう行うか

トランザクションの分離レベル

phenomina

  • Dirty Read

    • 他トランザクションの未コミットのCUDが見える

  • Fuzzy Read

    • 他トランザクションのコミット済のUDが見える

  • Phantom Read

    • 他トランザクションのコミット済のCが見える

  • Serialization Anomaly

    • トランザクションを1つずつ重ならないように(直列に)実行するいずれの場合とも結果が一致しない
Dirty Fuzzy
(Unrepeatable Read)		 Phantom Serialization Anomaly
Read Uncommitted
Read Committed 起こらない
Repeatable Read 起こらない 起こらない
Serializable 起こらない 起こらない 起こらない 起こらない
  • PostgreSQLのデフォルトはRead Committed

postgres.conf

#default_transaction_isolation = 'read committed'

分離レベルの設定

postgres=# SELECT name,setting,enumvals,context FROM pg_settings WHERE name~'transaction_isolation';
             name              |    setting     |                               enumvals                               | context 
-------------------------------+----------------+----------------------------------------------------------------------+---------
 default_transaction_isolation | read committed | {serializable,"repeatable read","read committed","read uncommitted"} | user
 transaction_isolation         | read committed | {serializable,"repeatable read","read committed","read uncommitted"} | user
(2 rows)
  • 一般ユーザのSET文で変更可能
  • transaction_isolation: トランザクション内のみ
postgres=# SET transaction_isolation TO 'serializable';
SET

postgres=# SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 serializable
(1 row)

postgres=# COMMIT;
COMMIT

postgres=# SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)
  • デフォルト値はトランザクション外で設定
postgres=# SET default_transaction_isolation = 'serializable';
SET

postgres=# SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 serializable
(1 row)

postgres=# SHOW default_transaction_isolation;
 default_transaction_isolation 
-------------------------------
 repeatable read
(1 row)
  • トランザクション開始時に分離性レベルを指定できる
postgres=# BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

postgres=# SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

分離性による振る舞いの違い

  • SERIALIZABLEの場合、直列化異常が起きうるような更新はエラーが出る
(tx1)
postgres=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
(tx2)
postgres=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN

postgres=# SELECT * FROM tbl WHERE c1 = 1;
 c1 
----
  1
(1 row)
(tx1)
postgres=# UPDATE tbl SET c1 = 0 WHERE c1 = 1;
UPDATE 1

postgres=# COMMIT;
COMMIT;
(tx2)
postgres=# UPDATE tbl SET c1 = c1 - 1 WHERE c1 = 1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
  • 他トランザクションですでにCOMMITされたものをABORTできないので後発のトランザクションがABORTされる

ロック

  • 更新処理時の矛盾を避けるための排他処理

  • 公式

    • いっぱいある

行ロック

| Requested\Current | For Key Share | For Share | For No Key Update | For Update | | For Key Share | | | | X | | For Share | | | X | X | | For No Key Update | | X | X | X | | For Update | X | X | X | X |

  • 「key」はFKのこと

  • 直列化異常を防ぐやつ

    • SERIALIZABLEならば、そもそもロックが必要な更新をするとエラーになる
BEGIN;

SELECT col FROM tbl FOR UPDATE;
...

COMMIT;
  • ロック取得後、COMMITまたはROLLBACKするまで他のトランザクションから更新されない

テーブルロック

Command:     LOCK
Description: lock a table
Syntax:
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]

where lockmode is one of:

    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

URL: https://www.postgresql.org/docs/12/sql-lock.html

  • EXCLUSIVE

    • CUDブロック

  • ACCESS EXCLUSIVE

    • CRUDブロック

  • あまり積極的に使うものではない

    • ALTER TABLEで暗黙的にACCESS EXCLUSIVEが取得されたりする

デッドロック

  • PostgreSQLではデッドロックを自動検出し、当該トランザクションをABORTして自動回復する