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
精彩评论