July 11, 2018 at 12:02 am
Dear All,
I have a application with SQL Server 2008 r2 DB which creates some Ticket ID's based on last ID + 1 logic.
We have some concurrency issues where two users get same ticket ID.
We have identified a fix to use Insert/Update logic (Upsert), but have issues in full proof testing it.
We tried below testing ways:
> Screen similuation -- Two users clicking on create button simultaneously.
> Run stored procedure by passing same data at same time (from two different sessions).
This works for such scenario, but there is always a millisecond gap which worries me is not a good way of test.
Is there any simulator tool (free is good 🙂 ) or if I can use something within SQL server (any feature) to test this thoroughly.
Any pointers would be helpful.
July 11, 2018 at 7:17 am
you can try distributed replay controller. It's part of SQL 2012 and you might be able to use it with 2008 as well. It's more for load testing, but might work for you
July 11, 2018 at 11:11 am
GonnaCatchIT - Wednesday, July 11, 2018 12:02 AMDear All,
I have a application with SQL Server 2008 r2 DB which creates some Ticket ID's based on last ID + 1 logic.
We have some concurrency issues where two users get same ticket ID.We have identified a fix to use Insert/Update logic (Upsert), but have issues in full proof testing it.
We tried below testing ways:
> Screen similuation -- Two users clicking on create button simultaneously.
> Run stored procedure by passing same data at same time (from two different sessions).
This works for such scenario, but there is always a millisecond gap which worries me is not a good way of test.Is there any simulator tool (free is good 🙂 ) or if I can use something within SQL server (any feature) to test this thoroughly.
Any pointers would be helpful.
Is the logic changeable ? You might want to consider a SQL upgrade to at least SQL 2012, where SEQUENCE objects become available, resolving the problem much more easily.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 11, 2018 at 1:30 pm
Check this link for some free tools and suggestions. If you scroll down to the C# method, you can read about how this one started from trying to test the same thing. There is also a link to the original article about the same upsert issues:
Generating Concurrent Activity
Sue
July 12, 2018 at 3:24 pm
No load test, but I did the tests for you
https://sqlinthewild.co.za/index.php/2018/02/27/homebuilt-sequential-columns/
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply