July 29, 2016 at 4:26 am
Hi please find attached.
Procedure:
CREATE PROCEDURE [dbo].[RSDocument_Store]
-- Add the parameters for the stored procedure here
@ID int = 0,
@DocumentType int,
@EntityID int,
@VersionNumber int=1,
@DocumentGUID uniqueidentifier=null,
@BorrowerDocumentGUID uniqueidentifier=null,
@LenderDocumentGUID uniqueidentifier=null,
@BinaryLength bigint,
@BinaryData varbinary(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @ID=0
BEGIN
DELETE [dbo].[RSDocuments] WHERE DocumentType=@DocumentType AND EntityID=@EntityID AND DocumentType <> 5 AND DocumentType <> 130 AND DocumentType <> 170
INSERT [dbo].[RSDocuments](DocumentType,EntityID,VersionNumber,DocumentGUID,BorrowerDocumentGUID, LenderDocumentGUID,BinaryLength,BinaryData)
VALUES (@DocumentType,@EntityID,@VersionNumber,@DocumentGUID,@BorrowerDocumentGUID, @LenderDocumentGUID,@BinaryLength,@BinaryData)
SELECT @ID = @@IDENTITY
END
ELSE
BEGIN
UPDATE [dbo].[RSDocuments]
SET DocumentType=@DocumentType,
EntityID=@EntityID,
VersionNumber=@VersionNumber,
DocumentGUID=@DocumentGUID,
BorrowerDocumentGUID=@BorrowerDocumentGUID,
LenderDocumentGUID=@LenderDocumentGUID,
BinaryLength=@BinaryLength,
BinaryData=@BinaryData
WHERE ID=@ID
END
SELECT * FROM [dbo].[RSDocuments] WHERE ID=@ID
END
July 29, 2016 at 4:45 am
I'm afraid you need to do the hard work on this one yourself. Start here[/url].
Incidentally, I'd advise you to use an OUTPUT clause in your DELETE and UPDATE statements, instead of relying on @@IDENTITY. It will probably perform better, and it doesn't run any of the risks mentioned in this topic.
John
July 29, 2016 at 4:47 am
This is some old code that just started causing problems, all new procedures are using OUTPUT. thank you for the link!
July 29, 2016 at 4:59 am
You should be looking at the section with delete and insert. Two concurrent session got through the delete, may be got page locks due to lock escalation, and both wait for the other to release so that they can proceed with insert.
Things you may consider,
1) See the plan for the delete and optimize if necessary
2) Disable lock escalation
3) worst case, take a higher lock while delete
July 29, 2016 at 5:03 am
Thank you Joe! I will look into it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply