はじめに
以下はスッキリわかるSQL入門第9章(トランザクション)の学習備忘録です。
引用されている個所は書籍の内容をそのまま書き、引用されていない箇所は私が調べて補足した内容を書いてあります。
SQLのタグが付いているコードブロックに関しては、書籍の引用と私が調べて補足したものを織り交ぜた内容になっています。
RDMSについてはMySQLを想定しています。
第9章トランザクション
9.1正確なデータ操作
トランザクション
開発者がDBMSに対して複数のSQL文を送る際に、1つのかたまりとしてまとめたもの。
トランザクション制御
DBMSがひとかたまりのSQL文を扱うこと。
DBMSによるトランザクションの制御
- トランザクションの途中で、処理が中断されないようにする
- トランザクションの途中に、他の人が処理を割り込めないようにする
SQLにおけるセミコロンの取り扱い
現状では多くのDBMS製品には、1つのSQL文の終了にはセミコロンが用いられてはいるが、これはあくまで『SQLの構文規則』というよりは、文の区切りを判定するための『単なる記号』として扱っている点に注意が必要。文の区切りをセミコロン以外の別の記号に設定できるDBMSは多数存在する。
9.2 コミットとロールバック
ACID特性の1つである原子性(atomicity)
トランザクションに含まれる複数のSQL文が、DBMSによって不可分な性質として扱われること。DBMSはトランザクションに含まれる全てのSQL文において、必ず『全ての実行が完了している』あるいは『1つも実行されてない』の状態になるように制御する。
原子性確保の仕組み
トランザクション中のSQLは確定ではなく、仮のものとして管理され、トランザクションが終了する際に『仮の書きかえ』を全て確定させてしまうこと。この確定行為のことをコミットと呼ぶ。
また、トランザクション中に異常が発生した場合、DBMSはそれまですべて行った『仮の書き換え』をキャンセルしてなかったことにする。この動作をロールバックと呼ぶ。
自動コミットモードの解除
多くのDBMSではデフォルト状態では自動コミットモードが動作する。1つのSQL文が実行されるたびに、自動的に裏でコミットを行っている。
MySQLで自動コミットモードを解除するには『SET AUTOCOMMIT=0』というSQL文を実行する。
9.3 トランザクションの分離
トランザクション分離については以下の記事を参考にさせて頂きました。
同時処理による発生しうる3つの代表的な副作用
1. ダーティリード
まだコミットされていない未確定の変更を、他の人が読めてしまうという副作用。
2. 反復不能読み取り(non-repeatable read)
あるテーブルに対してSELECT分を実行した後、他の人がUPDATE分を実行してデータを書き換えた後に、次回SELECTした際に検索結果が異なってしまうこと。
3. ファントムリード
反復不能読み取りと似ているが、2回のSELECT文の間にINSERT文を行う事により2回目のSELECT文結果行数が変わってしまうこと。1回目の検索結果の行数に依存するような処理を行う場合に問題となる事がある。
しかし上記3つの副作用について、DBMSは個々のトランザクションについて分離性(isolation)を維持して実行するために、仮に他のトランザクションと同時に実行していても、あたかも単独で実行しているのと同じ結果となるよう制御する。
DBMSはこの制御を行うために、行ロックをかけている。
一般的なトランザクション分離レベル
| 分離レベル | ダーティリード | 反復不能読み取り | ファントムリード | 処理速度/安全性 |
|---|---|---|---|---|
| READ UNCOMMITTED | ✖ | ✖ | ✖ | 高速/危険 |
| READ COMMITTED | 〇 | ✖ | ✖ | |
| REPEATABLE HEAD | 〇 | 〇 | ✖ | |
| SERIALIZABLE | 〇 | 〇 | 〇 | 低速/安全 |
分離レベルが表のに下に行けば行くほど、3つの副作用(ダーティリード、反復不能読み取り、ファントムリード)を防げるが処理が遅い。
また、分離レベルが表の上に行けば行くほど3つの副作用を防げないが処理が早い。ちなみにデフォルトのトランザクション分離レベルはREAD COMMITTEDが採用されている。分離レベルはケースバイケースで変更していく必要がある。
トランザクション分離レベルの指定
SET TRANSACTION ISOLATION LEVEL 分離レベル名
SET CURRENT ISOLATION 分離レベル名
※どちらの構文を使うかは、DBMS製品によって異なる。
あるデータについて、『書き換え済み(ただし未確定)』と『書き換え前』の2つのバージョンを併存させることを並列実行制御と言う
9.4 ロックの活用
DBMSはトランザクションの分離性を確保するために自動的に行ロックをかけている。開発者はいつ、どの行にロックをかけるかを指示する必要はない。一方でSQL文を使って明示的に指定した対象をロックすることもできる。
排他ロックと共有ロック
- 排他ロック・・・他からのロックを一切許可しない。主にデータの更新時に利用される。
- 共有ロック・・・他からの共有ロックを許す特性があるため、データの読み取り時に利用される。
明示的な行ロックと表ロックの取得
NOWAITオプションを付与した場合、DBMSはロックの解除を待機せずにすぐさまロック失敗のエラーを返すため、トランザクションは即時終了する。処理を待たせたくないアプリケーションなどに有効。
/* 行ロック */
SELECT ~ FOR UPDATE (NOWAIT)
/* 表ロック。モード名はEXCLUSIVEで排他ロック、SHAREで共有ロック */
LOCK TABLE テーブル名 IN モード名 MODE (NOWAIT)
デッドロック予防方法
1. トランザクションの時間を短くする。
2. 同じ順番でロックするようにする。SQL文を組み立てる際は、可能な限り同じ順番でテーブルや行にロックがかかるようにする。
ロックエスカレーション
あるテーブルについて多数の行ロックがかけられると自動的に表ロックに切り替わる機構。
メリットとしては、膨大な数の行をロックする事そもそも負荷が高まり、メモリも圧迫するので負荷が下げられる。
デメリットは、同時に実行できるトランザクションが減って逆に遅くなったり、デッドロックの原因にもなりうる。
フェーズコミット
各データベースに対してトランザクションの『確定準備』と『確定』の2段階の指示を出すことによって、複数のデータベースにまたがったトランザクションの整合性を維持する。