【訂正】
以下の文章が間違っていたことがわかったので、ここで訂正してお詫びします。
SQLServer2005のSERIALIZABLEレベルでは、クエリによる範囲ロックを行うことが
問題の真のメカニズムでした。
以下詳細。
SQLServer 7.0ではSERIALIZABLEレベルでロックを取得するとテーブルロックがかかっていました。
しかし、これでは同時実行性の悪化を招きます。
そのため新しいバージョンのSQLSERVERでは、クエリ範囲に該当する範囲ロックがかけられます。
つまり、
SELECT * FROM EMP WHERE ENAME LIKE '山本%'
とすれば、「山本%」に該当するデータを操作することは行えず、挿入も行えません。
bit列のカーディナリティーが低いために、全テーブルロックのように見えたのは、
間接的に正しいですが、直接の原因ではなかったようです。
また、SQLSERVERにビットマップインデックスはありません。
大嘘を言いました。ここにお詫びします。
SqlServer2005を使っていて、行ロックを取ろうとしたところ
テーブルロックになってしまう現象が発生し原因を追究していったところ
データベースエンジンの奥深い仕組みにぶつかったので説明する。
SqlServerで行ロックを取得する場合、以下のようにクエリを記述する。
『クエリ１』
SELECT＊FROM EMP WITH(UPDLOCK,ROWLOCK) WHERE EMPNO = 1
ただし、WITH()ロックヒントを使った
クエリで必ずしも行ロックされるとは限らない。
今日、以下のようなクエリでテーブルロックがかかってしまった。
『クエリ２』
SELECT＊FROM EMP WITH(UPDLOCK,ROWLOCK) WHERE ISDELETE = True
1番目のクエリと何が違うのか？
この謎を解く鍵は、WHERE句で使われているカラムの型とインデックスの使い方にある。
ISDELETEカラムは、bit型の列である。
通常データベースのインデックスは、Bツリー方式で管理される。
Bツリーを設定しておけば二分探索という方法を使った高速な検索が可能となる。
二分探索では、あらかじめソートした配列の中央にあるエントリの値をキーの値と比較する。
そして、そのキーが配列の前半分にあるか、後ろ半分にあるかを調べる。
これを繰り返すことによって検索領域を1/2ずつに狭めていきキーの位置を特定する。
という方法だ。
しかし、この方法はデータの重複が少ないデータ配列に対しては有効だが
データの重複度が高い配列には効果がない。
bit列などは、True、Falseの２種類しかデータの種類がないため
ほとんどのデータが重複すると言える。
（データの多くが重複することを「カーディナリティーが低い」という。）
bit列は極端にカーディナリティーの低い列だ。
そのため、このクエリで使われるインデックスの範囲は
TrueのレコードとFalseのレコードであり、結果として全行が該当する。
SQLSEVERでは、カーディナリティーの低い列に対するクエリで
行ロックをかけた場合、インデックスのほとんどロックされてしまい
結果として行ロックではなくなってしまうようだ。
Oracleなどでは、
bit列のようにカーディナリティーが低い列には、次の「ビットマップインデックス」が使われる。
ビットマップインデックスとは、キー値データからビットデータを作成し、インデックスにビットデータを格納する
ビット演算を使ったデータ整合による選択を行うため、データの種類が少ない（カーディナリティが低い）列をキーにした検索に有効である。
ただしビットマップインデックスでは、キー値データの更新時に都度、
全てのキー値データ（つまり全レコード）からビットデータを作成する。
（SQLSERVERではビットマップインデックスは使われないようだ。）
クエリの実行時にはRDBMSのオプティマイザという機能が、
クエリを解析し、そのクエリに最適なインデックスを利用して検索を実施する。
上記の「クエリ１」と「クエリ２」では、ロックされるインデックスの範囲が異なるために
クエリ１では行ロックが取得でき、クエリ２ではテーブルロックとなってしまった。
このほかにも、WITH(UPDLOCK)だけでは、行ロックと呼べない現象がまだある。
行のデータを保存するエクステントというデータの保存単位に原因があるのだが今回は割愛する。
