トランザクション分離レベルの設定方法

Question

SQL Server 初心者です。 よろしくお願い致します。

別のPC(他アプリケーション)から、SQL Serverへ接続時、データベースがロックされ、

ネットワーク内の他のPCから、MDFファイルにアクセス出来ず、困っております。

対策として、トランザクション分離レベルの設定変更を考えているのですが、正しいでしょうか？

既定の「READ COMMITTED」から、「READ UNCOMMITTED」に変更したいのですが、

変更方法がよく分かりません。

申し訳ありませんが、ご教授いただけると幸いです。

以上、よろしくお願い致します。

Answer 1

>ネットワーク内の他のPCから、MDFファイルにアクセス出来ず、困っております。
ファイルにアクセス出来なくて困る、というのはおかしいと思いますよ。
1つの(MDFを開いている)SQLServerに接続して、SQLを発行するわけですから。（トランザクション分離モデルは、発行したSQLが戻ってこなくなったときに出てくる話です。）
それとも、LocalDBモードで使っているMDFをSMB共有していて、多PCからAttachDBFileしたら駄目だった、という系統の話ですか？であれば、SQLServerの使い方が間違っているといえますね。

---

jzkey

Answer 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

select * from Table

↑こんな感じで、ISOLATION LEVELを設定できます。

---------------------

参考になりましたら、投票と回答としてマークをお願いいたします。

Answer 3

「READ UNCOMMITTED」に変更した場合、ダーティリード(コミットされていないデータが参照される)が発生するため、システムとしてダーティリードが許されるのかについては、しっかりと検討されると良いかと思います。
今回の現象は、ファイルシステムレベルの問題(SQL Server プロセスからデータベース物理ファイル(.mdf ファイル)にアクセスできない) ではなく、ブロッキング(ロックリソースの競合)が発生しているのであれば、まずはブロッキングが発生している流れを追い、対処を考えることを推奨しますが、トランザクション分離レベルを変更することで、ブロッキングの現象を解消できる可能性はあるかと思います。
なお、READ_COMMITTED_SNAPSHOTを有効化することで、Oracleで言う読み取り一貫性が有効となり、例えば、共有ロック(S)と排他ロック(X)で競合が発生しなくなるため、ダーティリードを発生させずにブロッキングが発生する頻度を軽減できる可能性があるかと思います。

ALTER DATABASE の SET オプション (Transact-SQL)
https://docs.microsoft.com/ja-jp/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15
READ_COMMITTED_SNAPSHOT { ON | OFF }
ON

Answer 4

maaaaaaaa8さん、ご連絡ありがとうございます。

Management Studio の新しいクエリで、SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED と実行し、

直後は「READ UNCOMMITTED」に設定変更できたのですが、

Management Studio を閉じて、再度立ち上げ、設定を確認すると「READ COMMITTED」に戻ってました。

デフォルト設定を「READ UNCOMMITTED」に固定するにはどうしたらいいのでしょうか？

ご教授いただけると幸いです。

以上、よろしくお願い致します。

Answer 5

NOBTAさん、ご連絡ありがとうございます。

READ_COMMITTED_SNAPSHOTを有効化して試してみましたが、状況は変わらず、

データベースがロックされているのか、他PCからはアクセス出来ませんでした。

他に確認する事はありますでしょうか？

ご教授いただけると幸いです。

以上、よろしくお願い致します。

Answer 6

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED で、トランザクション分離レベルを 「READ UNCOMMITTED」 に変更した場合、現在発生している問題は解決したのですかね？

残念ながら、READ UNCOMMITTEDを既定値に変更することはできないため、トランザクション単位で明示的にトランザクション分離レベルを指定する必要があります。

「他PCからはアクセス出来ませんでした。」 の現象発生時、どのようなエラーが発生していますかね？ また、その現象が発生時、SQL Server エラーログに何かエラーは発生している感じでしょうか？

SQL Server エラーログは、既定の設定で、C:\Program Files\Microsoft SQL Server\MSSQL**.<インスタンス名>\MSSQL\Log 配下にある ERRORLOG (ERRORLOG.* は世代) というファイルになります。

Answer 7

e07412さん、こんにちは。フォーラムオペレーターのHarukaです。
MSDNフォーラムにご投稿くださいましてありがとうございます。

既定の「READ COMMITTED」から、「READ UNCOMMITTED」に変更して、問題は解決できると思います。
まず、SET TRANSACTION ISOLATION LEVEL (Transact-SQL)を実現してください。

アクセスが原因である可能性もあります。
このリンクを確認してください。データベース エンジン アクセスのファイル システム権限の構成。

まだ問題を解決できなかった場合は、エラーメッセージと詳細な情報をご共有いただけますでしょうか。

どうぞよろしくお願いいたします。

---

MSDN/ TechNet Community Support Haruka
～参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、 ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、[e-mail]までお気軽にお問い合わせください。～

Answer 8

そもそもですが、jzkeyさん指摘のとおり質問文をそのままうけとるとSQLServerの利用方法についての認識齟齬があるようにみえます。

どういった環境でどのような利用方法で状況が発生しているのかを明確にするのが先決だと思いますが。
それによってはまったく別方向での解決方法となることも考えられます。