none
SQLServer2005での一つのSQL文の同時実行 RRS feed

  • 質問

  •  みなさん、こんにちは。

     VWDでWebアプリケーションを作成しています。

     SQLServer2005で、トランザクションを行わずに
    Insert文を同時に実行した場合どうなるのでしょうか?

     ヘルプ等色々調べたのですが該当する記述を見つけられないでいます。

     一つのSQL文(Insert)だけしか実行しないので、
    トランザクションはなるべく使用したくないです。

     衝突した場合、SqlCommandオブジェクトの
    CommandTimeoutプロパティで指定した時間の間、
    書き込みを試行するのでしょうか?

     どなたか、ご教授いただければ幸いです。

    補足---------
    SqlConnectionとSqlCommandを使用しSqlCommandのExecuteNonQueryで
    SQL文を実行しています。

    2006年8月11日 4:07

すべての返信

  •  ken777 さんからの引用
    SQLServer2005で、トランザクションを行わずに
    Insert文を同時に実行した場合どうなるのでしょうか?

    厳密に同時なんてのはないです。

    2006年8月11日 5:08
  •  ken777 さんからの引用

     一つのSQL文(Insert)だけしか実行しないので、
    トランザクションはなるべく使用したくないです。

    明示的なトランザクションを使用したくないということですよね?
    SQL ServerはInsert文などの更新系のSQL文は、その一行のみでトランザクションとして扱います。また、既定の動作が自動コミットなので、Insert文が正常に終了すれば、自動でコミットされます。

     ken777 さんからの引用

     衝突した場合、SqlCommandオブジェクトの
    CommandTimeoutプロパティで指定した時間の間、
    書き込みを試行するのでしょうか?

    Insert文が実行されている間、排他ロックがかかります。既定では、他のトランザクションは排他が解除されるまで実行をじっと待っています。もちろん、タイムアウトの時間を設定することもできます。

    CommandTimeoutプロパティで設定するタイムアウトは、アプリケーションでSQL Serverから応答が帰って来るのを待つ最大の時間です。この時間が過ぎると、アプリケーションで、もう待てません。限界です。といってタイムアウトが発生します。SQL serverで排他ロックが解除されるのを待っている時間ではないので注意して下さい。ちなみに、CommandTimeoutプロパティのデフォルトは30秒です。
    なので処理時間が30秒以上かかるストアドプロシージャなどは、この時間を延ばしておかないとタイムアウトしてしまいます。

    さて、じゃんぬさんが言われるように、全く同時に更新処理がSQL Serverに行ったとしても、完全に同時には処理できませんから、どちらかを先に処理することになるでしょう。

    2006年8月11日 6:04
  • 顛末は同じだけど、内容の濃さは大違いだなぁ...
    trapemiya さん、補足ありがとうございます。

    2006年8月11日 8:15
  • それよりもまずどういうテーブルにどういうINSERTを投げる気なのか?がわからないことには。

    たとえばログであれば同じINSERT文を山ほど出力することになりますよね?

    PKがかぶらない限り何も問題はないでしょう?

    2006年8月11日 13:32
  •  trapemiyaさん、中博俊さん、じゃんぬねっとさん、
    ご教授いただきありがとうございます。

     じゃんぬねっと さんからの引用

    厳密に同時なんてのはないです。

     大変勉強になります。

     それから、trapemiyaさんの書き込みが易しく説明していただいたので
    非常に勉強になりました。ありがとうございます。

    ロックがかかっている場合、ロックが解除されるのを
    ある程度の時間じっと待ってていてくれるということで、
    明示的なトランザクションを使用せずにプログラムを組
    みたいと思います。

     中博俊 さんからの引用

    それよりもまずどういうテーブルにどういうINSERTを投げる気なのか?がわからないことには。

    たとえばログであれば同じINSERT文を山ほど出力することになりますよね?

     エラーを書き込むクラスを一つ作り、そのクラスで
    Webアプリケーションで発生しCatchしたエラーを全て
    テーブルに書き込もうと思いました。

     質問の仕方が下手で皆様に迷惑をかけてしまいました。
    申し訳ありませんでした。
    これからは、質問の仕方を少し考えたいと思います。

     これで疑問が解けてすっきりしました。散々、ネットやヘルプで
    調べたのですが、目的の情報を得ることができませんでした。
     皆さんのおかげで助かりました。

     trapemiyaさん、中博俊さん、じゃんぬねっとさん、皆さん、
    ありがとうございました。

    2006年8月12日 8:56
  • なんとなくですが,
    質問の背景が,「レコードを順番に追加していきたい」というのが
    あるような気がするので,念のため。

     ken777 さんからの引用
    ロックがかかっている場合、ロックが解除されるのを
    ある程度の時間じっと待ってていてくれるということで、

    単純に一行追加するようなINSERT文の場合は,
    排他ロックがかかるのは,KEY に対して排他ロックがかかるので,
    主キー値が異なる行の追加の場合は,その排他ロックの影響を受けません。

    なので,先にINSERTされた行が,コミットされていない状態でも,
    主キー値さえ異なれば,次の行を追加していけます。
    (簡単に書くと,新規行追加に対しては排他できないので,
    テーブルごと排他しないと追加を防げないということです。)

    IDENTITY(指定された)カラムの場合は,
    そのコミットされていない行の次の値で追加されます。
    その行がコミットされずロールバックされれば,
    そのまま,連番に抜けが発生する形になります。
    コミットされてから連番が発行されるわけではないです。

    KEY としての分離レベルという表現があるとすれば,
    常に READ UNCOMMITTED ということのようです。

    なので,それらカラクリが自分が想定していたのと違う場合は,
    なんらかのシカケが必要です。

     

    追記:
    トランザクション というのは,
    業務処理をある範囲でくくることによって一単位を作成して,
    all or nothing を実現するための機能です。
    ロックや分離レベルとは,別の視点というか概念です。

    2006年8月13日 15:58
  • 稍丼さん、みなさん、こんにちは。

     稍丼さん、ご教授ありがとうございます。

     稍丼 さんからの引用

    なので,先にINSERTされた行が,コミットされていない状態でも,
    主キー値さえ異なれば,次の行を追加していけます。
    (簡単に書くと,新規行追加に対しては排他できないので,
    テーブルごと排他しないと追加を防げないということです。)

    IDENTITY(指定された)カラムの場合は,
    そのコミットされていない行の次の値で追加されます。
    その行がコミットされずロールバックされれば,
    そのまま,連番に抜けが発生する形になります。
    コミットされてから連番が発行されるわけではないです。

    KEY としての分離レベルという表現があるとすれば,
    常に READ UNCOMMITTED ということのようです。

     難しいですが、大変勉強になります。使用するテーブルには主キーは設定していませんが

    Identytyは設定しています。また、エラーが発生したときの日付を保存していますので

    順番どおりに挿入できなくても問題ないと思っています。(欠番ができても問題ないと思っています)

    しかし、行を追加している最中に、他の新しい行を追加しようとしてエラーが

    発生するのは困ると思っています。この場合(主キーなし)、そもそもエラーなど発生せずに

    ロック(共有ロックや排他ロック)もかからず、行を追加している最中に

    新しい行を挿入しようとしても問題ないようにSQLサーバは作られているという認識で

    よろしいでしょうか?

    #自分にとって難しい内容であるため確認をとることをお許しください。

     

     稍丼 さんからの引用

    トランザクション というのは,
    業務処理をある範囲でくくることによって一単位を作成して,
    all or nothing を実現するための機能です。
    ロックや分離レベルとは,別の視点というか概念です。

    わかってはいたのですが、昔勉強したないようだったので

    自分の中で曖昧になっていまいました。(^^)

     これを読んでしっかり頭の中を整理できたと思います。

    2006年8月15日 10:23
  • もちろん単純なINSERTが失敗することはないでしょう。

    #その他の要因ではありえますが・・・

    2006年8月15日 15:51
  •  ken777 さんからの引用
    この場合(主キーなし)、そもそもエラーなど発生せずに
    ロック(共有ロックや排他ロック)もかからず、行を追加している最中に
    新しい行を挿入しようとしても問題ないようにSQLサーバは作られている
    という認識でよろしいでしょうか?

    まず,最初の確認ですが,
    接続(user connection)を開いた後は,
    プロセス内プロセス(or サブタスク)として,
    それぞれ独自にSQL文が実行されます。
    なので,SQL 文は,実際に,同時に複数実行可能です。

    共有ロックや排他ロックや更新ロックなどのいわゆる実際のロックを,
    各接続が行う場合に,
    それらロックが実際には同時には起きないのは,
    SQL Server は自分の
    リソースにアクセスする場合,
    ラッチ(latch)と呼ばれている軽いロックをかけながら移動していくので,
    いろんなリソースがあるけれど,
    それぞれ各リソースに複数の接続が同時にアクセスしないようなシカケが作ってあります。
    (詳しくはよくはわかりません)

    インデックスがなくても,
    ヒープ上のデータページには,RID( row indentifier )
    (ファイル番号, ページ番号, そのページ内での論理的な行番号(スロット番号))
    というものが存在するので,追加時は,RID にロックがかかります。

    (そのRIDそのものにロックがかかるのでなく,
    そのRIDを代理する lock resource block という16バイトの構造を中心にした
    lock block というものが動的に作られて,
    その lock block に対して,lock owner block というものを動的に作成することで
    そこでロックをかける仕組みになっています。
    動的といっても,オブジェクト自体は,設定もよりますが,
    あらかじめ数千個単位で用意されています。
    なんだかよくわからない程,ややこしくなっているのは,
    同一トランザクション内からは,ブロックされないけれど,
    別のトランザクションに対してブロックするように,
    つまり,ロックが有効になっているようにみせるシカケのためです)

     


    他では,単純追加時に,RID に排他がかかるのは,
    主キーが non-clustered index のテーブルの場合があります。

    なので,単純追加時に排他ロックが,KEY にかかるのは,
    つまり,インデックス(のキー)に排他ロックがかかるのは,
    テーブルに clustered index が存在している時ということになります。
    (多くの場合は,これになります)

     

    英語がOKなら,
    Inside Microsoft SQL Server 2005: The Storage Engine (Pro-Developer)
    あたりで,死ぬほど詳しくわかります。
    ただし,2000 では,ラッチ についてはあまり詳しくは触れてありませんでした。
    実際のロックに関しては,
    sp_lock の使い方やその結果の読み取り方の説明とかのあたりが
    自力で研究するのに手助けになります。

    2006年8月15日 17:38
  • 簡単に図示すると...
     データページのある行
    

    とあったとすると,実際は,

     データページのある行 + lock block
    

    という感じで行がある感じになります。
    もちろん,lock block は,別の場所にあるので,ある行の先頭に存在しているとかではありません。
    「ロック・リソースを一意に表すもの」となります。
    この場合は,行なので,誤解を恐れずに書けば代理キーのようなものです。

     データページのある行 + lock block
                 (一意に表すので代理キー(surrogate key)のようなもの)
    

    で,ロックがかかった状態が

     データページのある行 + lock block ---> lock owner block (共有とか排他などの情報) -->transaction
    

    共有ロックだった場合は,同時に存在できるので,

     データページのある行 + lock block ---> lock owner block (S) -->transaction
                                          lock owner block (S) -->transaction
                                          lock owner block (S) -->transaction
    

    のような lock owner block がリストになっている感じで,
    それらが別々のトランザクションに参加していてもロックをかけれます。

    でも,次にやってくるのが排他ロックだった場合は,

     データページのある行 + lock block ---> lock owner block (S) -->transaction
                                 :
                                 +---> lock waiter block (X) -->transaction
    
    
    のように待ち行列のリストに並びます。どんどんやってくれば,
     データページのある行 + lock block ---> lock owner block (S) -->transaction
                                 :
                                 +---> lock waiter block (X) -->transaction
                                       lock waiter block (X) -->transaction
    

    のように waiter リストに並びます。

    この時,次にやってくるのが共有(S) でも,waiterリストに待っているものがあれば,並んで待ちます。

     データページのある行 + lock block ---> lock owner block (S)
                                 :
                                 +---> lock waiter block (X)
                                       lock waiter block (X)
                                       lock waiter block (S) <-- ここ
    

    最初の共有ロックが解除されると,次の waiterリストからやってくるので,

     データページのある行 + lock block ---> lock owner block (X)
                                 :
                                 +---> lock waiter block (X)
                                       lock waiter block (S)
    

    で,次々に進むと

     データページのある行 + lock block ---> lock owner block (X)
                                 :
                                 +---> lock waiter block (S)
    
     データページのある行 + lock block ---> lock owner block (S)
                                 :
                                 +---> 
    

    のようになっていくわけです。

    また,lock block の中心となっているもので
    16バイトの構造体である lock resource block というのがあるんですが,それは,
    行やキーやページやテーブル等を一意に識別するもので,
    ロック機能中心自体というより,本来は,データ側の機能です。
    SQL Server は,一意に行を識別するものをデータページ内には,
    埋め込まないという仕様になってます。

    なので,実質は,lock owner block という32バイトの構造体が
    ロックリソースのロック情報を表すということになります。

    あと,各ロックリソースは,lock hash table を用いて動的に管理されてます。

    2006年8月16日 16:03
  • [補足]
    上の図だと,
    リストが物理的に前に詰まって行っている感じになってしまっていますが,
    いわゆるリストなので,物理的に移動してたり,並び返しているわけでなく,
    リストポインタで繋がっているので,
    要素が抜けたり足されたりしても,それらポイント先を変更しているということです。
    lock block から出ている ownerリスト向けや waiter リスト向けのリストは,
    図の ---> は,そういうリストの先頭を指していて,
    上の図の lock owner block と lock waiter block は,
    実際には同じもので,ポインタの付け替えで変わっているだけです。

    convertリスト向けのものもあるので,
    lock block は,
     ・ grant状態の lock owner block リストへのポインタ
     ・ wait状態の lock owner block リストへのポインタ
     ・ convert状態の lock owner block リストへのポインタ
    をもっていることになります。

    lock block は,ロックタイプの変換待ちリスト向けのものを含めた,
    それらリストを指すポインタや,
    16バイトの lock resource block という
    ロックリソースを一意に識別する構造体 等を持っています。


    Inside SQL Server 2000 を読むと,
    あるロックに関して,
    owner (32バイト) + waiter(32バイト) で 64バイト必要な感じにも
    読めてしまいますが,そういうことではないようで,

    a lock waiter block of another 32 bytes という表現は,
    特に another という意味は,
    待ちがひとつ増えるたびに,32バイト分だけ,
    waiterリストとして連なっている要素が増えて行くという意味です。
    2つ待っていれば,64バイト,3つ待っていれば,96バイトという意味です。

    ただし,共有ロックの場合も,同時にいくつも可能なので,
    lock owner block もリストとして32バイトずつ増えることが可能です。
    排他ロック(X, exclusive)の場合は,もちろん,ひとつだけで,
    他は,waiter になります。

     

    [追記]
    なので,当初の質問ですが,
    同じロックリソースに対しては,同時に実行されても,
    waiterリストに追加されて,順番待ちになるので大丈夫です。
    もちろん,リストに追加される瞬間は,
    相互排除しないといけないので,ラッチと呼ばれている
    SQL Server リソースに対するロックをかけて,リストに追加,
    で,ラッチ解除という風になります。

    リソースに対してラッチをかけるときに,すでにラッチがかかっていれば,
    しばらく待ち状態(spinning)になることがあります。
    その待ち状態の平均タイムとかを確認することも可能です。

    2006年8月16日 16:06
  • 稍丼さん、、みなさん、こんにちは。

     稍丼さん、こちらのレベルにあわせて説明していただき

    本当にありがとうございます。紹介していただいた書籍ですが

    英語が駄目なので和訳版がでるのを待つか、英語が英語版の書籍を

    読めるほど堪能になった時に購入したいと思います。

     本当に詳細に説明していただいてありがとうございました。

    後日2度3度と読み返してみたいと思います。

    稍丼さんのおかげでSQLサーバのことがちょっとわかった気になってます。(^^))

     稍丼さん、trapemiyaさん、中博俊さん、じゃんぬねっとさん、皆さん、
    本当にありがとうございました。

    2006年8月17日 8:28