SQLトランザクションの分離レベルとハンズオン
table:分離レベルの説明
説明
READ UNCOMMITTED 他のトランザクションのコミットされていない変更が見える。
READ COMMITTED 他のトランザクションのコミットされた変更が見える。
REPEATABLE READ トランザクション開始時にコミットされていたデータのみ見える。
SERIALIZABLE 毎回ロックをかけてトランザクションを直列化。
table:分離レベルごとに発生し得るリード
ダーティリード ファジーリード ファントムリード ロストアップデート
READ UNCOMMITTED 発生する 発生する 発生する 発生する
READ COMMITTED - 発生する 発生する 発生する
REPEATABLE READ - - 発生する 発生する
SERIALIZABLE - - - -
ダーティリード
他のトランザクションが更新した未コミット状態のデータを読み込んでしまうこと。
ファジーリード
トランザクション内で同じレコードを2回読み込んだとき、読み込み間隔内に他のトランザクションが該当レコードの更新をコミットしたことで、1回目と2回目の読み込み結果が変わってしまうこと。
ファントムリード
トランザクション内で同一条件のレコード抽出を2回行ったとき、読み込み間隔内に他のトランザクションがレコードの挿入/削除をコミットしたことで、1回目と2回目の結果のレコード数が変わってしまうこと。
ロストアップデート
失われた更新。
例えばREPEATABLE READの分離レベルでは、トランザクション1開始→別のトランザクション2がコミット→トランザクション1がコミット。という流れでトランザクション2の更新コミットがなかったことになる問題のこと。
READ UNCOMMITTEDでもREAD COMMITTEDでも値の取得タイミングが原因で発生し得る。
データベースを選定するときはトランザクション分離レベルを調べておこう。
table:有名どころのデフォルト値
Postgres READ COMMITTED
MySQL REPEATABLE READ
Oracle READ COMMITTED
SQL Server READ COMMITTED
jiroshin.icon READ COMMITTED か REPEATABLE READ 議論。どっちがいいんだろうね。
そのほか参考リンク:
jiroshin.icon mysqlはスナップショットを取得するのははじめのreadクエリ実行時ということは覚えておくべき。ただし例外としてselect for update(locking read)だったらテーブルレベルで最新情報をとる。
体験コーナー
準備するもの
MySQL
code:docker-compose.yml
version: '3'
services:
mysql:
image: mysql:5.7
command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --skip-character-set-client-handshake
ports:
- "3306:3306"
environment:
- MYSQL_ROOT_PASSWORD=mysql
接続: $ mysql -u root -h 127.0.0.1 -p
データベース作成: CREATE DATABASE test_db;
テーブル作成: create table users (id Integer auto_increment, cash Integer not null, primary key (id));
seedデータ入れておく: insert into users (cash) values (100);
Postgres
code:docker-compose.yml (MySQL)
version: '3'
services:
db:
image: postgres:9-alpine
restart: always
environment:
POSTGRES_PASSWORD: postgres
POSTGRES_USER: root
POSTGRES_DB: postgres
ports:
- 5432:5432
接続: $ psql -h 127.0.0.1 -p 5432 -U root -d postgres
データベース作成: CREATE DATABASE test_db;
テーブル作成: create table users (id Integer , cash Integer);
seedデータ入れておく: insert into users (1, cash) values (1, 100);
ハンズオン