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.