DB

SSMSでトランザクション管理を有効にしてみた

 

SSMS(SQL Server Management Tool)は、SQL Serverに接続しDB操作を行うためのツールで、デフォルトではオートコミットが有効になっている。そのため、update文やinsert文といったSQLを実行すると、更新内容が即時に確定されてしまうが、SSMSで明示的にコミットやロールバックを行うこともできる。

今回は、SSMSを利用してSQL Serverに接続し、明示的にコミットやロールバックを行ってみたので、その手順を共有する。

データベース設定の確認

SSMSのデータベース接続設定を確認すると、データベース更新時に明示的にコミットやロールバックを行う必要があるかどうか(自動的にトランザクションを開始するかどうか)を確認することができる。その手順は、以下の通り。

1) スタートメニューから「Microsoft SQL Server Tools 18」の「Microsoft SQL Server Management」を選択し、SSMSを起動する。
DB設定_1

2) 接続するSQL Serverのサーバー名・ユーザー名・パスワードを指定し、「Connect」ボタンを押下する。
DB設定_2

3)「Tools」メニューから「Options」を選択する。
DB設定_3

4)「Query Execution」-「SQL Server」-「ANSI」を選択すると、以下の画面のように、「SET IMPLICIT_TRANSACTIONS」のチェックがOFFであるため、オートコミットが有効で明示的にコミットやロールバックを行う必要がないことが確認できる。この画面を閉じるには、「Cancel」ボタンを押下する。
DB設定_4

データベース更新時にオートコミットされることの確認

SSMSでは、デフォルトでは「SET IMPLICIT_TRANSACTIONS」のチェックがOFFになっているため、オートコミットされる。その確認手順は、以下の通り。

1) データベース更新前のuser_dataテーブルの内容は、以下の通り。
オートコミット_1

2) user_dataテーブルのデータを追加／更新するSQLを指定し、「Execute」ボタンを押下する。

update dbo.user_data set name = N'テスト　プリン３更新後' where id = 3;
insert into dbo.user_data values (4, N'テスト　プリン４', 2003, 2, 20, '2', N'テスト４');
オートコミット_2

3)「commit」を指定し「Execute」ボタンを押下すると、以下のように、「COMMIT TRANSACTION 要求に対応する BEGIN TRANSACTION がありません。」というエラーメッセージが表示される。
オートコミット_3

4) データベースから切断後、再度、SSMSでSQL Serverデータベースに接続する。
オートコミット_4_1

オートコミット_4_2 オートコミット_4_3

5) データベース更新後のuser_dataテーブルの内容は以下の通りで、commit文実行は失敗したものの、SQLの更新内容がコミットされていることが確認できる。
オートコミット_5



「Envader」はLinuxコマンドやDatabase SQL等のスキルを、環境構築不要で習得できる学習サイトだった「Envader」は、ITエンジニアとしてよく使うLinuxコマンドやDatabase SQL等のスキルを、解説を読んだ上で、問題を解き...

トランザクションを有効化（ロールバック）

SSMSでは、トランザクションを明示的に開始し、データベース更新時にロールバックを行うこともできる。その手順は、以下の通り。

1) データベース更新前のuser_dataテーブルの内容は、以下の通り。
ロールバック_1

2)「BEGIN TRANSACTION」を指定し「Execute」ボタンを押下すると、トランザクションが開始される。
ロールバック_2

3) user_dataテーブルのデータを追加／更新するSQLを指定し、「Execute」ボタンを押下する。

update dbo.user_data set name = N'テスト　プリン３更新後' where id = 3;
insert into dbo.user_data values (4, N'テスト　プリン４', 2003, 2, 20, '2', N'テスト４');
ロールバック_3

4)「rollback」を指定し「Execute」ボタンを押下する。
ロールバック_4

5) データベースから切断後、再度、SSMSでSQL Serverデータベースに接続する。
ロールバック_5_1

ロールバック_5_2 ロールバック_5_3

6) データベース更新後のuser_dataテーブルの内容は以下の通りで、SQLの更新内容がロールバックされていることが確認できる。
ロールバック_6

トランザクションを有効化（コミット）

SSMSでは、トランザクションを明示的に開始し、データベース更新時にコミットを行うこともできる。その手順は、以下の通り。

1) データベース更新前のuser_dataテーブルの内容は、以下の通り。
コミット_1

2)「BEGIN TRANSACTION」を指定し「Execute」ボタンを押下すると、トランザクションが開始される。
コミット_2

3) user_dataテーブルのデータを追加／更新するSQLを指定し、「Execute」ボタンを押下する。

update dbo.user_data set name = N'テスト　プリン３更新後' where id = 3;
insert into dbo.user_data values (4, N'テスト　プリン４', 2003, 2, 20, '2', N'テスト４');
コミット_3

4)「commit」を指定し「Execute」ボタンを押下する。
コミット_4

5) データベースから切断後、再度、SSMSでSQL Serverデータベースに接続する。
コミット_5_1

コミット_5_2 コミット_5_3

6) データベース更新後のuser_dataテーブルの内容は以下の通りで、SQLの更新内容がコミットされていることが確認できる。
コミット_6



freelance hubを利用して10万件を超える案件情報からJava Spring案件を検索してみたfreelance hubは、レバテックフリーランスやフリエン(furien)を始めとした多くのフリーランスエージェントの案件をまとめて...

データベース更新時に明示的にコミットやロールバックを行う設定にした場合

データベース更新時に明示的にコミットやロールバックを行う設定に変更することもできる。その手順や動作確認結果は、以下の通り。

1)「Tools」メニューから「Options」を選択する。
手動コミット_1

2)「Query Execution」-「SQL Server」-「ANSI」を選択し、「SET IMPLICIT_TRANSACTIONS」のチェックをONに設定後、「OK」ボタンを押下する。
手動コミット_2

3) SSMSを終了後、再度、SSMSでSQL Serverデータベースに接続する。
手動コミット_3_1

手動コミット_3_2 手動コミット_3_3

4) データベース更新前のuser_dataテーブルの内容は、以下の通り。
手動コミット_4

5) user_dataテーブルのデータを追加／更新するSQLを指定し、「Execute」ボタンを押下する。

update dbo.user_data set name = N'テスト　プリン３更新後' where id = 3;
insert into dbo.user_data values (4, N'テスト　プリン４', 2003, 2, 20, '2', N'テスト４');
手動コミット_5

6)「rollback」を指定し「Execute」ボタンを押下する。
手動コミット_6

7) データベースから切断後、再度、SSMSでSQL Serverデータベースに接続する。
手動コミット_7_1

手動コミット_7_2 手動コミット_7_3

8) データベース更新後のuser_dataテーブルの内容は以下の通りで、SQLの更新内容がロールバックされていることが確認できる。
手動コミット_8

要点まとめ

  • SSMS(SQL Server Management Tool)では、デフォルトではオートコミットが有効になっているが、明示的にコミットやロールバックを行うような設定にすることもできる。
RELATED POST