August 20, 2016 at 6:37 am
Hi
SQL Server 2012
db server receives insert/update calls from multiple application servers at exactly same time.
Sp took 1 sec to complete one call, so multiple executions of same sp starts before completion of 1st sp call.
Therefore my following concurrency check (i.e. not change case status if once finished) fails.
Part of my sp code as under:
IF EXISTS (select 1 from MySchema.MyCasesTable where CaseID=@ParameterCaseID and CaseStatusID='FinishStatusID')
Begin
Return
End
Else
Begin
insert/update statements
End
I have tried nolock in IF block, transaction in else block but nothing work.
If db server got the calls of Finish status with another status at exactly the same time, then some time my cases status update to a pervious status even after getting finish status.
Wishes
August 20, 2016 at 7:43 am
While I'm not sure about your stack of insert and update statements, maybe you could take advantage of the already existing "where" clause.
for instance:
insert into MySchema.MyCasesTable
(
column_name1,
column_name2
)
select
source_column_name1,
source_column_name2
)
from
source_table_name
where
original_critiera_source = 'original criteria value'
---- and here's the new addition
and not exists (select 1 from MySchema.MyCasesTable where CaseID=@ParameterCaseID and CaseStatusID='FinishStatusID')
This way your insert only does the work when the condition is how you want it.
I'm sure theres more details and other options but just tossing that out for your consideration.
August 20, 2016 at 7:50 am
weekend_79 (8/20/2016)
HiSQL Server 2012
db server receives insert/update calls from multiple application servers at exactly same time.
Sp took 1 sec to complete one call, so multiple executions of same sp starts before completion of 1st sp call.
Therefore my following concurrency check (i.e. not change case status if once finished) fails.
Part of my sp code as under:
IF EXISTS (select 1 from MySchema.MyCasesTable where CaseID=@ParameterCaseID and CaseStatusID='FinishStatusID')
Begin
Return
End
Else
Begin
insert/update statements
End
I have tried nolock in IF block, transaction in else block but nothing work.
If db server got the calls of Finish status with another status at exactly the same time, then some time my cases status update to a pervious status even after getting finish status.
Wishes
This seems to be a variation on the classic UPSERT problem, and you are not the first nor the last to experience it (in SQL Server or other RDBMS systems).
You REALLY have to nail the concurrency to not wind up with bad data. Typically this involves explicit transaction around the entire set of code and a HOLDLOCK/UPDATE lock on the initial action. That action will vary depending on what the most common action is: if most of the time the data is missing then it will be a check for existence. If it is normally there then you can do the UPDATE first to be a bit more efficient, capturing 0 rows affected as the indicator that you need to INSERT the missing row.
Alternatively if the data is protected by some form of uniqueness guarantee you can simply try to INSERT the row and trap the violation error and then to the UPDATE.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 20, 2016 at 10:09 am
Quick question, what is the Isolation Level set by the applications?
😎
August 20, 2016 at 11:00 am
http://source.entelect.co.za/why-is-this-upsert-code-broken
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2016 at 12:53 pm
GilaMonster (8/20/2016)
http://source.entelect.co.za/why-is-this-upsert-code-broken
Ok interesting that they suggested (partially) what I was thinking would work but with the XLOCK and HOLDLOCK hints. So is that suggesting that having the criteria in the select part means concurrency is broken without the hints?
edit: lol that's your article. So isn't "HOLDLOCK" sort of implying an on the fly "elevation to serializable?" Just edicating myself not trying to argue!
INSERT INTO dbo.UpsertTest (Value, DateStamp)
SELECT @RandomNumber, GETDATE()
WHERE NOT EXISTS (SELECT 1 FROM dbo.UpsertTest WITH (XLOCK, HOLDLOCK) WHERE Value = @RandomNumber);
August 20, 2016 at 3:12 pm
patrickmcginnis59 10839 (8/20/2016)
edit: lol that's your article. So isn't "HOLDLOCK" sort of implying an on the fly "elevation to serializable?" Just edicating myself not trying to argue!
Hehe. Yes, it's my company's blog not mine, because that was first written for an internal knowledge base.
HoldLock just says keep the locks until the transaction commits, it's more repeatable read than serialisable, it doesn't introduce the range locks that serialisable needs.
It may be redundant in some of the cases I discussed, for concurrency stuff like this I tend to err on the side of caution.
You need the XLock hint though, otherwise the EXISTS can take a shared lock, and multiple sessions can have those. I can't recall offhand whether the lack of the XLOCK hint causes duplicates or deadlocks. I suspect it'll be deadlocks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply