DUCTION はじめての人のためのトランザクション入門 TO INTRO- TRANS- 日本 PostgreSQL ユーザ会第 35 回 PostgreSQL 勉強会 2017 年 5 月 27 日 ACTION 坂田哲夫 (NTT OSS センタ ) 1

Size: px

Start display at page:

Download "DUCTION はじめての人のためのトランザクション入門 TO INTRO- TRANS- 日本 PostgreSQL ユーザ会第 35 回 PostgreSQL 勉強会 2017 年 5 月 27 日 ACTION 坂田哲夫 (NTT OSS センタ ) 1"

まいかののした
4 years ago
Views:

1 DUCTION はじめての人のためのトランザクション入門 TO INTRO- TRANS- 日本 PostgreSQL ユーザ会第 35 回 PostgreSQL 勉強会 2017 年 5 月 27 日 ACTION 坂田哲夫 (NTT OSS センタ ) 1

2 この講演についてトランザクションという言葉を初めて聞く人にも分かるようにイチから説明しますトランザクションの基本を説明しますアプリケーションプログラムの開発の観点から重要なポイントを選びました標準 SQL を基準に PostgreSQL について説明します OSS-DB Silver の出題範囲をほぼカバーします細かい部分は省略しています最後に紹介する参考文献などで補ってください 2

3 もくじトランザクションの必要性と役割 4 トランザクションの使い方 19 トランザクションの分離 30 ロックとその利用 40 デッドロックとその対策 49 まとめ 58 参考文献 62 この資料について ( ライセンス等 ) 64 3

4 DUCTION TO トランザクションの必要性と役割 INTRO- TRANS- ACTION 4

5 データベースの必要性と役割同時実行制御の必要性トランザクションがなかったら典型的な同時実行の異常トランザクションによる異常の防止 5

6 データベースの働きとトランザクション同時実行制御の必要性効率化のため同時に複数の問い合わせを処理したいその際に異常が生じないように制御するトランザクションは同時実行制御の単位 AP( アプリケーションプログラム ) が実行する複数の処理をトランザクションにまとめて同時実行制御の単位とする 6

7 トランザクションがなかったらトランザクションなしで SQL 文だけで AP を構成したらどうなるかを考えてみよう同時に処理を実行すると異常な結果が生じることがある同時実行の異常の典型的なパターンを紹介 7

8 トランザクションがなかったら~ 同時実行の異常完了しない状態のデータが残ってしまう *1 一貫性がないデータを読めてしまう *2 不正な更新データが書き込まれてしまう *3 *1: 同時実行の異常ではありませんが重要なのでここで紹介します *2:dirty read ( 汚れのある読み出し ) と呼びます *3:lost update / loss of update ( 更新の消失 ) と呼びます : ここに挙げたもの以外に non-repeatable read ( 非再現読み出し ) があります 8

9 例題について銀行の例で考える口座同士の送金の例は次の通り口座 A から 1 万円引き出す口座 B へ 1 万円振り込む 1. 口座 A の残高を読み出し 2. 引きだし後の残高を計算し 3. その残高を口座 A に書き込む 1. 口座 B の残高を読み出し 2. 振込後の残高を計算し 3. その残高を口座 B に書き込むここでの一貫性はつじつまが合っていることお金が勝手に消えたりどこかから出てきたりしない 2 つの口座の間での送金の場合送金の前後でそれら口座の残高は等しい 9

10 異常 1: 完了しない状態のデータが残ってしまう口座 A から B へ 1 万円送金 1. 口座 A から 1 万円引き出す 2. 口座 B へ 1 万円振り込む 1 の SQL 完了直後に AP が停止すると口座 A から 1 万円を引き出したが口座 B に振り込まれていない状態になる 1 万円が消えてしまう 10

11 時間は下向き異常 1: 完了しない状態のデータが残ってしまう口座 A から B へ 1 万円送金口座 A 口座 B 残高を読み出す (10 万円 ) 引落後の残高を計算 (9 万円 ) 残高 (9 万円 ) を書き込む 10 万円 9 万円 10 万円ここで処理が中断すると不正な結果が残ってしまう残高を読み出す (10 万円 ) 振込後の残高を計算 (11 万円 ) 残高 (11 万円 ) を書き込む 10 万円 11 万円口座の残高合計が一致しない AP 凡例 APでの操作データ項目その値 11

12 異常 2: 一貫性がないデータが読めてしまう口座 A から B へ 1 万円送金 1. 口座 A から 1 万円引き出す 2. 口座 B へ 1 万円振り込む 1 の SQL 完了後 2 の SQL の完了前に別の AP で口座 A と B の残高合計を検索するとあるべき金額に対して 1 万円少ない額になる 12

13 異常 2: 一貫性がないデータが読めてしまう端末 1 口座 AからBへ端末 2 口座 AとBの 1 万円送金口座 A 口座 B 合計を照会残高を読み出す (10 万円 ) 引落後の残高を計算 (9 万円 ) 残高 (9 万円 ) を書き込む残高を読み出す (10 万円 ) 振込後の残高を計算 (11 万円 ) 残高 (11 万円 ) を書き込む 10 万円 9 万円 9 万円 10 万円 10 万円 11 万円残高を読み出す (10 万円 ) 残高を読み出す (9 万円 ) 残高の合計を計算 (19 万円 ) 口座の残高合計が一致しない 13

14 異常 2 の実行例 psqlを使って次の操作を順に実行端末 1で口座 Aから1 万円引き出す ( 前ページ左の列 ) 端末 2でaccount 表の内容を見る ( 前ページ右の列 ) 端末 1 端末 2 test=> select * from account ; id customer balance Mr.A Mr.B Mr.C (3 行 ) test=> UPDATE account set balance = WHERE id = 1; UPDATE 1 test=> select * from account ; id customer balance Mr.A Mr.B Mr.C (3 行 ) 送金途中の状態が見えてしまう送金のために 1 万円引き出した 14

15 異常 3: 不正な更新データが書き込まれてしまう口座 A から B へ 1 万円送金 1. 口座 Aから1 万円引き出す 2. 口座 Bへ1 万円振り込む口座 C から B へも 1 万円送金 3. 口座 Cから1 万円引き出す 4. 口座 Bへ一万円振り込む 1. 口座 A の残高を読み出し 2. 引きだし後の残高を計算し 3. その残高を口座 A に書き込む 1. 口座 B の残高を読み出し 2. 振込後の残高を計算し 3. その残高を口座 B に書き込む 1. 口座 C の残高を読み出し 2. 引きだし後の残高を計算し 3. その残高を口座 C に書き込む 1. 口座 B の残高を読み出し 2. 振込後の残高を計算し 3. その残高を口座 B に書き込む実行順序によっては不正な状態のデータが書き込まれる 15

16 異常 3: 不正な更新データが書き込まれてしまう口座 AからBへ口座 CからB 口座 A 1 万円送金口座 B へ口座 C 1 万円送金 10 万円 9 万円残高を読み出す (10 万円 ) 残高を読み出す (10 万円 ) 送金後の残高を計算 (11 万円 ) 残高 (11 万円 ) を書き込む引落後の残高を計算 (9 万円 ) 残高 (9 万円 ) を書き込む 10 万円 10 万円 11 万円 11 万円残高を読み出す (10 万円 ) 残高を読み出す (10 万円 ) 送金後の残高を計算 (11 万円 ) 残高 (11 万円 ) を書き込む引落後の残高を計算 (9 万円 ) 残高 (9 万円 ) を書き込む 10 万円 9 万円口座の残高合計が一致しない 16

17 同時実行の異常を防止する異常 1 一連の処理が完了した上で DB に記録を残すようにする異常 2 と異常 3 各 AP の一連の処理を続けて処理する ( 処理と処理の間に他の AP の処理が割りこまない ) AP の一連の処理をまとめて ( 中途半端で終わらずに割り込まれずに ) 実行するというところがポイント 17

18 異常 3 の防止 ( イメージ図 ) 口座 AからBへ口座 A 1 万円送金口座 B 口座 C 10 万円 9 万円残高を読み出す残高を読み出す残高を書き込む残高を書き込む 10 万円 11 万円 2 つの AP から DB への操作がそれぞれひとかたまりになっている直列化した実行 11 万円 12 万円口座 C から B へ 1 万円送金残高を読み出す残高を読み出す残高を書き込む残高を書き込む 10 万円 9 万円 18

19 DUCTION TO トランザクションの使い方 INTRO- TRANS- ACTION 19

20 トランザクションの使い方 APからみたトランザクショントランザクションの特性トランザクションの書き方トランザクションの例エラーとその対策ー SAVEPOINT 20

21 AP からみたトランザクション一貫した処理を保証するためのデータベース処理の集まり具体的には AP でここからトランザクションが始まると宣言した位置からここでトランザクションが終わると宣言する位置までの間の SQL 文の集まり終わりの宣言は完了したので内容を確定せよ (commit) と完了せずに内容を破棄せよ (rollback) の 2 つがある開始の宣言は省略できない *1 ( 何も宣言せずに SQL 文を実行すると Transaction が始まりその SQL 文の終了とともに commit/rollback する ) *1:PostgreSQL の独自仕様でそうなっている標準 SQL では START TRANSACTION なしでトランザクションを開始できる 21

22 トランザクションの特性更新データは処理が完了した状態でのみ保存できる (A) 完了前であれば更新データを実行前の状態に巻き戻す一貫性のあるデータの読み書きを保証する (C) 同時に 1 つのトランザクションだけが実行しているように見える (I) 書き込んだデータは確実に保存する (D) これらの性質をまとめて ACID 性と呼ぶ Atomicity ( 原子性 ) Consistency ( 一貫性 ) Isolation ( 分離性または隔離性 ) Durability ( 持続性または永続性 *1) *1:durability を永続性と訳す例があるが persistence と区別がつかないので良い訳でないと思う 22

23 トランザクションの書き方一貫性を持たせたい一連の処理を BEGIN *1 または START TRANSACTION と COMMIT の間に書く COMMIT によってトランザクションでの DB への更新が確定する途中で打ち切る場合は COMMIT に代えて ROLLBACK する DB への更新は全て取り消される BEGIN または START TRANSACTION なしで SQL を発行すると 1SQL が 1 トランザクションとなる *2 先に説明した異常が生じることがある *1:PostgreSQL 独自のコマンド *2:PostgreSQL 独自仕様による振舞い標準 SQL では START TRANSACTION なしでトランザクションを開始できる 23

24 トランザクションの例異常 2 を防ぐためにトランザクションを用いる 1 端末 1 で口座 A から 1 万円引き出す ( こちらはトランザクション内で実行する ) 2 端末 2 で account( 口座 ) 表の内容を見る端末 1 端末 2 トランザクション開始 test=> begin; BEGIN test=> SELECT * FROM account ; id customer balance Mr.A Mr.B Mr.C (3 行 ) test=> UPDATE account set balance = WHERE id = 1; UPDATE 1 1 送金のために 1 万円引き出した id: id: 口座番号 ( キーキー ) Customer : 顧客名 Balance: 残高残高 test=> select * from account ; id customer balance Mr.A Mr.B Mr.C (3 行 ) 2 送金途中の状態が見えない ( 送金前の状態が見える ) 24

25 トランザクションの例 ( 続き ) トランザクションがコミットすると結果が見える 3 端末 1 で口座 B に 1 万円振り込む 4 コミットする 5 端末 2 で account 表の内容を見る端末 1 端末 2 test=> UPDATE account set balance = WHERE id = 2; UPDATE 1 3 口座 B へ振込 test=> COMMIT; COMMIT 4 コミットして送金を完了 test=> select * from account order by id; id customer balance Mr.A Mr.B Mr.C (3 行 ) 5 送金が完了した状態が見える 25

26 トランザクションとエラートランザクション内でSQL 文がエラーとなった場合後続するSQL 文は全て無条件にエラーとなる commitを実行しても実際には rollback されるこの振舞いは PostgreSQL 特有のものです他の DBMS では振る舞いが異なることがあります ( 参考文献 [3] を見てください ) 26

27 トランザクションとエラー ( 実行例 ) test=> begin; BEGIN test=> select * from account ; id customer balance Mr.A Mr.B Mr.C (3 行 ) トランザクションを開始して正常に動作することを確認する SQL SQLでエラーを起こした (id (idを重複させた ) test=> INSERT INTO account(id, customer, balance) VALUES (3, 'Mr.D', ); ERROR: 重複キーが一意性制約 "account_id_key" に違反しています DETAIL: キー (id)=(3) はすでに存在します test=> select * from account ; ERROR: 現在のトランザクションがアボートしましたトランザクションブロックが終わるまでコマンドは無視されます test=> commit; ROLLBACK test=> COMMIT コマンドでも ROLLBACK されるエラーすると TrX TrXはアボートされるエラー後のコマンドは無視される 27

28 SAVEPOINT と部分的ロールバック部分的ロールバックによって先に保存したセーブポイント名のところまで巻き戻すことができる巻き戻し後はトランザクションが継続できる SAVEPOINT セーブポイント名 ; エラーになった SQL ROLLBACK TO セーブポイント名 ; 指定したセーブポイント名まで処理を巻き戻す SAVEPOINT セーブポイント 1 ; SAVEPOINT セーブポイント 2 ; SAVEPOINT セーブポイント 3 ; : : : ROLLBACK TO セーブポイント 1 ; 複数のセーブポイントを保存することもできるあるセーブポイントまで巻き戻すとそれ以降のセーブポイントは全て無効となる 28

29 SAVEPOINT ( 実行例 ) test=> begin; BEGIN test=> INSERT INTO account(id, customer, balance) VALUES (4, 'Mr.D', ); INSERT 0 1 test=> SAVEPOINT SP1; SAVEPOINT test=> INSERT INTO account(id, customer, balance) VALUES (4, 'Mr.D', ); ERROR: 重複キーが一意性制約 "account_id_key" に違反しています SQL DETAIL: キー (id)=(4) はすでに存在します SQLでエラーを起こした (id (idを重複させた ) test=> ROLLBACK TO SP1; ROLLBACK セーブポイント SP1 SP1 に保存セーブポイント SP1 SP1 に巻き戻す顧客顧客 D が追加できる test=> select * from account ; id customer balance Mr.A Mr.B Mr.C Mr.D (4 行 ) test=> commit; COMMIT コミットも正常にできる再びトランザクションが実行できる 29

30 DUCTION TO トランザクションの分離 INTRO- TRANS- ACTION 30

31 トランザクションの分離分離の考え方の紹介分離レベル Read committed Repeatable read Serializable 31

32 トランザクションの分離の考え方分離性 (ACID の I): 複数のトランザクションを同時に実行しても異常が生じないこと一度に 1 つずつ ( 逐次的に ) トランザクションを実行した場合 *1 には同時実行処理による異常は生じない一度に 1 つずつ実行した場合と等価な結果を生じるトランザクションの実行順序を Serializable ( 直列化可能 ) という *2 直列化可能な実行順序であればトランザクションは分離されている *1: このようなトランザクションの実行を直列あるいは逐次と呼びます *2: トランザクションの実行そのものが直列化されていなくても実行結果が何らかの直列化したトランザクション列と同じになれば良い直列化可能の詳しい定義は参考文献 2 を見てください 32

33 分離レベル Serializable は最も厳密な分離を提供する反面同時に実行されるトランザクションの数が制限される分離の程度 ( レベル ) を下げれば多くのトランザクションを同時に実行することができる分離レベルを下げると同時実行に伴う異常が生じることがある PostgreSQL では以下の 3 つの分離レベルがある *1 Serializable ( 直列化可能 ) Repeatable read ( 反復可能読み取り ) Read committed ( 読み取り一貫 ) *1:PostgreSQL のマニュアルではそれぞれシリアライザブルリピータブルリードリードコミッティドと言います 33

34 Read committed SQL 文を開始する時点で他のトランザクションが変更してコミットしたデータの参照を許す自トランザクションの開始後のコミットの分も含む同じ行を 2 回読むと違った結果を返すことがある反復不能読み取りと呼ぶ読み込みトランザクションではファントムリードの可能性があるファントムリードの説明は次ページで直列化異常が生じることがあるコミットは成功するが直列化した実行順序では生じえない結果が生じる 34

35 Repeatable read 一度読み込んだ行の内容は再度読み込んでも同じである他のトランザクションが更新してコミットした後に同じデータを読み込んでも値は不変 PostgreSQL の場合 *1 参照のみのトランザクションであれば Serializable と同じ結果が保証される直列化の失敗 ( というエラー ) が生じることがある *1: 標準 SQL では行の集合としてみた場合再度読み込むと 1 回目にはなかった行が読み込まれることがある ( この現象をファントムリード (phantom read) と呼ぶ ) PostgreSQL の Repeatable read ではファントムリードは生じない ( 標準 SQL では許容する ) 35

36 直列化の失敗同時実行しているトランザクションが更新したデータを更新すると直列化の失敗というエラーとなる更新を含むトランザクションでのみ発生する TrX 1 口座 A TrX 2 BEGIN 10 万円 Repeatable read と Serializable の場合 TrX 1 はこの時点のデータを読み書きしているようにふるまう残高を読み込む 10 万円 BEGIN TrX 2 はこの時点を基準にふるまう 9 万円残高を読み込む 1 万円振り込む ( ロックで待たされる ) 1 万円引き出す COMMIT TrX 2 のコミット後に直列化の失敗エラーとなる 36

37 Serializable 直列化した実行順序と等価な結果を保証する反復不能読み取りファントムリード直列化異常のいずれも生じない Repeatable read と同様に直列化の失敗が起こることがある実行のためのコストが大きい 37

38 分離レベルの使い方トランザクションの開始時に宣言する BEGIN のあとで以下のコマンドを実行 SET TRANSACTION ISOLATION LEVEL 分離レベル... SERIALIZABLE, REPEATABLE READ, READ COMMITTED のどれかが入る BEGIN を使わない場合は以下のコマンドでトランザクションを開始する START TRANSACTION ISOLATION LEVEL 分離レベル... 上と同じ宣言しないとデフォルトの分離レベルになるマニュアルの SET TRANSACTION のページを参照 38

39 分離レベルのまとめ PostgreSQL の分離レベルと同時実行処理の異常は下表の通り分離レベルにはトレードオフがある下に行くほど安全になる ( 正しい結果が保証される ) 上に行くほど効率が高い ( スループットが出せる ) 分離レベル反復不能読み取りファントムリード直列化異常 READ COMMITTED REPEATABLE READ 可能性あり可能性あり可能性あり生じない生じない可能性あり SERIALIZABLE 生じない生じない生じない 39

40 DUCTION TO ロックとその利用 INTRO- TRANS- ACTION 40

41 ロックとその利用トランザクションの実現とロックロックの種類ーレベルとモードロックモード表レベルロックと行レベルロック明示的なロック 41

42 トランザクションの実現とロックトランザクションを実現する際にはロック (lock) という仕組みを使っている 2 人以上の人が同時にデータをアクセスできない仕組みあるデータが使用中 ( ロックされている ) なら後からアクセスしたトランザクションは待たされるロックにはいろんなバリエーションがあります ( 上記は一例です ) DBMS ではロックは全て自動的に獲得開放されるので AP 開発者は操作 ( プログラム ) しなくて良い PostgreSQL でも同様だが AP の都合によっては明示的にロックを獲得することもできるトランザクションは内部的にロックを獲得します不必要に長くならないように注意しましょう 42

43 ロックのレベルロックの種類 : レベルとモードロックの対象 (object) をレベルと呼びます表レベル行レベルが代表的 *1 ページレベルもある ( が説明は略 ) ロックのモード同時にアクセスを許可する ( ロックを獲得できる ) か否かロックによって 2 つ以上のトランザクションからのアクセスを無条件に禁止されると処理性能が下がる AP の要件によっては 2 つ以上のトランザクションからのアクセスを許可してよいこともある *1:PostgreSQL のマニュアルでは表レベルテーブルレベル行レベルはそのまま英語は row level lock. 43

44 ロックモードの基本 : 共有と専有共有と専有ロックモードの基本的考え方共有 (share): 複数のトランザクションでアクセスできる読み出しに使われる専有 (exclusive):1 つのトランザクションだけがアクセスできる書込みに使われるロックモードの表 ( マトリクス ) 現在あるロックモードでロックが獲得されている時に別のトランザクションがあるモードでロックを要求する時にその要求が許可されるか否かを表で表す共有専有という 2 つのモードのロックの両立性要求するロックモード ( 列 ) 現在のロックモード ( 行 ) share 共有 (share) 排他 (exclusive) exclusive 凡例 : ロックが獲得できる : ロックが獲得できない 44

45 表レベルのロック PostgreSQL の主な表レベルロック SQL 文を実行する際に所定のモードでロックを要求する要求するモードロックを必要とするコマンド現在のロックモード AS RX SUX S Access Share 通常の表の読み出し Row exclusive INSERT, DELETE, UPDATE Share Update exclusive 主なロックモードの両立性 ( 全部で 8 モードあるマニュアル 13.3 を参照 ) (FULL ではない ) VACUUM, ANALYZE Share CREATE INDEX この表を見ると次のようなことも分かる表の読み出しと INSERT, DELETE, UPDATE は同時に実行できる VACUUM 中には表の読み書きは可能だが CREATE INDEX することはできない 45

46 行レベルのロック同じ行に対する書き込みを制御する ( 参照時には要求しない ) FOR UPDATE SELECT~FOR UPDATE で読み出した行を更新用にロック FOR NO KEY UPDATE *1 通常の UPDATE 文で獲得 FOR SHARE *1 UPDATE 文 DELETE 文をブロック FOR KEY SHARE *1 FOR UPDATE をブロックする *1: これらのモードは PostgreSQL 固有要求するモード現在のロックモード KS S NKU U FOR KEYS SHARE FOR SHARE FOR NO KEY UPDATE FOR UPDATE 凡例 : ロックが獲得できる : ロックが獲得できない 46

47 明示的なロック PostgreSQL はそのトランザクションに必要なもっとも制約の少ないモードでロックを獲得する AP の都合上より制約が大きいロックの方が都合が良いことがある特定の表行を明示的に ( コマンドを使って ) ロックすることができるロックを獲得する際にはモードを指定する獲得したロックは Transaction 完了時 (Commit または Rollback) にまとめて開放される Transaction 途中で個別に開放することはできない 47

48 明示的な表ロックの例 Read Committed トランザクションの中で特定の表を反復して読み出し一貫した結果を得たい Read Commited では表の読み出し時に Access Share でロック他のトランザクションで同じ表を更新できるので繰り返し読み込みすると一貫した参照結果が得られない ( 反復不能読み取り ) 以下のコマンドで表ロックすると反復読み取りで一貫した結果が得られる *1 LOCK 表の名前 IN モード名 MODE *1:LOCK 文は PostgreSQL 固有この例では SHARE ( 参照のみ実行する場合 ) または SHARE ROW EXCLUSIVE( 参照更新する場合 ) を指定することで他のトランザクションによる表の変更が抑止できる 48

49 DUCTION TO デッドロックとその対策 INTRO- TRANS- ACTION 49

50 デッドロックとその対策デッドロックの説明と発生例デッドロックの原因デッドロックの対策予防とリトライ 50

51 デッドロックとは? ロックを使うことであるデータにアクセスするトランザクションが 1 つしかないことを保証する同じデータにアクセスする他のトランザクションは待たされる ( ブロックされる ) ロックしているトランザクションが完了するまでロックは開放されない 2 つのトランザクションが互いに相手がロックしているデータにアクセスしようとすると相手を永久に待つことになる *1 デッドロック (dead lock) と呼ぶ *1:3 つ以上のトランザクションの間でも同様にデッドロックが発生することがありますここでは 2 つのトランザクションの例で解説します 51

52 デッドロックの例 ( 単独実行の場合はうまくいく ) TrX 1 口座 B から A へ 1 万円送金口座 A 口座 B 排他ロックを獲得残高に 1 万円加える 11 万円 10 万円排他ロックを獲得残高を 1 万円減らす 9 万円排他ロックなので TrX 1 だけがアクセスできる排他ロックを開放排他ロックを開放 52

53 デッドロックの例 TrX 1 口座 BからAへ口座 AからBへ TrX 2 1 万円送金口座 A 口座 B 1 万円送金排他ロックを獲得排他ロックを獲得残高に 1 万円加える排他ロックを要求 11 万円 11 万円残高に 1 万円加える排他ロックを要求 TrX 2 が排他ロックしており待たされる TrX 1 が排他ロックしており待たされるお互いに相手がロックを開放するのを永遠に待つ 53

54 デッドロックの発生例 PostgreSQL はデッドロックを検出してデッドロックしているトランザクションをどれか 1 つアボートする 2 つのトランザクション ( 端末 ) で 1~4 の順で SQL を実行 4 を実行するとデッドロックが発生端末 1 端末 2 test=> begin; 11 BEGIN test=> update account set balance = balance where id=1; UPDATE 1 3 ロック待ち test=> update account set balance = balance where id=2; ERROR: デッドロックを検出しました DETAIL: プロセス 4374 は ShareLock をトランザクションで待機していましたがプロセス 3941 でブロックされましたプロセス 3941 は ShareLock をトランザクションで待機していましたがプロセス 4374 でブロックされました HINT: クエリーの詳細はサーバログを参照してください CONTEXT: while updating tuple (0,2) in relation "account" デッドロックが検出されてトランザクションがアボートされた test=> begin; 22 BEGIN test=> update account set balance = balance where id=2; UPDATE 1 test=> update account set balance = balance where id=1; UPDATE 1 test=> commit; COMMIT 4 ロック待ちここでデッドロックが発生 54

55 デッドロックの原因複数のトランザクションが同時実行されるときに a) 複数回に分けてロックを要求する b) それぞれのトランザクションで互いに逆順にロックを要求する TrX 1 TrX 2 口座 A のロックを要求口座 A 口座 B のロックを要求口座 B のロックを要求口座 B 口座 A のロックを要求デッドロック上の状況で生じるデッドロックを TrX の視点から見たもの ( 待機グラフ ) TrX 1 TrX 2 口座 B に関して TrX1 は TrX2 を待つ口座 A に関して TrX2 は TrX1 を待つ 55

56 デッドロックの対策 : 予防するデッドロックを生じないように AP を作成する a) 必要なロックは 1 度にまとめて要求する先の例では以下のように最初に SELECT して行ロックを獲得するという手法が利用できる SELECT * FROM account WHERE id = 1 or id =2 FOR UPDATE; 最初にアクセスする際に明示的な行ロックを用いてそのトランザクション内で更新する全ての行のロックを獲得する b) 全てのトランザクションで表や行へのロックの要求の順序を一定にする 56

57 デッドロックの対策 : リトライするデッドロックを生じることを前提に AP を作成する PostgreSQL はデッドロックを自動的に検出する検出するとデッドロックに含まれるトランザクションが 1 つエラーとなってアボートされるロックを要求した SQL がエラーで戻ってくるエラーコード (SQL STATE) は 40P01 (deadlock_detected) デッドロック時はロールバックして再実行 ( リトライ ) する全てのデッドロックを予防することは困難であるからリトライの仕組みを用意すると良い 57

58 DUCTION TO 今日のまとめ INTRO- TRANS- ACTION 58

59 まとめ (1/2) 一貫した DB 操作のためにはトランザクションが必要 ACID 性が保証されるトランザクションは複数の SQL 文のあつまり BEGIN( または START TRANSACTION) で始まる COMMIT あるいは ROLLBACK で終わるトランザクションには分離レベルが指定できる他のトランザクションの実行結果がどこまで見えるか 3 つの分離レベル (SERIALIZABLE, REPEATABLE READ, READ COMMITTED) があり前の方ほど確実に分離される分離レベルと性能にはトレードオフの関係がある 59

60 まとめ (2/2) トランザクションでは表や行を他のトランザクションによる更新から保護するためにロックを用いるロックが獲得できないときにはトランザクションは待機する SQL を実行する際に自動的にロックが獲得される獲得したロックはトランザクション終了まで開放されない明示的にロックを要求することもできるロックの副作用 : デッドロックを生じることがある AP 設計時にデッドロックを予防するのが望ましい予防以外にデッドロック発生時のリトライを作りこんでおく 60

61 トランザクションその他の話題今回のお話はアプリケーションプログラム開発の観点から話題を選びました DB 管理者の観点からは以下のようなテーマも重要です ACID 性の D( 持続性 ) については触れませんでしたがクラッシュ時のリカバリやオンラインバックアップといった重要なテーマがあります継続的アーカイブとポイントインタイムリカバリは重要リカバリに関連してチェックポイントの設定も重要デッドロックや長時間ロックの特定方法 61

62 参考文献 1.PostgreSQL グローバル開発グループ, PostgreSQL マニュアル, 増永良文, リレーショナルデータベース入門 ( 第 3 版 ), 第 10 章, サイエンス社, RDB 全般について基礎 ( 理論 ) から解説した教科書 3. 松田神一, オススメ! OSS-DB 情報, トランザクション, デッドロックについて, PostgreSQL 特有の振る舞いや設定の TIPS を挙げており参考になる 62

63 DUCTION TO INTRO- TRANS- おわりおつかれさまでした ACTION 63

プレポスト【問題】

プレポスト【問題】 1/5 ページプレポストデータベース基礎受講日程受講番号氏名 1 データベースの特徴で間違っているものを選びなさい 1. データの一元管理が可能 2. データの重複が少ない 3. プログラムとの関係が1 対 1 4. データの整合性の確保 2 ANSI/SPARC による 3 層スキーマについて正しいものを選びなさい 1. 外部スキーマ : プログラムに必要な部分のデータ構造を定義概念スキーマ

DUCTION はじめての人のための トランザクション入門 TO INTRO- TRANS- 日本 PostgreSQL ユーザ会第 35 回 PostgreSQL 勉強会 2017 年 5 月 27 日 ACTION 坂田哲夫 (NTT OSS センタ ) 1

DUCTION はじめての人のためのトランザクション入門 TO INTRO- TRANS- 日本 PostgreSQL ユーザ会第 35 回 PostgreSQL 勉強会 2017 年 5 月 27 日 ACTION 坂田哲夫 (NTT OSS センタ ) 1