handling Concurrency in SQL SERVER 2005

Question

Hi,

I have one question for you, if you can answer and refer resource it will be great help.

I have a scenario where i need to create a appointment slot and a serial no for each slot memberwise.

ex:

Member Id |App Slot #

1|1

1|2

2|1

2|2

1|3

what im doing is take the Max slot number,increamenting it and insert it memberwise. but the problem is concurrent user can create a slot when i take the max slot after that if any other user insert the slot the value that im working with is no more valid, how to over come this problem

Thanks & Regards,

Sameer

Answer 1

Do all your work inside a transaction. http://msdn.microsoft.com/en-us/library/ms188929.aspx

Answer 2

You need to serialize the call to this code. You can do that by setting the isolation level of this transaction to Serializable. refer SET TRANSACTION ISOLATION LEVEL

There are side effects of using Serializable isolation level as it is the most restrictive mode of isolation. Use this isolation level for a very short duration to avoid blocking.