sqlserverでselectがデッドロックを起こす
2653 ワード
プロジェクトがオンラインになり、検収の準備をする前に深刻な問題が発生しました.多くのselect文はデッドロックの犠牲として、ほとんどのレポートが開くことができません.この問題の影響範囲は大きく、すべてのレポートにアクセスできませんが、私たちのレポートはテレビの上に置いて放送されています.テレビは工場の中に置いてあるので、問題が発生したら、工場全体が知っています.
この問題を解決するのは曲がりくねっていて、まずSAPインタフェースを書いた同僚が問題を発見しました:SAPはずっと間違ったデータを伝えて生産量表がロックされています.SAP転送のエラーデータを修正した後、このデッドロックの問題は発生しませんでした.しかし、私が本番環境サーバーのログを見たとき、この問題は依然として存在していることに気づきました.お客様がこの問題を提起していないため、私は時間をかけて修正する理由がありません.他のプロジェクトが忙しいからです.
問題は常に存在し、その暴露は時間の問題にすぎない.一週間が過ぎて、私たちがプロジェクトの検収について話している間に、この問題がまた明らかになった.影響が非常に大きいため、お客様に悪い印象を与えます.だからこの問題を修正してボスの支持を得て、正直に言って、私も初めてこのような問題に出会って、select文がどうしてデッドロックになったのか分からない.私はこの問題がとても頭が痛いことを知っていて、幸いにもボスの支持を得て、十分な时に私が問題を解決することができて、私も自信があります.
ネット上で多くの文章を探して、私の解決の構想は:文を検索することを通じてデッドロックの関連するsql文を探して、犠牲になったあのsql文だけを発見して、もう一つのsql文は探し当てていないで、この道は通じません.次に問題を再現し、問題を解決します.この道は最初は通じなかったし、通じなかったし、午後に問題が発見されたかどうかを再現した.もうすぐ退勤する時、私は迷った.翌日またネット上で半日の資料を探して、この時sql server profilerでデータベースを傍受することを考えて、悲劇的なのは取引先のあちらが休みになって、問題の生産環境のデータベースにつながっていません.それから同僚とこの問題について話して、彼の提案は依然として再現して、それから再現する方法があることを覚えていて、この時私はやっとこのことを思い出しました.
再現方法は、sql文が生産量テーブルにループしてデータを挿入することです.レポートの多くは生産量テーブルを読み取り、レポートは常にデッドロックになります.この時私は希望を見て、ただselect文がどのようにデッドロックを引き起こすのか理解していないで、それからネット上で真剣に1篇の文章を読みました:SqlServerの中でselect文が引き起こしたデッドロック(http://www.csharpwin.com/csharpspace/11505r288.shtml)、この文章を読んで、私が出会った状況とよく似ていると思います.
彼の理論を通じて私はデッドロックの過程を分析した.
select文では、非クラスタインデックスを使用して生産量情報をクエリーすると、非クラスタインデックスに共有ロックが追加されます.非クラスタインデックスにはselectのすべてのデータ列がないため、ブックマーク検索が表示されるため、生産量テーブルをクエリーする必要があります.生産量表を検索する場合、生産量表データに共有ロックを追加する必要があります.Update文が生産量表を更新した後、排他ロックを解除するのを待つ必要があります.つまり、SelectはUpdateがロックを解除するのを待っています.
このとき生産量テーブルのUpdate/Insert文が生産量情報を更新すると、ファミリーインデックスに位置付けをしたり、排他的ロックを追加したり、非ファミリーインデックスを修正したりする情報が追加されます.問題は、非ファミリーインデックス情報を修正する際に、非ファミリーインデックスに排他的ロックを追加する必要があることです.select文はすでにファミリーインデックスに共有ロックを追加しており、排他ロックを追加するには解放する必要があります.すなわちupdate文はselect文がロックされるかどうかを待つ必要があります.
これでデッドロックが形成されます.
これにより、クエリー文に共有ロックを付けるだけで問題が解決され、sql server行バージョンレベル制御は私の問題を解決します.
ロー・バージョン管理ベースの独立性レベルの使用:ロー・バージョン管理ベースの独立性の下で実行されるトランザクションがデータを読み込むと、読み込み中のデータの共有ロック(Sロック)は取得されません.
ロー・バージョン・レベルを最も早く設定する方法を見つけます.
不思議なことに、このように設定すると、デッドロックの問題は存在しません.
クエリが正常に設定されたかどうか:select is_read_committed_snapshot_on from sys.databases where name = DB_Name()
この問題を解決するのは曲がりくねっていて、まずSAPインタフェースを書いた同僚が問題を発見しました:SAPはずっと間違ったデータを伝えて生産量表がロックされています.SAP転送のエラーデータを修正した後、このデッドロックの問題は発生しませんでした.しかし、私が本番環境サーバーのログを見たとき、この問題は依然として存在していることに気づきました.お客様がこの問題を提起していないため、私は時間をかけて修正する理由がありません.他のプロジェクトが忙しいからです.
問題は常に存在し、その暴露は時間の問題にすぎない.一週間が過ぎて、私たちがプロジェクトの検収について話している間に、この問題がまた明らかになった.影響が非常に大きいため、お客様に悪い印象を与えます.だからこの問題を修正してボスの支持を得て、正直に言って、私も初めてこのような問題に出会って、select文がどうしてデッドロックになったのか分からない.私はこの問題がとても頭が痛いことを知っていて、幸いにもボスの支持を得て、十分な时に私が問題を解決することができて、私も自信があります.
ネット上で多くの文章を探して、私の解決の構想は:文を検索することを通じてデッドロックの関連するsql文を探して、犠牲になったあのsql文だけを発見して、もう一つのsql文は探し当てていないで、この道は通じません.次に問題を再現し、問題を解決します.この道は最初は通じなかったし、通じなかったし、午後に問題が発見されたかどうかを再現した.もうすぐ退勤する時、私は迷った.翌日またネット上で半日の資料を探して、この時sql server profilerでデータベースを傍受することを考えて、悲劇的なのは取引先のあちらが休みになって、問題の生産環境のデータベースにつながっていません.それから同僚とこの問題について話して、彼の提案は依然として再現して、それから再現する方法があることを覚えていて、この時私はやっとこのことを思い出しました.
再現方法は、sql文が生産量テーブルにループしてデータを挿入することです.レポートの多くは生産量テーブルを読み取り、レポートは常にデッドロックになります.この時私は希望を見て、ただselect文がどのようにデッドロックを引き起こすのか理解していないで、それからネット上で真剣に1篇の文章を読みました:SqlServerの中でselect文が引き起こしたデッドロック(http://www.csharpwin.com/csharpspace/11505r288.shtml)、この文章を読んで、私が出会った状況とよく似ていると思います.
彼の理論を通じて私はデッドロックの過程を分析した.
select文では、非クラスタインデックスを使用して生産量情報をクエリーすると、非クラスタインデックスに共有ロックが追加されます.非クラスタインデックスにはselectのすべてのデータ列がないため、ブックマーク検索が表示されるため、生産量テーブルをクエリーする必要があります.生産量表を検索する場合、生産量表データに共有ロックを追加する必要があります.Update文が生産量表を更新した後、排他ロックを解除するのを待つ必要があります.つまり、SelectはUpdateがロックを解除するのを待っています.
このとき生産量テーブルのUpdate/Insert文が生産量情報を更新すると、ファミリーインデックスに位置付けをしたり、排他的ロックを追加したり、非ファミリーインデックスを修正したりする情報が追加されます.問題は、非ファミリーインデックス情報を修正する際に、非ファミリーインデックスに排他的ロックを追加する必要があることです.select文はすでにファミリーインデックスに共有ロックを追加しており、排他ロックを追加するには解放する必要があります.すなわちupdate文はselect文がロックされるかどうかを待つ必要があります.
これでデッドロックが形成されます.
これにより、クエリー文に共有ロックを付けるだけで問題が解決され、sql server行バージョンレベル制御は私の問題を解決します.
ロー・バージョン管理ベースの独立性レベルの使用:ロー・バージョン管理ベースの独立性の下で実行されるトランザクションがデータを読み込むと、読み込み中のデータの共有ロック(Sロック)は取得されません.
ロー・バージョン・レベルを最も早く設定する方法を見つけます.
if(charindex('Microsoft SQL Server 2008',@@version) > 0)
begin
declare @sql varchar(8000)
select @sql = '
ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;'
Exec(@sql)
end 不思議なことに、このように設定すると、デッドロックの問題は存在しません.
クエリが正常に設定されたかどうか:select is_read_committed_snapshot_on from sys.databases where name = DB_Name()