SQL Serverのデッドロックを減らす方法
しげん
の中で、2つ以上のスレッドは実行中、ある資源を争っている間に互いに待つ現象をもたらし、外力の作用がなければ、それらは推進できず、死ぬとデッドロックが発生する可能性があり、これらは永遠に互いに待っている.
プロセスをデッドロックスレッドと呼ぶ
簡単に言えば、プロセスAはプロセスBが彼のリソースを解放するのを待っていて、BはまたAが彼のリソースを解放するのを待っていて、このように互いに待つとデッドロックになります.
データベース#データベース#において、1つのデータを修正する必要がある場合、まずデータベース管理システム
同じ時間に変更できるトランザクションが1つしかないことを保証するために、ロックが追加されます.トランザクション1のスレッドT 1にテーブルA上の排他ロックがある場合、トランザクション2のスレッドT 2にテーブルB上の排他ロックがあり、その後、テーブルA上のロックが必要となる.トランザクション2は、トランザクション1がすでに所有しているため、このロックを取得できません.トランザクション2がブロックされ、トランザクション1が待機します.その後、トランザクション1はテーブルBのロックを必要とするが、トランザクション2がロックしているため、ロックを取得できない.トランザクションは、コミットまたはロールバックの前に保持されているロックを解放できません.トランザクションには相手がコントロールするロックが必要です
才能
操作を続行すると、コミットまたはロールバックできないため、データベースにデッドロックが発生します.
きおくてつづきを記述する
の場合、ストアド・プロシージャのトランザクションが頻繁に行われるため、テーブルAをロックしてからテーブルBをロックすると、すべてのストアド・プロシージャでこの順序でロックされます.ストレージ中にテーブルBをロックしてからテーブルAをロックすると、デッドロックになる可能性があります.しかもデッドロックは一般的には容易ではありません
検出
のです.
サーバ
このようなデッドロックが頻繁に発生すると、サーバの
パフォーマンス
ああ、だからアプリケーションが使用する時、私たちはそれを追跡してsp_を使う必要があります.whoとsp_who 2は、どのユーザが他のユーザをブロックしている可能性があるかを決定し、次のストレージ・プロシージャを使用して、特定のデッドロック実行の影響を追跡することもできます.
create procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
IF @@ERROR<>0 RETURN @@ERROR
--
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '
' as message
--
while @intCounter <= @intCountProperties
begin
--
select @spid = spid,@bl = bl
from #tmp_lock_who where id = @intCounter
begin
if @spid =0
select '
: '+ CAST(@bl AS VARCHAR(10)) + ' , SQL '
else
select ' SPID：'+ CAST(@spid AS VARCHAR(10))+ ' ' + ' SPID：'+ CAST
(@bl AS VARCHAR(10)) +' , SQL '
DBCC INPUTBUFFER (@bl )
end
--
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
クエリー・アナライザでspを実行するだけですwho_lockは、実行される渋滞プロセスを具体的にキャプチャすることができます.この場合、対応するSQL文またはストレージプロセスのパフォーマンスの向上と
デザイン
.
したがって、データベース設計では、デッドロックを完全に回避することはできませんが、デッドロックの数を最小限に抑えることができます.トランザクションのスループットを増やし、システム・オーバーヘッドを削減します.トランザクションが少ないため、次の原則に従います.
オブジェクトに同じ順序でアクセス
すべての同時トランザクションが同じ順序でオブジェクトにアクセスすると、デッドロックが発生する可能性が低下します.SQL文またはストアド・プロシージャを書くときは、2つの同時トランザクションでテーブルAのロックを順番に取得し、テーブルBのロックを取得する必要があります.最初のトランザクションが完了する前に、別のトランザクションがテーブルAにブロックされます.最初のトランザクションがコミットまたはロールバックされた後、2番目のトランザクションは続行され、文にテーブルBのロックを先に取得してからテーブルAのロックを取得すると書くことはできません.
トランザクション内のユーザー・インタラクションの回避
ユーザー・インタラクションのないバッチの実行速度は、ユーザーがクエリーに手動で応答する速度よりもはるかに速いため、ユーザー・インタラクションを含むトランザクションの作成は避けます.たとえば、回答
アプリケーション
パラメータを要求するプロンプト.たとえば、トランザクションがユーザーの入力を待っていて、ユーザーが別のことをしている場合、ユーザーはこのトランザクションを保留して完了させません.これにより、トランザクションが保持するロックは、トランザクションのコミットまたはロールバック時にのみ解放されるため、システムのスループットが低下します.デッドロックが発生しなくても、同じリソースにアクセスする他のトランザクションはブロックされ、トランザクションの完了を待機します.
トランザクションを短く維持し、バッチで
通常、同じデータベースで長時間実行する必要がある複数のトランザクションを同時に実行すると、デッドロックが発生します.トランザクションの実行時間が長ければ長いほど、排他的ロックまたは更新ロックを保持する時間が長くなり、他のロックが詰まります.
アクティブ
デッドロックを引き起こす可能性があります.トランザクションをバッチに保持し、トランザクションのネットワークを最小化
つうしん往復量は、トランザクションの完了の遅延を低減し、ロックを解放します.
低い独立性レベルの使用
トランザクションがより低い独立性レベルで実行できるかどうかを確認します.コミット・リードを実行すると、トランザクションが最初のトランザクションの完了を待つことなく、別のトランザクションが読み込まれた(変更されていない)データを読み込むことができます.高い独立性レベル(シリアル読み取りなど)を使用せずに低い独立性レベル(コミット読み取りなど)を使用すると、共有ロックを保持する時間が短縮され、ロック競合が低減されます.
バインド接続の使用
バインド接続を使用すると、同じアプリケーションで開いている2つ以上の接続が相互に連携できます.二次接続によって得られる任意のロックは、主接続によって得られるロックのように保持することができ、逆に、互いにブロックされない.
デッドロックに関するいくつかのアドバイスがあります.
1)頻繁に使用されるテーブルに対してクラスタ化されたインデックスを使用する.
2)大量記録のT-SQL文、特にINSERTとUPDATE文に一度に影響しないように工夫する.
3)UPDATE文とDELETE文にインデックスを使用するように工夫する.
4)ネストされたトランザクションを使用する場合は、コミットおよびロールバックを回避
コンフリクト
;
5)一部のデータに対して更新値を適時に読み取る必要がないテーブルに対してSQLを書く時にテーブルのバックグラウンドに(nolock)を付けて、例えば:Select*from tablea(nolock)