April 8, 2015 at 8:05 am
Hi guys, i've stucked in a problem with duplicated registers in my db, my scenario below:
First Transaction: Insert with merge (holdlock)
Something like this:
MERGE [edm-in].tbIn_Invoice WITH (HOLDLOCK) AS invoice
USING (SELECT @DocKey01, @DocKey02, @DocKey03) AS srcAccessKey (docKey01, docKey02, docKey03)
ON invoice.DocKey01 = srcAccessKey.docKey01 AND invoice.DocKey02 = srcAccessKey.docKey02 AND invoice.DocKey03 = srcAccessKey.docKey03
WHEN MATCHED THEN
UPDATE SET AuthorizationDate = @AuthDate, EmissionDate = @EmissionDate, VNF = @VNF, ReceiverId = @ReceiverId
WHEN NOT MATCHED THEN
INSERT (AccessKey, DocKey01, DocKey02, DocKey03, EmissionDate, IssuerId, LiveStatus, ReceiverId, VNF, InsertDate, DocNumber, DocSeries, AuthorizationDate)
VALUES (@AccessKey, @DocKey01, @DocKey02, @DocKey03, @EmissionDate, @IssuerId, 0, @ReceiverId, @VNF, GETDATE(), @DocNumber, @DocSeries, @AuthDate);
Second transaction:
Simple Select insert on "[edm-in].tbIn_Invoice" table, if register exists i will perform an update, else, insert.
Problem:
2 transactions running at same time, and i have same document on each transaction.. so, im getting duplicated registers. Is there any thing i can do to avoid it? o cant use Merge on second command, since queries is generate dinamically on my .net application
Thanks
April 8, 2015 at 11:39 am
Make sure you have a unique constraint to prevent getting duplicate values.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2015 at 12:30 pm
thx man, but create an unique index is not a possibility, we will need to refactor our code to handle the exception and re-process document, since we populate many other tables after that insert, and if it raise an duplicated key error, the process will be abortated and we will lose data
April 8, 2015 at 12:39 pm
Probably no real way to prevent it while using MERGE then. It just doesn't have much flexibility. You'd need to have a read in place to prevent the insert, and, probably, best to put a UPDLOCK on that read in order to prevent deadlocks. So read, then insert/update, but as separate statements.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply