QA@IT
この質問・回答は、@ITの旧掲示板からインポートされたものです。

SQL Server の排他ロックに関して(SELECT)

こんにちは、こんどると申します。

C# と SQL Server 2000 Enterprise Editionを用いて開発を行っております。

DBのレコードに対して
トランザクションをかけて取得したレコード達に対して
そのトランザクションがコミットされるまでの間

1.更新ロック
 …他のトランザクションからUPDATE, DELETEさせたくない
2.取得ロック
 …他のトランザクションからSELECTさせたくない
 (できれば同じトランザクション内での取得は可能にしたい)

の2つを制御したいのです。

いろいろ調べた結果
Transaction開始時のIsolationLevelをRepeatableReadに設定し
SQLで
SET LOCK_TIMEOUT 1000 SELECT * FROM table_name WITH(ROWLOCK,UPDLOCK)
のように取得することで、実現できるということでしたので
以下のように実装してみたのですが、更新ロックはできるものの、取得ロックは実現できませんでした。
どなたか上記のような制御を実現する方法を知ってらっしゃいましたら教えていただけませんでしょうか。

************ ソースコード *******************

string strSQL = "SET LOCK_TIMEOUT 1000 SELECT top 10 * FROM table_name WITH(ROWLOCK,UPDLOCK) ";

SqlDataReader reader = null;
SqlDataReader reader2 = null;
SqlConnection conn = new SqlConnection("※接続文字列");
SqlConnection conn2 = null;

try
{
    // Connectionオープン
    conn.Open();
    // Transaction開始
    SqlTransaction trans = conn.BeginTransaction(IsolationLevel.RepeatableRead, "TransKey1");

    // データセレクト
    SqlCommand command = new SqlCommand(strSQL, conn, trans);
    reader = command.ExecuteReader();
    command.Dispose();

    string strKey = string.Empty;
    while(reader.Read())
    {
        // データ取得用のキーを取得(とりあえず一番最後のレコードのキーが入るかな)
        strKey = reader["id"].ToString();
    }
    reader.Close();

    // 上記で取得したレコードの1レコードに対して別トランザクション&コネクションで取得
    string strSql2 = "SELECT * FROM table_name WHERE id='" + strKey + "'";
    conn2 = new SqlConnection("※接続文字列");
    conn2.Open();
    SqlTransaction trans2 = conn2.BeginTransaction(IsolationLevel.RepeatableRead, "TransKey2");

    SqlCommand command2 = new SqlCommand(strSql2, conn2, trans2);
    reader2 = command2.ExecuteReader(); // ※ここで取得できないようにしたい!!!!
    command2.Dispose();
    reader2.Close();
    trans2.Commit();

    // コミット
    trans.Commit();

}
catch(Exception ex)
{
    throw new Exception(ex.Message, ex);
}
finally
{
    if (reader != null && !reader.IsClosed)
    {
        reader.Close();
    }
    if (reader2 != null && !reader2.IsClosed)
    {
        reader2.Close();
    }
    if (conn.State == ConnectionState.Open)
    {
        conn.Close();
        conn.Dispose();
    }
    if (conn2 != null && conn2.State == ConnectionState.Open)
    {
        conn2.Close();
    conn2.Dispose();
    }

}

質問者:こんどる

回答

南部です。

strSql2にもstrSQLと同じロックヒントを与えればいいのでは?
そういうことではない?

更新ロックは共有ロックをブロックしない、つまり、
「ほかのユーザーがデータを読むことをブロックせずにデータを読み取ることができます。」
ということです。
(用語あってるか微妙)

投稿者:nanbu

編集 履歴 (0)

UPDLOCK(更新ロック) じゃなくて XLOCK(排他ロック) にしてみ。

投稿者:未記入

編集 履歴 (0)

nunbuさん、未記入さん、早速の返信有難うございます。

nunbuさんへ
DBにアクセスしてくるSQLが必ずしも同じロックヒントを持ってやってくるとは限りませんのでそれではちょっと解決にはならないかと思います。

結局、未記入さんの方法で動作させることができました。有難うございました。

今後とも宜しくお願いします。

投稿者:こんどる

編集 履歴 (0)

ところで、
このSQLの記述は
ORACLやProgreSQLなどでも使うことが出来るのでしょうか?

投稿者:こんどる

編集 履歴 (0)

DBにアクセスしてくるSQLが必ずしも同じロックヒントを持ってやってくるとは限りませんのでそれではちょっと解決にはならないかと思います。

えっと、これは、分離レベルに関しても同様と思いますが、、、

投稿者:nanbu

編集 履歴 (0)

nanbuさんへ

分離レベルというのは
IsolationLevel.RepeatableRead
のことですよね?

確かにそうなのですが、
分離レベルに関してはフレームワークで保証しているので大丈夫なのです。
ご説明不足で申し訳ありませんでした。

最初の問題に戻りますが、
ロックヒントをXLOCKにしたところ、
他のトランザクションからの通常のSELECT文はロックしたのですが
最初にロックしたSQLと同じ様に、ロックヒントを指定してSELECTすると、
トランザクションをコミットする前に、別トランザクションからでも
SELECTできてしまうのです。

最初のソースの
// 上記で取得したレコードの1レコードに対して別トランザクション&コネクションで取得
string strSql2 = "SELECT * FROM table_name WHERE id='" + strKey + "'";
の部分を
string strSql2 = "SET LOCK_TIMEOUT 1000 SELECT * FROM table_name WITH(XLOCK) WHERE id='" + strKey + "'";
に変えたイメージです。

これでは意味がないと思ってしまうのですが、排他ロックとは
このようなものなのでしょうか。
解決策があれば教えて頂きたいのですが。
よろしくお願いします。

※ちなみに、2つ目のSELECT文でタイムアウト時間を1000に設定していますが
この場合、別トランザクションから取得するときに、1秒待ってから取得するようです。
0msに設定すると、待機することなく取得するようです。
(これは1つ目のSELECT文のタイムアウト設定には関わらないようです)

投稿者:こんどる

編集 履歴 (0)

トランザクションをコミットする前に、別トランザクションからでもSELECTできてしまうのです。(中略)これでは意味がないと思ってしまうのですが、排他ロックとはこのようなものなのでしょうか。

排他ロックは そのような動作はしない。(読み取りトランザクションが NOLOCK を指定している場合は別だけど…) なにか(動作確認のときに)勘違いしているんじゃないの?

SET LOCK_TIMEOUT に関する認識も完全に間違っているし、もっと基礎から勉強しなおしたほうがいい。
タイムアウト時間を1000に設定していますが この場合、別トランザクションから取得するときに、1秒待ってから取得するようです

「ようです」じゃなくて、ちゃんと SET LOCK_TIMEOUT について BooksOnline で調べたらどう? 取得というのはどうやって確認したの? ロックタイムアウトになって取得せずに制御が戻っただけじゃないの?

前提となるあなたの基礎知識が足りなすぎるようなので、悪いけど、これ以上は説明する気にもなれない。

投稿者:未記入

編集 履歴 (0)

分離レベルに関してはフレームワークで保証しているので大丈夫なのです。

with (READUNCOMMITTED) や with (NOLOCK)が指定されても?

最初の問題に戻りますが、

ロックヒントをXLOCKにしたところ、

他のトランザクションからの通常のSELECT文はロックしたのですが

最初にロックしたSQLと同じ様に、ロックヒントを指定してSELECTすると、

トランザクションをコミットする前に、別トランザクションからでも

SELECTできてしまうのです。

ありえません。

ロックヒントを指定したときとそうでないときで、
SQLServerの処理が違うの当然です。
でも、タイミングが違うだけで結果は同じです。

reader2 = command2.ExecuteReader(); // ※ここで取得できないようにしたい!!!!
reader2.Read(); //これを追加

で確認してみて下さい。

BooksOnlineはもちろん、
.NETエンタープライズ Webアプリケーション開発技術大全 Vol.5
トランザクション設計編に分かりやすく解説されています。
(分かりやすくは主観です)

投稿者:nanbu

編集 履歴 (0)

nanbuさんへ

with (READUNCOMMITTED) や with (NOLOCK)が指定されても?
詳細な説明は省きますが、SQLに関してはF/Wで様々な制御がされており、このような指定はできないように制御しているので大丈夫です。
いろいろ有難うございました。
本題のほうですが、結論として以下のような勘違いでした。申し訳ないです。

未記入さんへ

「ようです」じゃなくて、ちゃんと SET LOCK_TIMEOUT について BooksOnline で調べたらどう?
「ようです」というのは別にSET LOCK_TIMEOUT自体に関してではなく、動作に関して述べただけです。SET LOCK_TIMEOUTに関して調べてないわけでも認識不足なわけでもありません。ただちょっと混乱してよくわからない書き方をしてしまいました。

取得というのはどうやって確認したの? ロックタイムアウトになって取得せずに制御が戻っただけじゃないの?
その通りでした。
取得確認方法ですが、LOCKOUTを指定しない場合にSqlCommandのExceptionをキャッチしていたのですが、
LOCKOUTを指定した場合にExceptionが発生しないので、取得できてしまっていると勘違いし、混乱してしまっていました。

前提となるあなたの基礎知識が足りなすぎるようなので、悪いけど、これ以上は説明する気にもなれない。
あまりこういうことを言いたくはないですが、特にあなたが説明する気になれるかどうかを書いていただく必要はないです。

投稿者:こんどる

編集 履歴 (0)

SET LOCK_TIMEOUTに関して調べてないわけでも認識不足なわけでもありません。

認識不足でしょう。ちゃんとロックタイムアウトについて理解しているなら↓こんな発言は出てこない。

タイムアウト時間を1000に設定していますがこの場合、別トランザクションから取得するときに、1秒待ってから取得するようです。

それと、認識が足りない・おかしいのはロックタイムアウトに関する点だけではない。排他ロックについても十分に理解できていないから、適切な確認方法が取れずに変な勘違いをするんだよ。普通は機能を知っていて結果でそれを確認するものだけど、あなたの行動は「結果を見て機能を推測している」ように思える。

あまりこういうことを言いたくはないですが、特にあなたが説明する気になれるかどうかを書いていただく必要はないです。

言いたくないなら言わなければいいのに。結局のところ「言い返さずにはいられない」んでしょ。本当に恥ずかしい人だなあ。

投稿者:未記入

編集 履歴 (0)

認識不足でしょう。ちゃんとロックタイムアウトについて理解しているなら↓こんな発言は出てこない。

だから、認識はあっていたのですが、取得していると勘違いしていたために混乱して、自分の認識が間違っていたのかなと思ったんですよ。
別に言い訳するほどのことではないですが、言い方が腹立ったので言い返してみました。

それと、認識が足りない・おかしいのはロックタイムアウトに関する点だけではない。排他ロックについても十分に理解できていないから、適切な確認方法が取れずに変な勘違いをするんだよ。
これについては、別に認識が正しいなんて言った覚えはないです。
いろんなサイトを見ていろんな記述があって良く分からなくなったので、質問したまでです。

言いたくないなら言わなければいいのに。結局のところ「言い返さずにはいられない」んでしょ。本当に恥ずかしい人だなあ。
本来なら言いたくないけど、言われたから言い返したまでです。
顔が見えないからって挑戦的な書き方をするあなたの方が恥ずかしいと思いますが?
まあこんなことを書いてしまった私も同レベルですが。

と、こんなことを言い合う場ではないので、これで止めておきます。

結果的に解決することができました。
お二人ともありがとうございました。

投稿者:こんどる

編集 履歴 (0)

こんどるさんの書き込み (2005-04-17 21:16) より:

 もう見てないかもしれないけど。
 言葉の是非はともかく、未記入氏の指摘事項は、真摯に受け止めた方がいいよ。nanbuさんとのやりとりにしても、伝えるべきことを伝えず、誤解させているわけだし。

 自分を正しく伝えることができない、ということは、いろいろなところで損をします。たとえば、プレゼンテーション。「これくらいはわかってくれるだろう」と思って省いたことで、失敗することもあり得ます。

 今回のことは、あなたがわかっているつもりで、わかっていなかったことが原因なんだし。

投稿者:Jitta

編集 履歴 (0)
ウォッチ

この質問への回答やコメントをメールでお知らせします。