データベース・ファミリ・コース(20)-データベースのデッドロックの原因と解決策

データベースはマルチユーザが使用する共有リソースであり、複数のユーザが同時にデータにアクセスすると、データベースに複数のトランザクションが同時に同じデータにアクセスする場合が発生します.コンカレント・オペレーションを制御しないと、不正なデータが読み込まれ、格納され、データベースの一貫性が損なわれる可能性があります.ロックは、データベースの同時制御を実現するための非常に重要な技術です.実際のアプリケーションでよく発生するロックに関連する異常は、2つのトランザクションが競合するロックのセットを必要とし、トランザクションを続行できない場合、デッドロックが発生し、アプリケーションの正常な実行に深刻な影響を及ぼします.
データベースには、排他ロック(Exclusive Locks、すなわちXロック)と共有ロック(Share Locks、すなわちSロック)の2つの基本的なロックタイプがあります.

データ・オブジェクトに排他ロックが付加されると、他のトランザクションは読み取りおよび変更できません.

共有ロックが追加されたデータ・オブジェクトは、他のトランザクションによって読み込まれますが、変更できません.データベースは、この2つの基本的なロックタイプを使用して、データベースのトランザクションを同時制御します.

この2つのロックによる一般的なデッドロックの状況と解決策をまとめます.

01トランザクション間のリソースアクセス順序の交替

原因:

ユーザAは、テーブルAにアクセスし(テーブルAをロックした)、その後、テーブルBにアクセスする.別のユーザBは、テーブルBにアクセスし(テーブルBをロックした)、テーブルAにアクセスしようとする.このときユーザAは、ユーザBがテーブルBをロックしているため、ユーザBがテーブルBを解放するのを待たなければならず、同様にユーザBがテーブルAを解放するのを待たなければならず、デッドロックが発生する.

解決方法:

このようなデッドロックは一般的で、プログラムのBUGによって発生したもので、調整されたプログラムの論理以外に方法がない.プログラムのロジックをよく分析して、データベースのマルチテーブル操作に対して、できるだけ同じ順序で処理して、できるだけ2つの資源を同時にロックすることを避けて、例えばAとBの2つの表を操作する時、いつも先にAと後のBの順序で処理して、必ず同時に2つの資源をロックしなければならない時、いつでも同じ順序で資源をロックするべきであることを保証します.

02同一レコードの同時変更

原因:

ユーザAは、レコードを照会し、そのレコードを変更する.このときユーザBがこのレコードを修正すると,ユーザAのトランザクションにおけるロックの性質がクエリの共有ロックによって排他ロックに上昇しようとするが,ユーザBにおける排他ロックは,Aが共有ロックが存在するため,Aが共有ロックを解除するのを待たなければならず,AがBの排他ロックによって上昇できない排他ロックは共有ロックを解放することができず,デッドロックが発生する.このデッドロックは比較的隠蔽されているため,やや大きな項目でよく発生する.

一般的な更新モードは、レコードを読み取り、リソース(ページまたは行)の共有(S)ロックを取得し、ローを変更するトランザクションで構成され、ロックが排他(X)ロックに変換される必要があります.2つのトランザクションがリソース上の共有モードロックを取得し、同時にデータを更新しようとした場合、1つのトランザクションはロックを排他的(X)ロックに変換しようとします.共有モードから排他ロックへの変換は、1つのトランザクションの排他ロックが他のトランザクションの共有モードロックと互換性がないため、しばらく待たなければならない.ロック待ちが発生します.2番目のトランザクションは、更新のために排他ロック(X)ロックを取得しようとします.両方のトランザクションが排他的(X)ロックに変換され、各トランザクションは別のトランザクションが共有モードロックを解放するのを待つため、デッドロックが発生します.

解決方法:

a.楽観ロックを使用して制御する.楽観的なロックの多くは、データバージョン(Version)記録メカニズムに基づいて実現されています.つまり、データベース・テーブル・ベースのバージョン・ソリューションでは、データベース・テーブルに「version」フィールドを追加することで、データにバージョンIDを追加します.データを読み出すときは、このバージョン番号とともに読み出し、その後更新するときは、このバージョン番号に1を加算します.このとき、コミットされたデータのバージョンデータは、データベーステーブルに対応して記録された現在のバージョン情報と比較され、コミットされたデータのバージョン番号がデータベーステーブルの現在のバージョン番号より大きい場合は更新され、そうでない場合は期限切れのデータとみなされます.楽観的なロックメカニズムは、長いトランザクションにおけるデータベースのロックオーバーヘッド(ユーザーAとユーザーBの操作中、データベースデータにロックをかけていない)を回避し、大きな同時量でのシステム全体の性能表現を大幅に向上させた.Hibernateは、データ・アクセス・エンジンに楽観的なロックを内蔵しています.楽観的なロックメカニズムは私たちのシステムで実現されるため、外部システムからのユーザー更新操作は私たちのシステムの制御を受けないため、汚いデータがデータベースに更新される可能性があります.

b.悲観ロックを用いて制御する.悲観的なロックの多くは、OracleのSelect...for update文などのデータベースのロックメカニズムによって実現され、操作の最大限の独占性を保証します.しかし、これに伴い、データベースのパフォーマンスに多くのオーバーヘッドが発生します.特に、長いトランザクションでは、このようなオーバーヘッドは耐えられません.ある金融システムのように、あるオペレータがユーザのデータを読み取り、読み出したユーザデータに基づいて修正を行う場合(ユーザ口座残高の変更など)、悲観的なロックメカニズムを採用すると、操作過程全体(オペレータがデータを読み出し、修正結果を提出するまでの全過程、さらにはオペレータが途中でコーヒーを沸かす時間を含む)を意味し、データベース記録は常にロック状態にあり、何百もの同時多発に直面すれば、このような状況は災難的な結果をもたらすことが予想される.したがって,悲観ロックによる制御は必ず考慮しなければならない.

c.SqlServerは更新ロックをサポートできる
デッドロックを解決するために、SqlServerは次のような特徴を持つ更新ロックを導入します.

ロックの条件:トランザクションがupdate文を実行すると、データベース・システムはトランザクションに更新ロックを割り当てます.

ロックを解除する条件:データの読み取りが完了し、更新操作が実行されると、更新ロックが独占ロックにアップグレードされます.

と他のロックとの互換性:更新ロックは共有ロックと互換性があり、つまり、1つのリソースは更新ロックと共有ロックを同時に配置することができるが、最大1つの更新ロックを配置することができる.これにより、複数のトランザクションが同じデータを更新すると、1つのトランザクションのみが更新ロックを取得し、更新ロックを排他ロックにアップグレードします.他のトランザクションは、前のトランザクションが終了するまで待たなければ更新ロックを取得できません.これにより、デッドロックが回避されます.

同時パフォーマンス:複数のトランザクションがロックされたリソースを同時に読み込むことができますが、他のトランザクションは変更できません.例は、

T1:
begin tran
select * from table(updlock) ( )
update table set column1='hello'

T2:
begin tran
select * from table(updlock)
update table set column1='world'

更新ロックとは、「私は今読みたいだけです.他の人も読むことができますが、将来更新操作をする可能性があります.共有ロックから排他ロック(更新用)までの資格を取得しました」という意味です.1つの物事には更新ロックが1つしかありません.T 1はselectを実行し、更新ロックを加える.T 2は運行して、更新ロックを加えるつもりですが、すでに1つの更新ロックがそこにあることに気づいて、待つしかありません.後でuser 3,user 4...tableテーブルのデータをクエリーする必要がある場合、T 1のselectが実行されているからといってブロックされることはなく、クエリーが可能になり、効率が向上します.

03インデックスが不適切なため、テーブル全体がスキャンされます。

原因:

トランザクションで条件を満たさない文を実行し、全テーブルスキャンを実行し、ロー・レベル・ロックをテーブル・レベル・ロックに上昇させると、複数のトランザクションが実行されると、デッドロックやブロックが発生しやすくなります.同様に、テーブル内のデータ量が非常に膨大でインデックスが少なすぎる場合や不適切な場合、テーブル全体のスキャンが頻繁に発生し、最終的にアプリケーションシステムがますます遅くなり、最終的にブロックやデッドロックが発生します.

解決方法:

SQL文では、複雑な複数のテーブルを関連付けたクエリーは使用しないでください.「実行計画」を使用してSQL文を分析し、テーブル全体をスキャンしたSQL文に対して、適切なインデックスを作成して最適化します.

04トランザクションのブラックアウト範囲が広く、互いに待機している

https://blog.csdn.net/qq_16681169/article/details/73359670