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

SQLServerの排他処理

VB.NETとSQLServerを使用してWINアプリの開発をしております。

複数クライアントがあるために、DB更新時に排他ロックをかけなければならないのですが、
テスト用に2つのプロジェクトに、以下のコードを持つフォームをつくり、
排他ロックテストを行ったところ、どちらのフォームでも、データを掴めてしまいます。
データロックの範囲はテーブル単位で、片方のフォームでボタン1をクリックして
データをロックして、もう一方のフォームでボタン1をクリックしたときに
"ロックされています。"というメッセージを表示されたかったのです。
皆さんはどのようにして排他を実現していますでしょうか?

sqlTrn = sqlCon.BeginTransaction(IsolationLevel.RepeatableRead)
sqlCom.Transaction = sqlTrn
Try
sqlCom.Connection = sqlCon
sqlCom.CommandText = "select * from tblTEST for update nowait"
Label1.Text = "ロックしています。"
Catch ex As Exception
If ex.ToString = "54" Then
Label1.Text = "ロックされています。"
clsSql.SqlClose(sqlCon)
End If
End Try

*一部修正しました。
[ メッセージ編集済み 編集者: カウンター 編集日時 2004-04-07 11:08 ]

質問者:カウンター

回答

自己レスです。

以下のようにすることで、一応排他はかかりました。

'トランザクション開始
sqlTrn = sqlCon.BeginTransaction(IsolationLevel.Serializable)
sqlCom.Transaction = sqlTrn
Try
sqlCom.Connection = sqlCon
sqlCom.CommandText = "select * from tblTEST WITH(TABLOCK, UPDLOCK)"←変更
Label1.Text = "ロックしています。"
sqlreader = sqlCom.ExecuteReader
Catch ex As Exception
If ex.ToString = "54" Then
Label1.Text = "ロックされています。"
clsSql.SqlClose(sqlCon)
End If
End Try

ただ、ロック状態の確認が出来ないので、それについて引き続き調査中です。

投稿者:カウンター

編集 履歴 (0)

C# & コンソールプログラムですが、以下のコードで行ロックを検出できます。
ビルドして複数のコマンドプロンプトで実行してみて下さい。



using System;
using System.Data.SqlClient;

public class test
{
    public static void Main(string[] args)
    {
        Console.WriteLine("Lock Start: {0}", DateTime.Now.ToString(@"yyyy/MM/dd HH:mm:ss.fffff"));
        try
        {
            using(SqlConnection con = new SqlConnection("server=localhost;database=WORK;uid=sa;password="))
            {
                con.Open();
                SqlTransaction tran = con.BeginTransaction();
                try
                {
                    string sql = "SET LOCK_TIMEOUT 0 \n";
                    sql += @"SELECT * FROM KBN_TBL WITH (ROWLOCK, UPDLOCK) WHERE KBN_KEY = 1";
                    SqlCommand cmd = new SqlCommand(sql, con, tran);
                    for(int cnt = 0; cnt < 10; cnt++)
                    {
                        try
                        {
                            cmd.ExecuteScalar();
                            Console.WriteLine("Not Locked");
                            System.Threading.Thread.Sleep(3000);
                            break;
                        }
                        catch(SqlException ex)
                        {
                            System.Threading.Thread.Sleep(500);
                            Console.WriteLine("[{0}] {1}", ex.Number, ex.Message);
                        }
                    }
                    tran.Commit();
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
            Console.WriteLine("Lock End: {0}", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss.fffff"));
        }
        catch(Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
    }
}

投稿者:ぼると

編集 履歴 (0)

またまた自己レスです。

何とか、エラー状態を取得するために、いろいろ調べていたのですが、
データがロックされた状態で発生するエラーメッセージが

「"タイムアウトに達しました。操作が完了する前にタイムアウト期間が過ぎたか、またはサーバーが応答していません。"」

でした。ひょっとして、ロックがかかってないのでしょうか?
ただ、タイムアウトする前にロックされているデータを開放すると、
正常にデータを掴むことが出来るので、やっぱりロックはかかってると思うのですが

ロック状態を表す値をご存知の方がいましたら、ご教授よろしくお願いいたします。

投稿者:カウンター

編集 履歴 (0)

ぼるとさんありがとうございます。

試してみたのですが、SET LOCK_TIMEOUT 0 とするとロックされなくなってしまいました。

投稿者:カウンター

編集 履歴 (0)

三度、自己レスです。
一応、ロックの検出は出来ましたが、テーブルロックのみで行ロックでは出来ません。

①sqlCom.CommandText = "SET LOCK_TIMEOUT 0 select * from tblTEST WITH(ROWLOCK, UPDLOCK) "
上記の場合には、排他制御がかからない。また、SET LOCK_TIMEOUT 0をはずすと
「タイムアウトに達しました。操作が完了する前にタイムアウト期間が過ぎたか、またはサーバーが応答していません。」
のエラーが返ってくる。

②sqlCom.CommandText = "SET LOCK_TIMEOUT 0 select * from tblTEST WITH(TABLOCK, UPDLOCK) "
上記の場合には、排他制御が係り、「ロック要求がタイムアウトしました。」のエラーが返ってくる。
また、この方法でロックした場合に、他でselect * from tblTESTを実行しても排他されてしまう。

http://www.microsoft.com/japan/msdn/library/default.asp?url=/japan/msdn/library/ja/acdata/ac_8_con_7a_1hf7.asp

上記のサイトによると

「テーブルの読み出しの間、共有ロックの代わりに更新ロックを使用し、ステートメント終了またはトランザクション終了まで保持します。UPDLOCK には、ほかのユーザーがデータを読むことをブロックせずにデータを読み取ることができます。また、読み取り以来データは変更されていないという保証があるため、後にそのデータを更新できるという利点があります。」

とされているが、実際にはデータを読むことが出来ない。

以上が、ここまででわかった点です。

SQLServerでは、行レベルの排他ロックまたは、共有ロックは出来ないのでしょうか?

投稿者:カウンター

編集 履歴 (0)

書き方が悪かったみたいです。
上記のコードをビルドして、
①実行する
②①が実行中にもう一度実行(2重起動)する
と②で行ロックが検出できるという意味です。

.NET Framework 1.1 + SQL Server 2000SP3で動作確認しました。

投稿者:ぼると

編集 履歴 (0)

出来ました。結局、ぼるとさんが教えてくれた
sqlCom.CommandText = "SET LOCK_TIMEOUT 0 select * from tblTEST WITH(ROWLOCK, UPDLOCK) where NO = 1"
で行ロックがかかってました。

ロックした後に、もう一度 SET LOCK_TIMEOUT 0 select * from tblTEST where NO = 1
を発行したところでロックがかかるわけありませんでした。

DELETE文を発行したらちゃんとロックがかかりました。

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

投稿者:カウンター

編集 履歴 (0)
ウォッチ

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