August 19, 2016 at 2:34 am
Morning All,
I have this utter pile of rubbish system that is not going anywhere soon. Sadly.
The original developers who we (before my time) paid tons of money to for this opted not to use an identity field on tables, and instead, have a single table of numbers, for each table that requires and ID. This beautiful table looks something like this:
[Table: ID_GENERATOR_STORAGE]
ID_NAME VALUE
------------- ------------------
User 34556
Address 78231
Purchase 123412313
For this to 'work' there is a stored procedure, for some unknown reason in a different database, called GetNextID.
GetNextID does something like this:
SET NOCOUNT ON;
begin transaction;
declare @val bigint;
select @val = [VALUE] FROM ID_GENERATOR_STORAGE WITH (HOLDLOCK, UPDLOCK) WHERE ID_NAME=@id_name;
IF @@ROWCOUNT = 0
begin
set @return = -1;
commit;
return;
end
else
begin
set @return = (@val+1);
UPDATE ID_GENERATOR_STORAGE SET VALUE=@val+@step_size WHERE ID_NAME=@id_name;
commit;
return;
end
Please do note the hint on ID_GENERATOR_STORAGE: holdlock and updlock.
Needless to say this highly concurrent system is neither! Especially during peak times such as 9am (opening up time) and 3pm (crap its 3pm, I better do some work!).
There is zero approval to make this work sensibly, I just have to work around this problem.
With that; is there *anything* I can do to improve the blocking situation that is experienced daily.
There are 58 Rows in ID_GENERATOR_STORAGE. This DB was ported from MySQL about 7 years ago but is now in SQL Server 2008. At some point it will be moved on to a 2014 Enterprise box, so I am not against any 2014 only solutions if they exist.
The other problem that compounds this issue is the client timeout, there is a mixture of inline sql, prepared statements and stored procedures... Often, the user, who is rightly impatient because their application has hung whilst waiting on a new ID kills their app (or even their citrix session) which leaves a transaction open which means the blocking never clears up.
So, I am at a loss as to how to improve this nonsense.
Cheers All,
Alex
August 19, 2016 at 4:28 am
Edit: The SNAPSHOT and Cross Database Query aspect of Hekaton eliminates the use of that. Damn.
August 19, 2016 at 4:41 am
Running a separate 'sequence' table like this is a fairly well-known pattern and it can work extremely well if done correctly.
May I ask if the system has always displayed blocking (because it seems to have been around for a long time)? If not, you might want to prove to others that the GetNextId proc is truly the source of the problem.
Just looking at the proc, hitting the table twice (with select and then update) is not optimal. I think you should try and get it all done in an update statement. If you do this, it might not need the query hints. Obviously with any changes to something as vital as this you'll need to work out a good test system (particularly simulating concurrent multiple users)
Search sqlservercentral for Jeff's forum answers on sequence tables. I'm sure if Jeff is around he'll give you some good advice.
August 19, 2016 at 4:48 am
Not uncommon. Here's how someone who really knows what he's doing fixed the very same problem.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 19, 2016 at 5:04 am
Douglas, thanks! Yes they have had the problem for years, since buying it. Their solution until I joined was to have processes to kill lots of spids that 'seem' to be blocking... (Though in reality they were killing spids that were blocked, and also blocking and not killing the lead the blocker anyway) and also to throw more hardware at it. None of which helped.
So instead they employed more support staff to answer the hundreds of phone calls a day about the application hanging!
Both of the above seemed to be the wrong approach to me.
Whilst I can't disagree that it might be a common pattern, I can't see any benefit to it either that an identity wouldn't solve? Could you elaborate if you happen to have an example to hand?
Cheers
Alex
August 19, 2016 at 5:05 am
Thanks Chris, this is excellent, I particularly like this:
SET @NextID = NextID = NextID + @IncrementValue
I've never seen such an assignment!
August 19, 2016 at 5:17 am
Consider tossing the sequence table away and using SQL's built-in sequence objects (in the product since 2012 iirc) once you get up to SQL 2014. They have the same mechanics as identity columns in the background and don't block (downside is they will have gaps as a result)
You can then replace that 'mess' (I'm being polite :-)) with a
SELECT NEXT VALUE FOR <sequence name>
statement in the stored proc, and most of the problems around concurrency of the sequence table should go away (no guarantees about other problems).
If the sequence numbers must be gap-free, then stick with Jeff's solution. Sequences skip numbers when inserts fail (they always increment when you get the numbers from then, even if it's not used), and the sequence numbers jump after a restart (due to caching)
Edit, and instead of multiple columns in the sequence table you can create multiple sequence objects. Unfortunately the name of the sequence can't be a variable, so you'll need IF statements inside the procedure.
https://msdn.microsoft.com/en-us/library/ff878058.aspx
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 19, 2016 at 9:50 am
Thanks All, I am making good progress on the development copy because of your replies. Going to leave it soak over the weekend with the simulator running against it, and then let the 'testers' sanity check the results make sense...
Gail, THANKS! Sequence looks ideal when (IF WE EVER!!) move to this DB on to 2014/2016
Cheers All,
Alex
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply