勉強日記

チラ裏

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

www.shoeisha.co.jp


トランザクションの概念

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

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

ACID特性について

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

BEGIN / START TRANSACTION / COMMIT / ROLLBACK / ABORT

postgres=# \echo :AUTOCOMMIT
on
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 Fuzzy
(Unrepeatable Read)
Phantom Serialization Anomaly
Read Uncommitted
Read Committed 起こらない
Repeatable Read 起こらない 起こらない
Serializable 起こらない 起こらない 起こらない 起こらない

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)
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

ロック

  • 更新処理時の矛盾を避けるための排他処理
  • 公式
    • いっぱいある

行ロック

| 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;

テーブルロック

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
  • あまり積極的に使うものではない
    • ALTER TABLEで暗黙的にACCESS EXCLUSIVEが取得されたりする

デッドロック