- PR -

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

投稿者投稿内容
こんどる
常連さん
会議室デビュー日: 2004/05/10
投稿数: 30
投稿日時: 2005-04-14 13:16
こんにちは、こんどると申します。

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();
}

}

nanbu
大ベテラン
会議室デビュー日: 2004/08/19
投稿数: 178
投稿日時: 2005-04-14 14:17
南部です。

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

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

未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2005-04-14 15:11
UPDLOCK(更新ロック) じゃなくて XLOCK(排他ロック) にしてみ。
こんどる
常連さん
会議室デビュー日: 2004/05/10
投稿数: 30
投稿日時: 2005-04-14 15:33
nunbuさん、未記入さん、早速の返信有難うございます。

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

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

今後とも宜しくお願いします。
こんどる
常連さん
会議室デビュー日: 2004/05/10
投稿数: 30
投稿日時: 2005-04-14 17:42

ところで、
このSQLの記述は
ORACLやProgreSQLなどでも使うことが出来るのでしょうか?
nanbu
大ベテラン
会議室デビュー日: 2004/08/19
投稿数: 178
投稿日時: 2005-04-14 18:41
引用:

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



えっと、これは、分離レベルに関しても同様と思いますが、、、
こんどる
常連さん
会議室デビュー日: 2004/05/10
投稿数: 30
投稿日時: 2005-04-16 12:33
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文のタイムアウト設定には関わらないようです)
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2005-04-16 13:14
引用:
トランザクションをコミットする前に、別トランザクションからでもSELECTできてしまうのです。(中略)これでは意味がないと思ってしまうのですが、排他ロックとはこのようなものなのでしょうか。


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

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


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

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

スキルアップ/キャリアアップ(JOB@IT)