SQL入門
トランザクション処理

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
公開日:2019-12-11 最終更新日:2019-12-17

トランザクション処理


エクセルVBAでデータベースを扱うためのSQL入門です。
前回までは、1つのテーブルにデータを追加/更新/削除してきました。
システム構築していく上では、複数のテーブルへの処理は必要不可欠になります。


しかし、このとき注意しなければならない問題があります。
複数のテーブル間のデータの整合性を保つことです。
あるテーブルのデータと、別のテーブルのデータの間に整合性が必要な場合これを保つ必要があります。
あるデータを2つのテーブルに更新しなければならない時、
もし、片方のテーブルしか更新されていなかったら・・・
これでは、データが不整合となりデータの信頼性が失われてしまいます。

このようなデータの不整合を起こさない為のデータベースの仕組みがトランザクション処理になります。
今回は、このトランザクション処理の説明と、ADOでの実装と使用例の解説になります。

使用するテーブル定義は以下になります。

全テーブル定義とテーブル自動作成VBA
・テーブル:m_customer ・テーブル:m_item ・テーブル:t_sales ・テーブル自動作成

トランザクション処理とは

以下はウィキペディアの トランザクション処理 からの引用です。
トランザクションは、データベースをある一貫した状態から別の一貫した状態へ変更するアクションを1つに束ねたものである。
トランザクション処理は、既知の一貫した状態のデータベースを維持するよう設計されており、相互依存のある複数の操作が全て完了するか、全てキャンセルされることを保証する。

例えば、顧客の普通預金口座から当座預金口座に500ドルを移動させる典型的な銀行のトランザクションを考えて見る。
普通預金口座から500ドルを引き落とし、当座預金口座に500ドルを入金するのである。
引き落としが成功して入金が失敗した場合(あるいは逆の場合)、銀行の帳簿はその日の営業完了時点で不整合を生じる。
したがって、2つの操作が両方成功するか、両方失敗することを保証する必要があり、それによって銀行のデータベースに不整合が生じないようにする


切り離すことの出来ない一連の処理が一つのトランザクションになります。
データの整合性を保つためには、
1つのトランザクション内の全操作がエラー無しに全て終了するか、もしくは、そのトランザクションが実行される前の状態にもどすかのどちらかでなければなりません。
一連の処理の途中でエラー発生した場合でも処理が中途半端な状態のデータのままにならないようにしなければなりません。

トランザクションが開始されると、その更新内容は専用のトランザクション領域に保存されます。
トランザクション処理ではデータベースの本来のデータ領域に直ちには書き込みません。
トランザクションの全操作が完了した後、コミットすることで本来のデータ領域に書き込まれます。
※この仕組みはデータベースによりさまざまになりますので、あくまで簡易的なイメージとして書いています。
途中の処理で不都合が発生した場合は、ロールバックすることで、トランザクション開始前の整合性が取れた状態を維持します。
このような仕組みをトランザクション処理と呼びます。

トランザクション中の更新は、コミットするまでは他(のトランザクション等)からその更新は参照することはできません。
トランザクション中で取得するデータは、そのトランザクションの中の更新後の情報になります。
つまり、コミットするまでは、トランザクションの更新はその中だけで有効なものであり、
コミットすることではじめてデータベースに確定として保存されます。

コミット
1つのトランザクションの全操作が終了し、その全更新内容をデータベースに正式に書き込み確定せることをコミットと言います。
コミットするまでは他(のトランザクション等)からは更新内容は見ることが出来ず、コミットすることで他からも見えるようになります。

ロールバック
トランザクション開始前の整合性がとれた状態に戻します。
データベースの仕組みなので様々ではありますが、
トランザクション処理中の更新内容が記憶されているトランザクション領域を破棄して、データベースへの書き込みを行わないと理解すれば良いでしょう。
デッドロック
2つのトランザクションの処理においてデータベースの同じ部分に同時に更新アクセスしようとした場合、
後から開始されたトランザクションは、前のトランザクションが終了するまで待機させて、二重更新しないようになっています。
このとき、2つのトランザクションが、互いの処理の進行を妨げてしまう場合がでてきます。
例えば、
トランザクションAがデータXを更新、 トランザクションBがデータYを更新、
その後に、 トランザクションAがYを、トランザクションBがXを更新しようとした時、
互いが互いを待機させてしまう為、どちらのトランザクションも先に進めない状態に陥ります。
この状態になる事をデッドロックと言います。
※ここでの説明は極めて単純化したもので、実際にはロックの仕方もあり、より複雑になります。

多くのデータベースではデッドロックが発生するのを検出するよう設計されていたりしますが、完全に防ぐことはできません。
各業務アプリではデッドロックが発生しないよう対策を施す必要があります。
この対策は専門に行う必要がありますので、SQL入門ではデッドロックについては考慮しません。

ADOでのトランザクション処理

ADOでトランザクション処理を行うには、ADODB.Connectionオブジェクトのメソッドを使用します。

BeginTrans メソッド
CommitTrans メソッド
RollbackTrans メソッド

各DBのプロバイダーによっては、入れ子になったトランザクションをサポートしていますが、ここでは省略します。
BeginTransでトランザクションを開始した後、CommitTransすることなくDBをクローズした場合はロールバックされます。
トランザクションの更新内容は、CommitTransしない限り確定されることはありません。

以下は、VBAクラスに対するトランザクション処理にともなう追加部分になります。

'トランザクション制御
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になります。
処理内容は何の意味も持たないもので、あくまでトランザクション処理の動作確認用です。

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入門」の記事

データの更新(UPDATE)
データの削除(DELETE)
他のテーブルのデータで追加/更新/削除
インデックスを作成して高速化(CREATE INDEX)
トランザクション処理
VBAクラスの全コード:トランザクション処理
サブクエリ(副問合せ)
サブクエリのネストとSQLコメント&整形
WITH句(共通テーブル式)
取得行数を限定するLIMIT句
分析関数(OVER句,WINDOW句)


新着記事NEW ・・・新着記事一覧を見る

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ