

SQLserverでレコードをinsertしたいのですが、その際の主キーをどうやって設定するか悩んでいます。  
今までmax(主キー)+1でやっていたのですが、この方法だと複数プロセス同時にinsertがかかったときに  
同じ主キーを登録しようとしてしまうことがあります。

少し調べたのですが、TransactionScopeというのがありそれで排他処理できるようです。  
しかし試してみたところ  
「ハンドルされていない例外: System.Data.SqlClient.SqlException:  
トランザクション (プロセス ID 65) が、ロック 個のリソースで他のプロセスとデッドロックして、  
このトランザクションがそのデッドロックの対象となりました。トランザクションを再実行してください。」  
という例外が起きてしまい上手くいきません。

以下試したコード（C#)です。同じプログラムをほぼ同時に起動したところ上記の例外が発生しました。

[code]

    class Program
    {
        static void Main(string[] args)
        {
            DwhDBAccess.DwhTableAdapters.workSampleTableAdapter adapter = new DwhDBAccess.DwhTableAdapters.workSampleTableAdapter();
            TransactionOptions o = new TransactionOptions { IsolationLevel = IsolationLevel.Serializable };
            using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, o))
            {
                int max = 0;
                try
                {
                    max = adapter.GetData().Max(x => x.id);
                }
                catch (Exception e)
                {
                    max = 0;
                }
                System.Threading.Thread.Sleep(10000);
                adapter.InsertQuery(max + 1, "hello world.");
                Console.WriteLine("Insert id=" + (max + 1).ToString());
                ts.Complete();
            }
        }
    }
    
[/code]

排他制御を上手く働かせて同じidを登録しないようにするにはどうすればいいでしょうか。  
よろしくお願いします。


CC BY-SA 3.0
2017-04-26T10:23:58.877
34293
2017-08-04T06:24:51.710
18637
question
sql sql-server
SQL serverでレコードをinsertする際の主キーをどう設定するか


問題の例外はデッドロックを検知して正しく発生しています。  
ですので必要なのは、この例外が発生した場合に処理をリトライするような実装になります。

まず件のエラーの`SqlException.Number`は`1205`ですので、以下のような条件でキャッチを行います。

[code]

    try
    {
        // トランザクション処理
    }
    catch (SqlException ex)
    {
        if (ex.Number == 1205)
        {
            // リトライ処理
        }
        else
        {
            throw;
        }
    }
    
[/code]

リトライ方法は要件によると思いますが、もっとも単純な方法としては`for`ループによるものがあります。

[code]

    // リトライ回数
    const int RETRY = 5;
    for (var i = 0; i < RETRY; i++)
    {
        try
        {
            // トランザクション処理
    
            break;
        }
        catch (SqlException ex)
        {
            if (ex.Number == 1205 && i < RETRY - 1)
            {
                continue;
            }
            throw;
        }
    }
    
[/code]

なお質問の内容とは関係ありませんが、下記の個所では全件`SELECT`が発生しているので`SELECT COALESCE(MAX(id), 0) FROM
...`のようなSQLに書き直すべきです。

>
[code]

>     int max = 0;
>     try
>     {
>         max = adapter.GetData().Max(x => x.id);
>     }
>     catch (Exception e)
>     {
>         max = 0;
>     }
>  
[/code]


CC BY-SA 3.0
2017-04-26T11:26:19.977
34295
2017-04-26T12:25:23.343
2017-04-26T12:25:23.343
5750
5750
34293
answer


`IsolationLevel.Serializable`の概念を説明します。

>
[code]

>     max = adapter.GetData().Max(x => x.id);
>  
[/code]

にて最大値を得るわけですが、その際、当然ながら全レコードを参照します。トランザクションでは参照した全レコードに対して共有ロックを取得します。その後、

>
[code]

>     adapter.InsertQuery(max + 1, "hello world.");
>  
[/code]

にて排他ロックを取得してから`INSERT`が実行されます。

さて、共有ロックと排他ロックは次の性質があります。

  * 別セッションで共有ロック中に新たに共有ロック **可能**
  * 別セッションで共有ロック中に新たに排他ロック **不可** 、解除待ちする
  * 別セッションで排他ロック中に新たに共有ロック **不可** 、解除待ちする
  * 別セッションで排他ロック中に新たに排他ロック **不可** 、解除待ちする

この通りロックは解除待ちを行うだけの存在で、それ自体がデッドロックを引き起こすものではありません。しかし、このロックの性質を踏まえて質問のコードの動作を検証すると次の状態が存在することがわかります。

複数のセッションが`max = adapter.GetData().Max(x =>
x.id)`を実行し、それぞれが共有ロックを保持し、`Thread.Sleep(10000)`後にそれぞれが`adapter.InsertQuery(max
+ 1, "hello world.")`にて排他ロックを獲得しようとすると、どちらのセッションも共有ロックの解除待ちを行います。これが
**デッドロック** です。

* * *

以上のデッドロック発生のメカニズムを踏まえた上で、対策を考えます。

### 整合性レベルを下げる

整合性レベルを下げて`IsolationLevel.ReadCommitted`を使用すると共有ロックを保持し続けなくなります。これにより排他ロックを獲得できるようになるためデッドロックは発生しなくなり、本来の主キー重複エラーが発生可能になります。重複エラーが発生したらリトライしてください。

### 参照（`SELECT`）と挿入（`INSERT`）を同時に行う

`TableAdapter`の機能を使わず、直接SQL文を記述してください。

### テーブル設計を変更し、自動的に採番する

[`IDENTITY`プロパティ](https://msdn.microsoft.com/ja-
jp/library/ms186775\(v=sql.110\).aspx)を使用します。yukihaneさんが提案されているシーケンス番号とは別物です。


CC BY-SA 3.0
2017-04-26T13:52:56.580
34296
2017-04-26T13:52:56.580
4236
34293
answer


特に不都合が無いようなら、PK 列に Identity 属性を付与するのが軽量でお勧めです。


CC BY-SA 3.0
2017-04-26T17:53:53.493
34302
2017-04-26T17:53:53.493
3033
34293
answer