开发者

Deadlock while inserting into sql server table from multiple machines

I am having a deadlock situation while inserting records into the sql table from multiple machines (more than 5 machines). I am running following SP from multiple machines at the same time and getting deadlock.

create proc InsertDocuments (@folderId int, @entryIdTable EntryIdTable readonly)  
  begin tran
    insert into [transaction] (transaction_date) values (CURRENT_TIMESTAMP); 

    select @transactionId = SCOPE_IDENTITY();

    insert into document with (TABLOCK) (entry_id, transaction_id)  select entry_id, @transactionId from @entryIdTable ;

    insert into folder_document with (TABLOCK) (source_folder_id, document_id) select @folderId , document_id from document where transaction_id = @transactionId;
  commit

Document Table has primary key document_id which is also the identity column. Transaction_id in Transaction table is also identity column. EntryIdTable is the userdefin开发者_开发知识库ed table type.

Any input will be highly appreciated.


Bibek Dawadi


Here is the issue: "Specifying the TABLOCK hint on a table that is the target of an INSERT statement has the same effect as specifying the TABLOCKX hint. An exclusive lock is taken on the table."

http://msdn.microsoft.com/en-us/library/ms174335.aspx

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜