「トランザクション」は、一つの処理に対して、途中で失敗しても、データの完全性を保つための仕組みです。
しかし、多くのDBMSでは、一つのデータベースに対して、かなり多くのSQL文が送信されます。なので、トランザクション同士が競合してしまうのか心配になりますよね。
トランザクションの競合により起こりうる問題
ダーティーリード
まだ、コミットされていない内容を他の人が読めてしまう問題です。
単純に、データベースの内容を参照するだけのシステムならまだしも、お金を扱うようなシステムであれば、特に致命的な問題になりえるでしょう。
DBMSによっては、ダーティーリードは発生しない。
なお、Oracleや、PostgreSQLの場合は、仕様上、コミットされてない情報が読みないようになっているため発生しません。
反復不能読み取り(ノンリピータブルリード)
複数のSELECT文を発行するような処理であれば、処理の途中で計算の結果が変わってしまう可能性があります。
ファントムリード
一回目のSELECT文が実行された後に、他のトランザクションでINSERTされて、2回目のSELECT文の行数が変わってしまう問題です。
厳密な件数処理が行われる処理では、大きな影響が出てしまうでしょう。
それらの対策としては?
そこで、DBMSには、トランザクションを分離する仕組みになっています。
競合の対策としてある「ロック」
あるトランザクションで、実行していた内容を他のトランザクションと競合しないよう、単独で実行しているのと同じ結果になります。
そのために「ロック」という機能が用いられています。
ロックとは?
あるトランザクションで読み書きしている行に関して鍵を掛けて、他の人が読み書きできないようにする仕組みです。
なので、「コミット」または、「ロールバック」で、そのトランザクションが終了すると、他の人が再度読み書きできるようになります。
ロックによるパフォーマンスはどうなの?
相手のトランザクションが終了するまで、他のトランザクションは待つことになります。この待ち時間は、数ミリ秒以下と非常に短いです。
しかし、ロックがたくさん発生した場合は、チリも積もれば山となるで長い時間になってしまうこともあります。
トランザクションの分離レベル(隔離性水準:ISOLATION LEVEL)
「ロック」は、確かに安全ですが、パフォーマンスに影響が出てしまいます。
そこで、トランザクションでは、「安全サイドに倒す」か、「パフォーマンスを取るか」で「トランザクションの分離レベル」というものを設定できます。
トランザクションの分離レベル | ダーティーリード | 反復不能読み取り
(ノンリピータブルリード) |
ファントムリード | 説明 |
---|---|---|---|---|
READ UNCOMMITED | 起こりえる。 | 起こりえる。 | 起こりえる。 | |
READ COMMITTED | 起こりえない。 | 起こりえる。 | 起こりえる。 | コミットされたデータだけ読み取るのでダーティリードは発生しない。
多くのDBMSではこれで動いている。 |
REPEATABLE READ | 起こりえない。 | 起こりえない。 | 起こりえる。 | |
SERIALIZABLE | 起こりえない。 | 起こりえない。 | 起こりえない。 | 一番安全ではあるが排他待ちが多くなるためスループットが悪くなる。 |
デフォルトでは、ほとんどのDBMSで分離レベルとして、「READ COMMITTED」が設定されています。「ダーティーリード」しか防げないですが、ある程度パフォーマンスを重視しているということですね。
トランザクションの分離レベルの指定方法
下記のSQL文を発行すれば、「分離レベル」を変更することができます。(DBMSによって異なります。)
1 2 |
SET TRANSACTION ISOLATION LEVEL 分離レベル名 SET CURRENT ISOLATION 分離レベル名 |
Oracleや、PostgreSQLの場合
Oracleや、PosgreSQLの場合は、「READ UNCOMMITED」がDBMSの仕様として、常にコミットされていない情報が読めないようになっているため存在しないです。
MySQLとOracleのロックの動きの違い
OracleとMySQLではロックの動きが異なります。
MySQL
JOINした際にJOIN対象のテーブルの行を全てロックするため、Oracleに比べるとロックの範囲が広いので競合が起こりやすい。
技術的には「FOR UPDATE カラム名」という構文が使えない。(「FOR UPDATE」までしか使えないのでロックの範囲が狭くなる。)
対処法
一旦、サブクエリを使うことでFOR UPDATEの範囲はこのテーブルだけですよと伝えてあげること。こうしたとしても素直にJOINする場合と比べても実行計画は変わらない。
Oracle
JOINした際に、更新対象のテーブルの行のみをロックするため、MySQLに比べるとロックの範囲が狭いので、競合が起こりにくい。
技術的には「FOR UPDATE カラム名」とカラム名まで指定して行ロックすることが可能。
この記事へのコメントはありません。