SQL Server 2008が長いトランザクションのINSERTでSELECTをブロックするのはなぜですか?

sql-server sql-server-2008

定期的に新しいレコードを挿入するだけのトランザクションテーブルを作成しようとしています。

この単純なテーブルでは、新しいレコードを継続的に追加する必要があります。 このテーブルへのトランザクションの量はかなり高いと予想され、またトランザクションの完了までに数秒かかるかもしれないトランザクションの定期的なバッチインポート(> 1000)があるかもしれません。

このデータから、必要な値を返すために異なる列をグループ化した一連のselectステートメントを実行します。

最初のテストから、INSERTSのトランザクションの最中にSELECTをブロックするSQL Serverに関連するボトルネックが見つかりました。

以下は、問題を説明するために実行できる簡単な例です。

  • 簡易DBテーブル

テーブルLOCK_TESTを作成します(LOCK_TEST_ID int identity、AMOUNT int)。
  • 1つのクエリウィンドウでこれを実行する

LOCK_TEST(AMOUNT)値への挿入を開始します(1)。 WAITFOR DELAY '00:00:15 '---- 15 LOCK_TEST(AMOUNT)の値に15秒の遅延が挿入されます(1)。コミット
  • クエリ2では、これを並行して実行します。

LOCK_TESTからSUM(AMOUNT)を選択します。

私はQuery 2がすぐに戻ってくるのを期待し、Query 1が完了するまで0を返し、そして2を表示します。 2番目のクエリから1が返されるのを見たくありません。

私たちが見た答えは、selectステートメントのWITH(NOLOCK)に関連しています。 しかし、これはトランザクションの境界に違反しており、返される情報は本質的に経済的なものである可能性があるため、クエリで詳細な情報が表示されることは望ましくありません。

私の問題はINSERT側にあるようです…​ 既存のデータを変更していなくても、INSERTがSELECTステートメントをブロックするのはなぜですか?

*ボーナスポイントの質問:*これはSQL Serverの “機能”ですか?それとも他のデータベースフレーバーでも見つけられるでしょうか?

*更新*私はローカルのOracleデータベースを見つけて同じ簡単なテストを実行する時間がありました。 このテストに合格するのは予想通りです。

つまり、私はいつでもqueryを実行することができ、最初のトランザクションがコミットされるまでnullを返し、その後2を返します。

  • SQL Serverをこのように機能させる方法はありますか? それともOracleに移行する必要がありますか?*

  16  7


ベストアンサー

このロック動作はSQL Serverの機能です。 2005以上では、 ローレベルバージョン管理(Oracleではデフォルトで使用されているもの)を使用して、次のことを実現できます。同じ結果 tempdbは行レベルのバージョン管理を維持するため、これはtempdbに余分な負担をかけます。そのため、必ずこれに対応してください。 SQLをあなたの望む通りに振る舞わせるためには、これを実行してください:

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

12


これはSQL ServerとSybaseの(少なくとも)標準的な動作です。

データの変更(挿入、更新、削除)にはhttp://msdn.microsoft.com/en-us/library/ms175519.aspx [排他ロック]が必要です。 これにより、読者はブロックされます。

_
排他(X)ロックでは、他のトランザクションはデータを変更できません。読み取り操作は、NOLOCKヒントを使用するか、コミットされていない分離レベルを読み取ることによってのみ実行できます。
_

SQL Server 2005以降では、スナップショット分離(行バージョン管理)を導入しました。 MS BOLより:http://msdn.microsoft.com/en-us/library/ms189050.aspx [行のバージョン管理ベースの分離レベルの理解]] これは、ある読者が最新のコミット済みデータを持っていることを意味しますが、その後書き戻したい場合は、ブロッキングトランザクションで変更されたためにデータが間違っていることがわかります。

これが、ベストプラクティスがトランザクションを短くすることである理由です。 たとえば、BEGIN / COMMITの間に必要なものだけを処理し、トリガーなどからEメールを送信しないでください。

編集する

このようなロックは、SQL Serverでは常に発生します。 ただし、長すぎるロックはブロッキングとなり、パフォーマンスが低下します。 長すぎるのは明らかに主観的です。

4


これはMySQLにも存在します。 その背後にある論理は、次のとおりです。あるデータに対してSELECTを実行した場合、それは最新のデータセットに対して機能すると予想されます。 これが、INSERTがテーブルレベルのロックになる理由です(つまり、innodbのようにエンジンが行レベルのロックを実行できない場合)。 この動作を無効にして “ダーティリード”を実行できるようにする方法はいくつかありますが、それらはすべてソフトウェア(またはデータベースエンジンでさえ)固有のものです。

1


私もこの問題に遭遇しました。 調査の結果、それ自体はロックされているのはデータではなく、挿入の結果として変更されていたクラスタード・インデックスであるように見えました。 クラスタ化インデックスはデータページにあるため、関連付けられているデータ行もロックされます。

1


タイトルとURLをコピーしました