May 18, 2011 at 8:54 am
Hi Gurus,
Set up for old code: A table was created with 1 row and 1 column to hold a number between 100000 and 899999. Application calls stored procedure that selects this number and then increments it by one for the next call using an update statement. To guarantee that the number was unique every time the sp was called, the isolation level was set to serializable.
Problem: When this sp is called by more than one process at the exact same time, deadlock is created and the sp fails for one of the processes.
Suggested solution: Alter table to use identity insert on only column and return identity upon insert. Remove update statement and isolation level serializable. When identity gets to 899999, truncate table and reseed to 100000.
So although my solution has been tested and confirmed, the developer is asking why the isolation level he used is not working correctly. I am trying to explain why isolation level serializable should not be used in this way. Can someone give me some good situations as to when you would use SET ISOLATION LEVEL above the default in a transactional database? I really need to explain to him that his original method is not just incorrect, but also poor design.
Thanks,
Jared
Jared
CE - Microsoft
May 18, 2011 at 9:11 am
The following article may give you some information and help you make your case.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2011 at 9:30 am
I don't think you need to be so harsh about serialisable transactions, you just need to explain that it effectively puts the database into single user mode - you'd need to have queuing in this case to handle multiple calls ( I'm not saying that's a solution - just an explanation )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 18, 2011 at 10:54 am
Be careful in choosing an identity column over the solution that is in place. There is a major difference which is that identity can introduce gaps.
Is there a requirement that says your sequence must not include gaps?
PS There is nothing inherently wrong with the SERIALIZABLE isolation level and is defined by the SQL standard. It's there for a reason and like everything else offers advantages and disadvantages depending on the scenario.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 23, 2011 at 8:41 am
Thank you everyone for your comments. The ultimate goal here was to generate a number within a specific range that is unique. This range could reset once the maximum had been reached. What we decided to do was use the identity column along with the mod operator (%). Since our range is 100000 - 899999 we would use the following statement:
INSERT INTO TableA (CreatedDate)
SELECT GETDATE()
SELECT scope_identity() % 900000 + 100000
This will guarantee us a number within our range that will automatically reset when the maximum is reached. This table is not queried by the application or any reports, it is simply used to generate this number and spit it back to the application. I believe that this method will give us the result we have been looking for.
Thanks,
Jared
Jared
CE - Microsoft
May 23, 2011 at 10:08 am
Thanks for the post back. Keep sequences in mind when you upgrade to SQL 11 (code named "Denali")
...you just manually implemented Example D from this article:
http://msdn.microsoft.com/en-us/library/ff878058%28v=SQL.110%29.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply