この記事では、トランザクション処理・排他制御・ストアドプロシージャについて、初心者にも分かりやすく、図解付きで丁寧に解説しています！
トランザクション処理
- データベースに対してまとめて処理する必要がある操作を一括りにした処理。
- ACID特性はトランザクション処理が満たす必要がある4つの特性。
排他制御
- 排他制御とは、トランザクション処理を実行している間は別のトランザクション処理が実行できないようにデータベースをロックする処理。
- 専有ロック中、他ユーザはデータの変更も読取も出来ない。
- 共有ロック中、他ユーザはデータの変更は出来ないが、読取は出来る。
- デッドロックとは、お互いがアクセスしたいデータをロックしてしまうことで、永遠にロック解除を待ち続ける状態。
ストアドプロシージャ
- 一連のSQLをまとめたプログラムをプロシージャと呼び、DBMSにプロシージャを保存する。
- アプリはDBMSに保存されたプロシージャを呼び出すことで、SQLを実行できる。
- ストアドプロシージャを使うことで、アプリとサーバ間の通信負荷を下げることが出来る。
トランザクション処理
トランザクション処理とは、データベースに対してまとめて行う必要がある操作を一括りにした処理のことです。トランザクション処理は全てを完全に行うか、全く行わないかのどちらかです。
トランザクション処理の例は銀行の振込処理です。
振込処理は以下の4つの処理から成り立ち、中断することは許されません。
- Aさん(送金する人)の口座残高を確認する
- Aさんの口座から残高を減らす
- Bさん(入金される人)の口座残高を確認する
- Bさんの口座残高から残高を増やす
トランザクション処理が途中で終了した場合を考えてみましょう。例えば、送金口座から残高を減らした後にシステム障害が起きて、入金口座の残高を増やす処理が行われなかったとします。Aさんの送金口座からお金は減ったのに、Bさんの入金口座の残高が増えないことになり、凄く困ったことになりますよね。
なので、処理を行う時は4つ全て行う必要があり、途中で失敗した時は一番最初の状態に戻す必要があります。このように、一連で行う必要がある操作の一括りをトランザクション処理と言います。
ACID特性
トランザクション処理はACID特性という4つの特徴を満たす必要があります。
原子性(Atomicity)
トランザクション処理は全て完了するか、全く実行されないかのどちらかで終了する必要があります。これを原子性と呼びます。
一貫性(Consistency)
トランザクション処理の前後でデータ不整合が生じないようにする必要があります。例えば、残高が100円しかないのに1000円の振込を実行したら、残高が-900円になり、おかしなデータが生じてしまいますよね。このような不整合を起こさないようにする性質が一貫性です。
独立性(Isolation)
複数のトランザクション処理を実行しても、他のトランザクション処理の影響を受けずに実行できるようにする必要があります。例えば、残高が1000円あるときに、100円の銀行引き落としと200円の銀行引き落としが同時に実行されたとします。
この時、1000円-100円=900円の処理と900円-200円=700円の処理がそれぞれ独立して行われる必要があります。つまり残高は700円になるということですね。
同時に行われたから、1000円-100円=900円、1000円-200円=800円で計算して残高が800円になりました。では困りますよね。
トランザクション処理は1つずつ完了させる必要があるという性質が独立性になります。
耐久性(Durability)
トランザクション処理が完了した後は、データベースに障害が発生しても結果を保存する必要があります。銀行のシステムに障害が発生してよく分からなくなったので残高を0円にします、だと困りますよね。例え、障害が発生してもデータは保存しておく必要があります。この性質を耐久性と呼びます。
排他制御
トランザクション処理では独立性を満たす必要があるため、トランザクション処理を行っている時は別のトランザクション処理が実行出来ないようにデータベースをロックします。これを排他制御と言います。
排他制御をしないとどうなるのか
排他制御を行わずに、複数のトランザクション処理を行うとどうなるのでしょうか。ATMからお金を引き出すトランザクションを同時に行った場合を考えてみます。
ATMからお金を引き出すトランザクションでは以下3つの処理を行います。
- 口座残高を確認する
- 口座残高からお金を減らす
- ATMからお金を出金する
今、残高が100万円の口座から1万円を引き出す処理を同時に行ってみます。
- トランザクション①が残高を確認する：残高100万円であると確認
- トランザクション②が残高を確認する：残高100万円であると確認
- トランザクション①で残高を1万円減らす：100万円から1万円減らして99万円にする
- トランザクション②で残高を1万円減らす：100万円から1万円減らして99万円にする
- トランザクション①で1万円を出金
- トランザクション②で1万円を出金
トランザクション①と②で合計2万円出金しているのに銀行の残高は99万円と、おかしなデータが発生しています。
排他制御をするとどうなるか
排他制御を行うと、トランザクション実行中はデータベースをロックして別のトランザクションがアクセス出来ないようにします。
- トランザクション①が残高を確認する：残高100万円であると確認
- トランザクション①で残高を1万円減らす：100万円から1万円減らして99万円にする
- トランザクション①で1万円を出金
- トランザクション②が残高を確認する：残高99万円であると確認
- トランザクション②で残高を1万円減らす：99万円から1万円減らして98万円にする
- トランザクション②で1万円を出金
トランザクション①実行中はデータベースをロックしているので、トランザクション②はデータベースにアクセス出来ません。そのため、トランザクション①→トランザクション②の順番で実行することになり、データの整合性が保たれます。
専有ロックと共有ロック
データベースにおかしなデータを生み出さないためには排他制御をする必要があることが分かりました。では、排他制御の際、どのようにデータベースをロックするのでしょうか。ロックの方法はトランザクション処理のタイプによります。
2種類のトランザクション処理
トランザクション処理には大きく分けて2種類あります。
- 登録・更新・削除など、データを変更する処理
- データを変更せずに読み取るだけの処理
口座への入金や出金はデータを変更する処理ですが、残高や取引履歴を確認するのは読み取り処理になります。トランザクション処理の種類によって、排他制御のロックの仕方が変わります。
専有ロック
データを変更する処理の場合は専有ロックを行います。専有ロックを行うと、後続のトランザクションはデータを変更する処理もデータを読み取る処理も行えません。
データ変更中に別のトランザクション処理がデータを変更するとおかしなデータが出来てしまいます。また、データを読取っても変更中のデータを読取ることになるので誤解を生む可能性があります。なので、専有ロック中は変更も読取も不可になります。
共有ロック
データを読取る処理の場合は共有ロックを行います。共有ロックを行うと、後続のトランザクションはデータを変更する処理は出来ませんが、データを読み取る処理は行えます。
データ読取中に別のトランザクション処理がデータを変更すると、変更中のおかしなデータを見ることになってしまいます。しかし、データ読取中に別のトランザクションがデータを読取る分にはデータの不整合は発生しないので問題ありません。なので、共有ロック中は変更不可で読取可能になります。
デッドロック
デッドロックとは、複数のトランザクション処理が同じデータベースを使用する場合に、お互いに相手がアクセスしたいデータベースをロックしてしまい、永遠に待ち状態になってしまう状態のことです。
下の例では、トランザクション①がテーブルA→テーブルBの順番で、トランザクション②がテーブルB→テーブルAの順番で編集しようとしているため、お互いがお互いロックしているテーブルの解放待ちになってしまい、次のステップに進めなくなっています。この状態がデッドロックです。
アクセスする順番が逆のトランザクション処理が存在すると、デッドロックが発生する可能性があるため、複数のトランザクション処理を実行する場合は同じ順番でアクセスするように設計します。上の例で言うと、トランザクション①・②の両方がテーブルA→テーブルBもしくはテーブルB→テーブルAの順番でアクセスする設計になっていればデッドロックは起きなかったということですね。
ストアドプロシージャ
データベースに命令するときに使う言語をSQLと言います。トランザクション処理はSQLで書かれた処理です。通常アプリからデータベースサーバに対して命令を実行する場合、SQL文を1つずつ送ることになります。しかし、SQLをちまちま実行すると、その度にアプリとDBサーバ間で通信をする必要があるので、ネットワークの負荷が大きくなってしまい、処理速度も遅くなる可能性があります。
そこで、あらかじめ複数のSQL文を1つのプログラムにまとめて、データベース管理システム(DBMS)側に保存しておき、SQLを実行する時はそのプログラムを呼ぶようにします。この技術をストアドプロシージャと呼びます。
アプリはプロシージャを呼び出せば一連のSQLを実行出来るので、アプリとDBサーバ間の通信回数が減り、ネットワーク負荷を軽減できます。また、処理速度も上がります。
応用情報技術者試験での出題例
令和6年度春期問25
応用情報技術者
午前試験 令和6年度春期問25
ストアドプロシージャの利点はどれか。
ア アプリケーションプログラムからネットワークを介してDBMSにアクセスする場合，両者間の通信量を減少させる。
イ アプリケーションプログラムからの一連の要求を一括して処理することによって，DBMS内の実行計画の数を減少させる。
ウ アプリケーションプログラムからの一連の要求を一括して処理することによって，DBMS内の必要バッファ数を減少させる。
エ データが格納されているディスク装置へのI/O回数を減少させる。
正解は”ア”
ア DBMSに保存したプロシージャを呼び出すだけで、一連のSQLが実行されます。普通にSQLを実行すると、何回もクライアント-サーバ間で通信する必要がありますが、ストアドプロシージャを使うと1回の通信でOKになり、通信量を減少出来ます。なので正しいです。
イ 実行計画とはSQLを実行する際、どうすれば効率的に処理出来るのかを計算して導いた計画のことです。SQLを実行する度にDBMSが実行計画を作成するので、1回1回SQLを流しても、プロシージャを使っても、実行計画の回数は変わりません。なので誤りです。
ウ バッファとはSQLを実行した際、一時的に結果を保管しておく領域のことです。SQLを実行する度にバッファが必要になるので、プロシージャを使ってもバッファ数は変わりません。なので誤りです。
エ SQLが実行される回数自体は変わらないので、データが格納されているディスク装置へのI/O回数は変わりません。なので誤りです。
令和5年度春期問27
応用情報技術者
午前試験 令和5年度春期問27
クライアントサーバシステムにおけるストアドプロシージャの記述として，誤っているものはどれか。
ア アプリケーションから一つずつSQL文を送信する必要がなくなる。
イ クライアント側のCALL文によって実行される。
ウ サーバとクライアントの間での通信トラフィックを軽減することができる。
エ データの変更を行うときに，あらかじめDBMSに定義しておいた処理を自動的に起動・実行するものである。
正解は”エ”
ア SQLを集約したプロシージャを呼び出すだけでOKになるので、一つずつSQL文を送信する必要が無くなります。なので正しいです。
イ プロシージャを呼び出す時はCALL文を使います。なので正しいです。
ウ SQLを集約したプロシージャを呼び出すだけでOKになるので、サーバとクライアント間の通信負荷を軽減出来ます。なので正しいです。
エ プロシージャは自動的に起動・実行しません。ストアドプロシージャを使う場合は呼び出しを行う必要があります。なので誤りです。
令和4年度秋期問30
応用情報技術者
午前試験 令和4年度秋期問30
ACID特性の四つの性質に含まれないものはどれか。
ア 一貫性 イ 可用性 ウ 原子性 エ 耐久性
正解は”イ”
ACID特性は原子性・一貫性・独立性・耐久性の4つです。
