OSS-DB Silver 暗記 C-3

管理者 2021-03-172021-04-20 0 Comments OSS-DB Silver

Contents

1 異常読取（リード）
2 トランザクション分離レベル
- 2.1 SET TRANSACTION
3 行ロック
- 3.1 SELECT
4 テーブルロック
- 4.1 LOCK
5 デッドロック
- 5.1 共有:

開発/SQL（32%）：：：トランザクションの概念【重要度：1】dokoQL可

PostgreSQL 11.5文書：トランザクション

dokoQL で実習が可能です（分離レベル、デッドロック除く）

ACID属性(あしっど)

Wikipedia：ACID（コンピュータ科学）

トランザクション：DBにおいて、データに対する一つの論理的操作のこと
ACID属性：信頼性あるトランザクションシステムが持つべき性質

Atomicity（原子性）：トランザクション中の全タスクが100%実行されるか、0%実行されるかのどちらか
Consistency（整合性）：トランザクション開始時と終了時に予め決められた整合性を満たす
Isolation（分離性）：トランザクション中に行われる操作の過程が他の操作から隠蔽される
Durability（持続性）：トランザクションが完了したら、作業結果は持続して失われない

暗記

原子性：トランザクション実行は 100% か 0% のどちらかのみ
- A口座からB口座への銀行振込は、成功するか失敗するかのどちらかのみ
整合性：トランザクション開始・終了時にルールで決められた整合性を満たす
- A口座からB口座への銀行振込後：A口座はマイナスではない（ルール）
  - プラス　の場合：振込成功
  - マイナスの場合：振込失敗
分離性：トランザクション1 とトランザクション2 で別の変数を使う
- 同じ変数（ex. 振込額）を使うと値が上書きされ、間違った結果になる
  - トランザクション1：A口座からB口座への銀行振込 → 振込額_A_to_B
  - トランザクション2：C口座からB口座への銀行振込 → 振込額_C_to_B
持続性：コミットされたトランザクションは必ず残る

※分離性の「別々のトランザクションで同じ変数を使う」は比喩です

※プログラマーはこの比喩でピンとくる（暗記しやすい）と思います

トランザクションの構文

PostgreSQL 11.5文書：SQLコマンド：SAVEPOINT

例：BEGIN と COMMIT

=> CREATE TABLE table1 (x int);
   BEGIN;
       INSERT INTO table1 VALUES (1);
   COMMIT;
   SELECT * FROM table1;
---------
    x
---------
    1
---------

例：ROLLBACK

=> BEGIN;
       INSERT INTO table1 VALUES (2);
   ROLLBACK;
   SELECT * FROM table1;
---------
    x
---------
    1
---------

例：SAVEPOINT

=> BEGIN;
       INSERT INTO table1 VALUES (2);
   SAVEPOINT savepoint1;
       INSERT INTO table1 VALUES (3);  -- skip
   ROLLBACK TO SAVEPOINT savepoint1;
       INSERT INTO table1 VALUES (4);
   COMMIT;
   SELECT * FROM table1;
---------
    x
---------
    1
---------
    2
---------
    4
---------

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

PostgreSQL 11.5文書：第13章同時実行制御：13.2. トランザクションの分離

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

Qiita：ダーティリード、リピータブルリード、ファントムリードをちゃんと理解してからトランザクション分離レベルを理解しよう

異常読取（リード）

暗記

読取異常	他トランザクションの可読データ
ダーティ・リード	未コミットデータ
ノンリピータブル・リード	コミット済データ（更新結果）
ファントム・リード	コミット済データ（挿入/削除結果）

ダーティ・リード（汚れ）
- 同時実行している他トランザクションの未コミットデータ（ダーティデータ）が読めること
ノンリピータブル・リード（反復不可）
- 同時実行している他トランザクションのコミット済データ（更新結果）が読めること
  - 現象：反復すると値が変わる（反復できない）
  - 原因：他トランザクションのデータ更新
ファントム・リード（幽霊）
- 同時実行している他トランザクションのコミット済データ（挿入/削除結果）が読めること
  - 現象：何回か読込みすると、前回は存在しなかった行が現れる
  - 原因：他トランザクションのデータ挿入
  - 現象：何回か読込みすると、前回は存在していた行が消える
  - 原因：他トランザクションのデータ削除

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

暗記

分離レベル	発生する異常読取
READ UNCOMMITTED	ダーティ・ノンリピータブル・ファントム
READ COMMITTED	ノンリピータブル・ファントム
REPEATABLE READ	ファントム
SERIALIZABLE

READ UNCOMMITTED（未確定データを読む）
- トランザクション中に、他トランザクションの未確定データが読める
  - 発生する異常読取（ダーティ・ノンリピータブル・ファントム）
READ COMMITTED（確定済データを読む）
- トランザクション中に、他トランザクションの確定済データが読める
  - 発生する異常読取（ノンリピータブル・ファントム）
REPEATABLE READ（反復可能）
- トランザクション中に、同じデータは何度読んでも同じ値になる
  - 発生する異常読取（ファントム）
SERIALIZABLE（直列化可能）
- 同時動作の複数トランザクションの結果が、それらトランザクションを時間的重なりなく逐次実行した結果と同じになる
  - 発生する異常読取（なし）

SET TRANSACTION

PostgreSQL 11.5文書：SQLコマンド：SET TRANSACTION

SET TRANSACTION ISOLATION LEVEL transaction_mode;
- transaction_mode：{ SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }

例：SHOW TRANSACTION_ISOLATION

postgres=# show transaction_isolation;
-----------------------
 transaction_isolation
-----------------------
 read committed
-----------------------

例：SET TRANSACTION

postgres=# begin;
postgres=#     set transaction isolation level serializable;
postgres=#     show transaction_isolation;
-----------------------
 transaction_isolation 
-----------------------
 serializable 
-----------------------
postgres=# commit;
postgres=# show transaction_isolation;
-----------------------
transaction_isolation 
-----------------------
 read committed
-----------------------

ロック

行ロック

SELECT

PostgreSQL 11.5文書：SQLコマンド：SELECT

SELECT column_name FROM table_name WHERE condition FOR { UPDATE | SHARE };
- 排他ロック：FOR UPDATE
- 共有ロック：FOR SHARE

例：行ロック

口座（accounts）、口座番号（account_number）、残高（balance）

postgres=# SELECT * FROM accounts;
---------+---------
 acctnum | balance
---------+---------
   11111 |     100
   22222 |     200
---------+---------
postgres=# BEGIN;
postgres=#    SELECT * FROM accounts WHERE acctnum=11111 FOR UPDATE;
---------+---------
 acctnum | balance 
---------+---------
   11111 |     100
---------+---------
postgres=#

テーブルロック

LOCK

PostgreSQL 11.5文書：SQLコマンド：LOCK

LOCK [TABLE] table_name [ IN lockmode MODE ];
- lockmode：{ EXCLUSIVE MODE | ACCESS EXCLUSIVE MODE }
  - EXCLUSIVE：select のみ許可
  - ACCESS EXCLUSIVE：全ての処理を不許可
- lockmode 省略時は ACCESS EXCLUSIVE MODE になる

例：テーブルロック

postgres=# SELECT * FROM table1;
---------
    x
---------
    1
---------
postgres=# BEGIN;
postgres=#     LOCK table1;

暗記

LOCK
- EXCLUSIVE：排他的（select 許可）
- ACCESS EXCLUSIVE：より排他的（全て不許可）

デッドロック

PostgreSQL はデッドロックを自動検知してトランザクションをアボートする

例：デッドロック

口座（accounts）、口座番号（account_number）、残高（balance）

➀=# SELECT * FROM accounts;
---------+---------
 acctnum | balance
---------+---------
   11111 |     100
   22222 |     200
---------+---------
➀=# BEGIN;
➋=# BEGIN;
➀=#    UPDATE accounts SET balance=balance+100 WHERE acctnum=11111;
➋=#    UPDATE accounts SET balance=balance+100 WHERE acctnum=22222;
➋=#    UPDATE accounts SET balance=balance-100 WHERE acctnum=11111;
➀=#    UPDATE accounts SET balance=balance-100 WHERE acctnum=22222;
➀ERROR: deadlock detected     -- デッドロック検知：アボート
➀=# ROLLBACK;
➋=# COMMIT;
➀=# SELECT * FROM accounts;
---------+---------
 acctnum | balance
---------+---------
   11111 |      0
   22222 |    300
---------+---------