- PR -

Sql Server2005の行ロックについて

1
投稿者投稿内容
ケイ
大ベテラン
会議室デビュー日: 2004/04/20
投稿数: 100
投稿日時: 2006-09-21 19:30
現在SqlServer2005+Vb6.0で開発を行っています。

Lan上で複数PCが同時に処理を行うようなアプリを作成しているのですが、
トランザクションを実行後コミットを行うまでに
別Pcで同一テーブルの更新を行うとロックエラーになってしまいました。

下記がその処理です。

//データ
create table Tes
(Code int not null ,
Name varchar(20)
Day datetime,
Kubun bit,
primary key (Code))

Insert into Tes (Code ,Name ,Day, Kubun) Values(1,'1の名前',GETDATE(),1);
Insert into Tes (Code ,Name ,Day, Kubun) Values(2,'2の名前',GETDATE(),1);
Insert into Tes (Code ,Name ,Day, Kubun) Values(3,'3の名前',GETDATE(),1);
Insert into Tes (Code ,Name ,Day, Kubun) Values(4,'4の名前',GETDATE(),1);
Insert into Tes (Code ,Name ,Day, Kubun) Values(5,'5の名前',GETDATE(),1);
Insert into Tes (Code ,Name ,Day, Kubun) Values(6,'6の名前',GETDATE(),1);
Insert into Tes (Code ,Name ,Day, Kubun) Values(7,'7の名前',GETDATE(),1);
Insert into Tes (Code ,Name ,Day, Kubun) Values(8,'8の名前',GETDATE(),1);
Insert into Tes (Code ,Name ,Day, Kubun) Values(9,'9の名前',GETDATE(),1);
Insert into Tes (Code ,Name ,Day, Kubun) Values(10,'10の名前',GETDATE(),1);

create table TesUp1
(Code int not null);

create table TesUp2
(Code int not null);

Insert into TesUp1 (Code) Values(1);
Insert into TesUp1 (Code) Values(2);

Insert into TesUp2 (Code) Values(8);
Insert into TesUp2 (Code) Values(9);


1.PcAでトランザクション実行
2.PcAでUpdate 文を実行
Update Tes set Day = GETDATE() ,Kubun = 1
where Code in(select Code From TesUp1)

3.PcBでトランザクション実行
4.PcBでUpdate文を実行
Update Tes set Day = GETDATE() ,Kubun = 1
where Code in(select Code From TesUp2)

4.を実行するとロックエラーが発生する。

Update文は、プライマリーキーに指定してあるCodeを
Where条件にしている為、行ロックでの更新を行っている
と思っているのですがロックエラーが発生します。

Sql Server Profilerでロックエスカレーションが発生して
いない事を確認したので、テーブルロックにはなっていないと思うのですが、

どうしてロックエラーが発生してしまうのでしょうか？

プライマリーキーの列をWhere条件に含めていた場合、
行ロックになると思っていたのですが、
違いましたでしょうか？

その場合、どのようにしたら確認できるでしょうか？
かずくん
ぬし
会議室デビュー日: 2003/01/08
投稿数: 759
お住まい・勤務地: 太陽系第三惑星
投稿日時: 2006-09-21 20:47
せめて、エラーメッセージの内容を、表示されたまま、省略せずに提示した方が良くね？

もしかしたら、エラーコードから原因をたどることができるかもしれんし。
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2006-09-21 23:22
引用:
テーブルロックにはなっていないと思うのですが、

テーブルロックになってないのは、おそらくデータ件数が少ないから。実際にはテーブルロックと同等の行数の行ロック(共有S)を要求しているものと思います。

引用:
プライマリーキーの列をWhere条件に含めていた場合、行ロックになると思っていたのですが、違いましたでしょうか？

これは SQL Server のオプティマイザが頭が悪いからだと思います。SQL Server が Where 句の条件と統計情報をもとに結果行数を予想して適切なインデックスとインデックス走査方法を決定しますが。Where 句の条件にユーザー定義関数やパラメタ変数が使われている場合、統計情報が利用できないという問題があります。

たとえば、where Code between 10 and 20 という条件を指定した場合、SQL Server は統計情報を参照して 10 から 20 のデータがどのくらいの件数になるか判断します。(結果予想が多くなる場合は、はじめからテーブルスキャンを選択します。)

しかし、条件が where Code between @START and @END のようにパラメタ変数になっていたり、where Code between dbo.func1(...) and dbo.func2(...) のように関数でラップされていたりすると、実行プラン立案時に統計情報を利用できず、テーブルスキャンまたはインデックススキャンが選択されてしまいます。

おそらく、サブクエリによる記述でも同様に統計情報が利用できていないのではないでしょうか？ まずは、実行プランを表示してインデックスシークになっているか確認してください。インデックスシークではなくインデックススキャンになっている場合は、お互いのプロセスが共有ロックを保持したあとに、排他ロックを要求することになるため、変換デッドロックが発生しているもの予想します。

･･･というかプロファイラ使ってるならロックイベント追加してみれば、エラーが記載されていない状況から第三者が予想するよりも、あなた自身のほうが分かるでしょう？
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2006-09-21 23:28
おっと。回避策を書くのを忘れていました。せっかく SQL Server 2005 を使われているということなので、2005 であらたに追加されたふたつのトランザクション分離レベルのうちのひとつ、バージョンニングされた READCOMMITTED を使用するのが良いと思います。

バージョニングされた READCOMMITTED は、従来の READCOMMITTED と異なり、読取時に共有ロックをかけないものです。乱暴な言い方をすれば、Oracle の読取一貫性というやつですかね。これは分離レベル READCOMMITTED の振る舞いを DBMS 側で変えるものなので、いままで作成したアプリケーションを変更する必要はありません。(アプリケーションで分離レベルを明示的に指定する必要はありません。)
ケイ
大ベテラン
会議室デビュー日: 2004/04/20
投稿数: 100
投稿日時: 2006-09-22 14:16
返信が遅くなって申し訳ありません。

引用:

未記入さんの書き込み (2006-09-21 23:22) より:

･･･というかプロファイラ使ってるならロックイベント追加してみれば、エラーが記載されていない状況から第三者が予想するよりも、あなた自身のほうが分かるでしょう？


エラー内容は下記が発生しています。

エラー番号:-2147217871
エラーMsg :ロック要求がタイムアウトしました。

情け無い話ではありますが、プロファイラや実行プランの出力情報のどこでもって
どのようなロック状況になっていると判断していいのかがわかりません。

引用:


おそらく、サブクエリによる記述でも同様に統計情報が利用できていないのではないでしょうか？ まずは、実行プランを表示してインデックスシークになっているか確認してください。インデックスシークではなくインデックススキャンになっている場合は、お互いのプロセスが共有ロックを保持したあとに、排他ロックを要求することになるため、変換デッドロックが発生しているもの予想します。



という事は、検索している行すべてがロックされてしまい、
結局テーブルロックと同様になっているかもしれないという事でしょうか？

実行プランを取得してみました。

Update
↑
Clustered index Update
↑
Compute Scalar
↑
Top
↑
Merge Join
↑←Sort ← Table Scan
Clustered Index Scan

ここでClustered Index Scanとなっているという事はテーブルロックに
なっているだろうと考えてよろしいのでしょうか？


また回避策としまして、READCOMMITTEDを使用するのがいいとあります。
今までアプリではREAD UNCOMMITTEDを使用していました。

これはselect文がロックがかかっている際に、select文が戻って来ない事が
あった為、それを回避する為だと聞いてはいます。

READCOMMITTEDを使用した場合、READ_COMMITTED_SNAPSHOT がOffの場合は、
トランザクションがコミットされるまではselect文は待機されてしまいますよね？

また、READ_COMMITTED_SNAPSHOT がOnにした場合は、tempdbを使用して
トランザクションがコミットされなくてもselect文は取得はできますが、

別行の同時更新(Update)を行いたいので、結局Update文がテーブルロックになっている
場合、ロックエラーになってしまわないですか？
※試してみた所、「ロック要求がタイムアウトしました。」が発生したのですが・・・・
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2006-09-22 18:34
まずはじめに。元質問をちょっと読み違えていました。「トランザクション実行」というのは明示的なトランザクションの開始ということですね？ なんらかのロックを取得する処理かと思っていました。それぞれ PcA, PcB で Update 文をひとつずつしか流さないということであれば、デッドロックにはならないです。単純に更新ロックでブロッキングが発生しているようですね。

引用:
という事は、検索している行すべてがロックされてしまい、結局テーブルロックと同様になっているかもしれないという事でしょうか？

いいえ違います。テーブルロックと同様になっているのではなく、テーブルスキャンと同様になっているだけです。

PcA は 1行目のデータに更新ロックをかけてから、データの中身を確認します。データが Code=1 で条件と合致しているので更新をおこないます。その結果、1行目には排他ロックがかかったままになります。次に、PcA は 2行目のデータに更新ロックをかけてからデータの中身を確認します。データが Code=2 で条件と合致しているので更新をおこないます。その結果、2行目には排他ロックがかかったままになります。次に PcA は 3行目のデータに更新ロックをかけてからデータの中身を確認します。データは条件と合致していないので、3行目にかけた更新ロックをはずします。次に PcA は 4行目の･･･ とすべてのデータに更新ロックをかけて・はずしてスキャンをおこないます。

最終的に PcA は、1行目と2行目に排他ロックをかけた状態で、アイドル状態(未コミット)となります。

PcB も同様の手順を踏むことになるのですが･･･。PcB は 1行目のデータに更新ロックをかけて、データの中身を確認したいのですが、PcA が 1行目のデータに排他ロックをかけているため、更新ロックをかけることができずに待たされます。これが今回の問題の原因です。

なぜ、インデックスが使用されずにすべての行を検査してしまうのかという点は、前回説明した通りです。試しに PcA の where 句を Code in (1, 2)、PcB の where 句を Code in (8, 9) としてみてください。スキャンをする必要がなくなる(代わりにインデックスシークになるはずです)ので、PcA は 1行目と 2行目のみをピンポイントで更新、PcB は 8行目と 9行目をピンポイントで更新しますので、ロックは発生しなくなります。

で最後にお詫びしなければならないのは、私が予想していたデッドロックとは異なるものなので、バージョニングを使っても解決することはできません。良く質問を見ずに適当な推測をしてすみません。また、あなたが使っている READUNCOMMITTED (=NOLOCK) でも解決できません。なぜなら、更新目的のスキャンのときには共有ロックではなく更新ロックを必要とするためです。

解決方法は、アプリケーション側でサブクエリを即値に展開した SQL 文を組み立てるか、1行ずつ更新する、のいずれかになると思います。
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2006-09-22 18:40
おまけ。以下のクエリでロックされているデータを表示できると思います。

コード:

select * from Tes with (NOLOCK)
where Code not in (select Code from Tes with (READPAST))
ケイ
大ベテラン
会議室デビュー日: 2004/04/20
投稿数: 100
投稿日時: 2006-09-25 08:59
引用:

未記入さんの書き込み (2006-09-22 18:34) より:

PcA は 1行目のデータに更新ロックをかけてから、データの中身を確認します。データが Code=1 で条件と合致しているので更新をおこないます。その結果、1行目には排他ロックがかかったままになります。次に、PcA は 2行目のデータに更新ロックをかけてからデータの中身を確認します。データが Code=2 で条件と合致しているので更新をおこないます。その結果、2行目には排他ロックがかかったままになります。次に PcA は 3行目のデータに更新ロックをかけてからデータの中身を確認します。データは条件と合致していないので、3行目にかけた更新ロックをはずします。次に PcA は 4行目の･･･ とすべてのデータに更新ロックをかけて・はずしてスキャンをおこないます。

最終的に PcA は、1行目と2行目に排他ロックをかけた状態で、アイドル状態(未コミット)となります。

PcB も同様の手順を踏むことになるのですが･･･。PcB は 1行目のデータに更新ロックをかけて、データの中身を確認したいのですが、PcA が 1行目のデータに排他ロックをかけているため、更新ロックをかけることができずに待たされます。これが今回の問題の原因です。

なぜ、インデックスが使用されずにすべての行を検査してしまうのかという点は、前回説明した通りです。試しに PcA の where 句を Code in (1, 2)、PcB の where 句を Code in (8, 9) としてみてください。スキャンをする必要がなくなる(代わりにインデックスシークになるはずです)ので、PcA は 1行目と 2行目のみをピンポイントで更新、PcB は 8行目と 9行目をピンポイントで更新しますので、ロックは発生しなくなります。



なるほど。ありがとうございます。

・・・・となると、本当に更新方法を考えなおす必要があるのですね・・・・

上記の考えを元に、更新方法を考えなおしてアプリに問題ないか等を
考慮した上で他の方法を考えるか等検討してみます。

ありがとうございました。


1

スキルアップ／キャリアアップ（JOB@IT）