トランザクション処理
エクセルVBAでデータベースを扱うためのSQL入門です。
前回までは、1つのテーブルにデータを追加/更新/削除してきました。
システム構築していく上では、複数のテーブルへの処理は必要不可欠になります。
複数のテーブル間のデータの整合性を保つことです。
あるテーブルのデータと、別のテーブルのデータの間に整合性が必要な場合これを保つ必要があります。
あるデータを2つのテーブルに更新しなければならない時、
もし、片方のテーブルしか更新されていなかったら・・・
これでは、データが不整合となりデータの信頼性が失われてしまいます。
今回は、このトランザクション処理の説明と、ADOでの実装と使用例の解説になります。
トランザクション処理とは
以下はウィキペディアの トランザクション処理 からの引用です。
トランザクションは、データベースをある一貫した状態から別の一貫した状態へ変更するアクションを1つに束ねたものである。
トランザクション処理は、既知の一貫した状態のデータベースを維持するよう設計されており、相互依存のある複数の操作が全て完了するか、全てキャンセルされることを保証する。
例えば、顧客の普通預金口座から当座預金口座に500ドルを移動させる典型的な銀行のトランザクションを考えて見る。
普通預金口座から500ドルを引き落とし、当座預金口座に500ドルを入金するのである。
引き落としが成功して入金が失敗した場合（あるいは逆の場合）、銀行の帳簿はその日の営業完了時点で不整合を生じる。
したがって、2つの操作が両方成功するか、両方失敗することを保証する必要があり、それによって銀行のデータベースに不整合が生じないようにする。
トランザクション処理は、既知の一貫した状態のデータベースを維持するよう設計されており、相互依存のある複数の操作が全て完了するか、全てキャンセルされることを保証する。
普通預金口座から500ドルを引き落とし、当座預金口座に500ドルを入金するのである。
引き落としが成功して入金が失敗した場合（あるいは逆の場合）、銀行の帳簿はその日の営業完了時点で不整合を生じる。
したがって、2つの操作が両方成功するか、両方失敗することを保証する必要があり、それによって銀行のデータベースに不整合が生じないようにする。
切り離すことの出来ない一連の処理が一つのトランザクションになります。
データの整合性を保つためには、
1つのトランザクション内の全操作がエラー無しに全て終了するか、もしくは、そのトランザクションが実行される前の状態にもどすかのどちらかでなければなりません。
一連の処理の途中でエラー発生した場合でも処理が中途半端な状態のデータのままにならないようにしなければなりません。
トランザクション処理ではデータベースの本来のデータ領域に直ちには書き込みません。
トランザクションの全操作が完了した後、コミットすることで本来のデータ領域に書き込まれます。
※この仕組みはデータベースによりさまざまになりますので、あくまで簡易的なイメージとして書いています。
途中の処理で不都合が発生した場合は、ロールバックすることで、トランザクション開始前の整合性が取れた状態を維持します。
このような仕組みをトランザクション処理と呼びます。
トランザクション中で取得するデータは、そのトランザクションの中の更新後の情報になります。
つまり、コミットするまでは、トランザクションの更新はその中だけで有効なものであり、
コミットすることではじめてデータベースに確定として保存されます。
1つのトランザクションの全操作が終了し、その全更新内容をデータベースに正式に書き込み確定せることをコミットと言います。
コミットするまでは他（のトランザクション等）からは更新内容は見ることが出来ず、コミットすることで他からも見えるようになります。
コミットするまでは他（のトランザクション等）からは更新内容は見ることが出来ず、コミットすることで他からも見えるようになります。
ロールバック
トランザクション開始前の整合性がとれた状態に戻します。
データベースの仕組みなので様々ではありますが、
トランザクション処理中の更新内容が記憶されているトランザクション領域を破棄して、データベースへの書き込みを行わないと理解すれば良いでしょう。
データベースの仕組みなので様々ではありますが、
トランザクション処理中の更新内容が記憶されているトランザクション領域を破棄して、データベースへの書き込みを行わないと理解すれば良いでしょう。
デッドロック
2つのトランザクションの処理においてデータベースの同じ部分に同時に更新アクセスしようとした場合、
後から開始されたトランザクションは、前のトランザクションが終了するまで待機させて、二重更新しないようになっています。
このとき、2つのトランザクションが、互いの処理の進行を妨げてしまう場合がでてきます。
例えば、
トランザクションAがデータXを更新、 トランザクションBがデータYを更新、
その後に、 トランザクションAがYを、トランザクションBがXを更新しようとした時、
互いが互いを待機させてしまう為、どちらのトランザクションも先に進めない状態に陥ります。
この状態になる事をデッドロックと言います。
※ここでの説明は極めて単純化したもので、実際にはロックの仕方もあり、より複雑になります。
多くのデータベースではデッドロックが発生するのを検出するよう設計されていたりしますが、完全に防ぐことはできません。
各業務アプリではデッドロックが発生しないよう対策を施す必要があります。
この対策は専門に行う必要がありますので、SQL入門ではデッドロックについては考慮しません。
後から開始されたトランザクションは、前のトランザクションが終了するまで待機させて、二重更新しないようになっています。
このとき、2つのトランザクションが、互いの処理の進行を妨げてしまう場合がでてきます。
例えば、
トランザクションAがデータXを更新、 トランザクションBがデータYを更新、
その後に、 トランザクションAがYを、トランザクションBがXを更新しようとした時、
互いが互いを待機させてしまう為、どちらのトランザクションも先に進めない状態に陥ります。
この状態になる事をデッドロックと言います。
※ここでの説明は極めて単純化したもので、実際にはロックの仕方もあり、より複雑になります。
各業務アプリではデッドロックが発生しないよう対策を施す必要があります。
この対策は専門に行う必要がありますので、SQL入門ではデッドロックについては考慮しません。
ADOでのトランザクション処理
ADOでトランザクション処理を行うには、ADODB.Connectionオブジェクトのメソッドを使用します。
BeginTrans メソッド
CommitTrans メソッド
RollbackTrans メソッド
各DBのプロバイダーによっては、入れ子になったトランザクションをサポートしていますが、ここでは省略します。
BeginTransでトランザクションを開始した後、CommitTransすることなくDBをクローズした場合はロールバックされます。
トランザクションの更新内容は、CommitTransしない限り確定されることはありません。
以下は、VBAクラスに対するトランザクション処理にともなう追加部分になります。
VBAクラスのメソッド名は、ADOのメソッド名をそのまま使用しました。
BeginTransは、DBへの接続が無い場合には自動的に接続するようにしています。
CommitAndCloseは、コミット後にクローズしています。
RollbackAndCloseは、ロールバック後にろくーずしています。
単純に使う場合は、
BeginTrans → CommitAndClose
この順に使うだけになります。
CommitTrans メソッド
RollbackTrans メソッド
BeginTransでトランザクションを開始した後、CommitTransすることなくDBをクローズした場合はロールバックされます。
トランザクションの更新内容は、CommitTransしない限り確定されることはありません。
'トランザクション制御
Private pTransaction As Boolean
'トランザクション開始
Public Function BeginTrans() As Boolean
On Error GoTo Err_Exit
If Not Me.DbOpen Then Exit Function
Me.AdoCon.BeginTrans
pTransaction = True
BeginTrans = True
Exit Function
Err_Exit:
BeginTrans = False
Call setErr(Err, "BeginTrans")
End Function
'コミット
Public Function CommitTrans() As Boolean
On Error GoTo Err_Exit
Me.AdoCon.CommitTrans
pTransaction = False
CommitTrans = True
Exit Function
Err_Exit:
CommitTrans = False
Call setErr(Err, "CommitTrans")
End Function
'ロールバック
Public Function RollbackTrans() As Boolean
On Error Resume Next
Me.AdoCon.RollbackTrans
pTransaction = False
RollbackTrans = True
End Function
'コミット&クローズ
Public Function CommitAndClose() As Boolean
If Me.CommitTrans Then Exit Function
If Me.DbClose Then Exit Function
CommitAndClose = True
End Function
'ロールバック&クローズ
Public Function RollbackAndClose() As Boolean
If Me.RollbackTrans Then Exit Function
If Me.DbClose Then Exit Function
RollbackAndClose = True
End Function
VBAクラスのメソッド名は、ADOのメソッド名をそのまま使用しました。
BeginTransは、DBへの接続が無い場合には自動的に接続するようにしています。
CommitAndCloseは、コミット後にクローズしています。
RollbackAndCloseは、ロールバック後にろくーずしています。
BeginTrans → CommitAndClose
この順に使うだけになります。
VBAクラスの全コード
以下に、上記のトランザクション処理を追加したVBA全コードを掲載します。
VBAクラスの全コード：トランザクション処理
SQL入門の「トランザクション処理」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール：clsSQLite SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説。
プロシージャーの並び順やコメントを、以前のデータ取得時のVBAクラスから大きく変更しています。
SQL入門の「データの取得：条件指定（SELECT,WHERE）」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール：clsSQLite
トランザクション処理の使用例
以下は、トランザクション処理で、コミットおよびロールバックを実際に試すVBAになります。
処理内容は何の意味も持たないもので、あくまでトランザクション処理の動作確認用です。
コミットしたりロールバックしたりして、実際にデータベースへの更新状況を確認してください。
・ロールバックすると、終了後にINSERTされていない。
・コミットすると、終了後にINSERTされている。
・INSERTしたデータが同じトランザクション内では取得でき、別のコネクションからは取得できない。
これらの動作を実際に実行して確認してください。
処理内容は何の意味も持たないもので、あくまでトランザクション処理の動作確認用です。
Sub TestTrans()
Dim clsDB As New clsSQLite
clsDB.DataBase = "C:\SQLite3\sample.db"
Dim ws As Worksheet
Set ws = ActiveSheet
Dim sSql As String, rCnt As Long
'トランザクション処理でINSERT
clsDB.BeginTrans
sSql = ""
sSql = sSql & "INSERT INTO m_customer"
sSql = sSql & " (code,name,address)"
sSql = sSql & " VALUES "
sSql = sSql & " ('T001','名前1','住所1')"
If Not clsDB.ExecuteNonQuery(sSql, rCnt) Then
MsgBox clsDB.ErrMsg
Exit Sub
End If
sSql = ""
sSql = sSql & "INSERT INTO m_item"
sSql = sSql & " (item_code,item_name)"
sSql = sSql & " VALUES "
sSql = sSql & " ('T001','名前2')"
If Not clsDB.ExecuteNonQuery(sSql, rCnt) Then
MsgBox clsDB.ErrMsg
Exit Sub
End If
'別のコネクションからデータを取得
Dim clsDB2 As New clsSQLite
clsDB2.DataBase = "C:\SQLite3\sample.db"
sSql = ""
sSql = sSql & "SELECT * FROM m_customer"
sSql = sSql & " WHERE code = 'T001'"
If Not clsDB2.SheetFromRecordset(sSql, ws.Range("A1"), Clear, True) Then
MsgBox clsDB2.ErrMsg
Exit Sub
End If
sSql = ""
sSql = sSql & "SELECT * FROM m_item"
sSql = sSql & " WHERE item_code = 'T001'"
If Not clsDB2.SheetFromRecordset(sSql, ws.Range("A5"), Clear, True) Then
MsgBox clsDB2.ErrMsg
Exit Sub
End If
'同じトランザクション内でデータを取得
sSql = ""
sSql = sSql & "SELECT * FROM m_customer"
sSql = sSql & " WHERE code = 'T001'"
If Not clsDB.SheetFromRecordset(sSql, ws.Range("A11"), Clear, True) Then
MsgBox clsDB.ErrMsg
Exit Sub
End If
sSql = ""
sSql = sSql & "SELECT * FROM m_item"
sSql = sSql & " WHERE item_code = 'T001'"
If Not clsDB.SheetFromRecordset(sSql, ws.Range("A15"), Clear, True) Then
MsgBox clsDB.ErrMsg
Exit Sub
End If
clsDB.RollbackAndClose
'clsDB.CommitAndClose
Set clsDB = Nothing
End Sub
コミットしたりロールバックしたりして、実際にデータベースへの更新状況を確認してください。
・ロールバックすると、終了後にINSERTされていない。
・コミットすると、終了後にINSERTされている。
・INSERTしたデータが同じトランザクション内では取得でき、別のコネクションからは取得できない。
これらの動作を実際に実行して確認してください。
トランザクション処理の最後に
今回は新しいSQLは無く、トランザクション処理についての解説だけになりました。
トランザクション処理は、データベースを扱う上では把握しておかなければならないものです。
データベースのデータ整合性を保てるように、しっかりトランザクション処理を理解しておきましょう。
今回までで基本的な部分については一通り説明が終わりました。
次回からは、より便利にSQLを使えるように副問合せ（サブクエリ）を解説していきます。
サブクエリが使えるようになると、SQLのすごさがより実感として分かってくるはずです。
トランザクション処理は、データベースを扱う上では把握しておかなければならないものです。
データベースのデータ整合性を保てるように、しっかりトランザクション処理を理解しておきましょう。
次回からは、より便利にSQLを使えるように副問合せ（サブクエリ）を解説していきます。
サブクエリが使えるようになると、SQLのすごさがより実感として分かってくるはずです。
同じテーマ「SQL入門」の記事
データの更新（UPDATE）
データの削除（DELETE）
他のテーブルのデータで追加/更新/削除
インデックスを作成して高速化（CREATE INDEX）
トランザクション処理
VBAクラスの全コード：トランザクション処理
サブクエリ（副問合せ）
サブクエリのネストとSQLコメント&整形
WITH句（共通テーブル式）
取得行数を限定するLIMIT句
分析関数（OVER句,WINDOW句）
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。