プログラミング

PowerShellでのSQLServerのDB操作方法【トランザクション処理編】

こんにちは。
けいぞうです。

今回は「 PowerShellでSQLServerのDB操作をする方法 」

【 トランザクション処理編 】です。

前回の【 クエリ実行編 】はこちらです👇

PowerShellでのSQLServerのDB操作方法【SELECT,INSERT,UPDATE,DELETEクエリ実行】

[目次]

トランザクション処理を実行する方法

以下がINSERTクエリを実行する際にトランザクションをかけたサンプルスクリプトになります。

# 接続文字列の作成
$ConnectionString = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder
$ConnectionString['Data Source'] = "【インスタンス名】"
$ConnectionString['Initial Catalog'] = "【データベース名】"
$ConnectionString['Integrated Security'] = "TRUE"

# INSERT文の作成
$SQLQuery = "INSERT INTO [dbo].[Curry_Menu] VALUES(N'カツカレー',N'タルタルソース',N'300',N'3')"

# 結果格納用のDataTableを作成
$resultsDataTable = New-Object System.Data.DataTable

# SQLConnectionとSQLCommandを設定する
$SqlConnection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
$SqlCommand = New-Object System.Data.SQLClient.SQLCommand($SQLQuery, $SqlConnection)

# データベース接続
$SqlConnection.Open()

# トランザクションスタート
$transaction = $SqlConnection.BeginTransaction()

# コマンドにトランザクションを割り当てる
$SqlCommand.Transaction = $transaction

try {

    # INSERT文の実行
    $SqlCommand.ExecuteNonQuery()
    
    # コミットの実行
    $transaction.Commit()

} catch {
    # ロールバック
    $transaction.Rollback()
} finally {
    # データベース接続解除
    $SqlConnection.Close()
}

21行目のBeginTransaction()でトランザクション処理をスタートしています。

その後に24行目にて、コマンドに対してトランザクションを割り当てるという操作を実行しています。

これを行わずにクエリを実行すると以下のようなエラーになってしまいます。

“0” 個の引数を指定して “ExecuteNonQuery” を呼び出し中に例外が発生しました: “ExecuteNonQuery は、コマンドに割り当てられ
た接続が保留状態であるローカルのトランザクションにあるとき、トランザクション オブジェクトを持つコマンドが必要です。コマ ンドの Transaction プロパティがまだ初期化されていません。”
発生場所 行:1 文字:1
$SqlCommand.ExecuteNonQuery()
~~~~~~~~~
CategoryInfo : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : InvalidOperationException

なぜこのようなエラーになってしまうかは、次の章の排他ロックのタイミングについてを見てもらえるとわかりやすいかと思います。

排他ロックのタイミングについて

排他ロックとは、あるテーブルに対するクエリを実行中に、他のクエリと実行が重複してデータの不整合が発生しないように、他のトランザクションの処理を受け付けないようにすることです。

要するに、INSERT文くんが

「今俺がDBいじってるんだから、誰もさわるなよ！」

っていうのが排他ロックです。

排他ロックにもいろいろ種類があって、

「今俺がいじってるんだから、誰もさわるなよ！見るだけにしてな！」

っていうのもあれば

「今俺がいじってるんだから、誰もさわるなよ！終わるまで誰も見るな！」

ってのもあります。

さて、この処理の排他ロックはどのタイミングでかかるかということですが

結果から言うと

29行目のExecuteNonQuery()実行時にロックがかかり始めます。

21行目のBeginTransaction() ではないんですねえ・・。

ExecuteNonQueryメソッドの中で、コマンドオブジェクトの中のトランザクションを初期化して、コマンドの内容に従ってロックをかけるという処理を行っています。

「コマンドの内容に従って」ロック処理をかけることになるので、このコマンドオブジェクトに対してトランザクションを割り当てる必要があったのです。

ちなみにこのロックが解放されるのは、トランザクションがコミットされたタイミングかロールバックされたタイミングとなります。

RECOMMENDこちらの記事も人気です。