- PR -

【SQL Server】採番テーブルのロックを避けるには？

1
投稿者投稿内容
あき
ベテラン
会議室デビュー日: 2003/07/23
投稿数: 72
投稿日時: 2005-06-08 23:39
こんばんわ。
採番テーブルにデッドロックやブロックが頻繁に発生しています。
どうやって改善しようかと色々と考えているのですが、
次のようなアイディアが浮かびました。
１．採番テーブルをキー毎に別テーブルにする。
　　→別のデータページにすることで、ロックを軽減できるかも？
２．採番テーブルの主キーを非クラスタ化にして、ヒープテーブルにする。
　　→インデックスキーのロックを軽減できるかも？
３．採番テーブルをインメモリテーブルにする。
　　→メモリに固定することで、ロック保有時間が短くなると踏んでいます。
効果がありそうな方法について、ご意見お願いします。
また、その他テクニックや方向性などありましたらご意見よろしくお願いします。
[SQL Server 2000 SP3]
[Windows 2003 Server]
はにまる
ぬし
会議室デビュー日: 2003/12/19
投稿数: 969
お住まい・勤務地: 誤字脱字の国
投稿日時: 2005-06-09 01:31
情報処理試験曰く
システムでテーブルのロック順序を取り決める。
_________________
人生変わっちゃうかもよ?OFF会参加者募集中今考えるな、参加してから考えろ。
あき
ベテラン
会議室デビュー日: 2003/07/23
投稿数: 72
投稿日時: 2005-06-09 01:54
はにまるさん、ご意見ありがとうございます。
おっしゃるとおりです。
アクセスパスを見直すという、基本セオリーを忘れていました。
今回[Database Expert 会議室]に頼ったのは、ロック対策でデータベース機能にどれだけ有効な手段があるか、最近の動向を知りたかったからです。
また何かありましたら、よろしくお願いします。
TLC
大ベテラン
会議室デビュー日: 2005/05/31
投稿数: 152
お住まい・勤務地: 東京都
投稿日時: 2005-06-09 03:08
採番テーブルを分離しているということは，
複雑な番号付け基準にのっとったＩＤ付与を行っているのだと思います。

もし，
その番号付けロジックをＴ−ＳＱＬで記述できるようなのであれば，
INSERT 文（ストアドプロシージャ）に定義するロジックを見直して，
採番テーブによる新規番号自体を廃止するのも視野に入れてはどうでしょうか？

根本からの見直しになりますが。
あき
ベテラン
会議室デビュー日: 2003/07/23
投稿数: 72
投稿日時: 2005-06-09 09:41
ＴＬＣさん、ご意見ありがとうございます。
次の開発では、教訓として必ず実現しようと思います。

ちなみに、私のアイディアは次のような結果になりました。
１．採番テーブルをキー毎に別テーブルにする。
　　→未実施。テスト環境では、効果あるように見える。
２．採番テーブルの主キーを非クラスタ化にして、ヒープテーブルにする。
　　→前よりデッドロックが増える。
３．採番テーブルをインメモリテーブルにする。
　　→明らかにデッドロック、ブロックが減る。

インメモリテーブルとは、DBCC PINTABLE でバッファキャッシュに固定することです。
今回固定した採番テーブルの大きさは、50レコードで1ページでした。
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2005-06-09 12:49
引用:
システムでテーブルのロック順序を取り決める。

すべての処理が同じロック順序だとしてもデッドロックは発生しますよ(SQL Server の場合)。今回の場合、採番テーブルに正しくインデックスが張ってあればデッドロックは回避できるように思う。

それと、3. の速度向上策の実施によってブロッキング時間を短縮し結果としてデッドロックの発生確率を下げる、という方法は良くない。というか不完全。ブロッキング時間を短くするのは良いことだけど、それだけじゃなくクリティカルなタイミングでもデッドロックが発生しないようロジック面でも改善すべき。
はにまる
ぬし
会議室デビュー日: 2003/12/19
投稿数: 969
お住まい・勤務地: 誤字脱字の国
投稿日時: 2005-06-09 23:00
引用:

未記入さんの書き込み (2005-06-09 12:49) より:
引用:

システムでテーブルのロック順序を取り決める。

すべての処理が同じロック順序だとしてもデッドロックは発生しますよ(SQL Server の場合)。

なぬ！そうなんですか。
SQL Serverは詳しく無いのでその様な特性があるとは．．．
ご指摘ありがとうございます。
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2005-06-10 01:05
SQL Server や DB2 はバージョニングせず読み取りにもロックを使用するので変換デッドロックってのが発生する。たとえば次のクエリを複数のセッションで実行すると、(同じ処理なので)ロック順序が同じであるにも関わらずデッドロックが発生する(SQL Server)。

コード:
use Northwind
go

begin tran
select * from Products (REPEATABLEREAD) where ProductID = 3
waitfor delay '00:00:10'
update Products set ProductName = 'Tarako' where ProductID = 3
rollback tran
1

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