December 2, 2008 at 4:01 pm
I have a very large stored procedure (called pr_insert_order) that saves an order into our database. Since it has a lot of moving parts, it is wrapped in a BEGIN TRANSACTION and COMMIT TRANSACTION. That way if anything blows up in the middle of the procedure, the whole transaction is automatically rolled back.
My problem is that we use counters in our system for things like "Next sequence number", "Next order number", "Next line item number", etc. So we have a stored procedure (called NextVal) that we call and it passes us the next available number in the sequence. In order to accomplish this, it throws a record lock on the row in a tiny utility table (SysData) that holds the next available sequence number. It grabs that number to return to the calling proc, then updates the row to the next number so the next time it is called it will return the next higher number.
Since the NextVal proc is called from within pr_insert_order, and pr_insert_order starts with BEGIN TRANSACTION and ends with COMMIT TRANSACTION, the update that takes place within NextVal gets tied into the overall transaction of pr_insert_order. As a result, two people running pr_insert_order at the same time can get stacked behind one another, due to the open lock on SysData created by the first call to NextVal by the first user in the mix.
I need a way to grab a next value without throwing locks on a table that will cause successive runs of a proc to get stalled behind one another. I considered doing an external procedure call to another instance of SQL server running on the same physical server, but that is way too much overhead for just one litttle table - i would have to have every customer re-install SQL to get the second instance - a nightmare.
I do not want to use an identity field in the tables to get a sequence number. This would present many additional challenges that are worse than my current problem.
I appreciate any help anyone can provide me. Below is a simplified code snippet from NextVal:
CREATE PROCEDURE NextVal
AS
DECLARE @x INT
UPDATE SysData set @x = val + 1, val = val + 1
RETURN @x
Thank You,
John
December 2, 2008 at 7:27 pm
John Barry (12/2/2008)
I do not want to use an identity field in the tables to get a sequence number. This would present many additional challenges that are worse than my current problem.
Heh... not likely... 😉 , but whatever...
The problem is that you do have the calls to NextVal within the declared transaction. To fix the problem, simply move those calls outside the transaction. We had a similar problem at my old job... it worked like a charm.
Also, you can speed things up in NextVal just a bit... compared to the transaction itself, it won't be much, but every bit helps...
CREATE PROCEDURE NextVal
AS
DECLARE @x INT
UPDATE SysData set [font="Arial Black"]@x = val = val + 1[/font]
RETURN @x
... and, Yes, it's a fully documented and well supported feature. Lookup "UPDATE" in Books Online and check for the syntax.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2008 at 7:53 pm
Jeff,
Thanks - I have used that syntax as well - it's cool. And I agree if I move the Nextval outside the transaction it would solve the problem. For example, if I just had to get one new sequence number, I could get it before starting the transaction, and then would enter the transaction with the sequence number loaded already. But the problem is that I never know how many new sequence numbers I will need before I start the transaction, since the transaction code is many lines long (a couple of thousand), and the order posting process is very complex. So that leaves me stuck with the problem of needed an indeterminate number of new sequence numbers while in the thick of a very long and complex transaction. For each new sequence number, I just want to pop one off a stack without affecting any other processes, because I don't want anyone to be parked behind me while I finish this transaction, as would happen if someone else was trying to pop a number off the same stack and I had an update lock on the row containing the sequence number.
December 2, 2008 at 9:55 pm
John Barry (12/2/2008)
For each new sequence number, I just want to pop one off a stack without affecting any other processes, because I don't want anyone to be parked behind me while I finish this transaction, as would happen if someone else was trying to pop a number off the same stack and I had an update lock on the row containing the sequence number.
Then you want to be using Identity values. That is what they were created for.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 2, 2008 at 10:46 pm
John Barry (12/2/2008)
Jeff,Thanks - I have used that syntax as well - it's cool. And I agree if I move the Nextval outside the transaction it would solve the problem. For example, if I just had to get one new sequence number, I could get it before starting the transaction, and then would enter the transaction with the sequence number loaded already. But the problem is that I never know how many new sequence numbers I will need before I start the transaction, since the transaction code is many lines long (a couple of thousand), and the order posting process is very complex. So that leaves me stuck with the problem of needed an indeterminate number of new sequence numbers while in the thick of a very long and complex transaction. For each new sequence number, I just want to pop one off a stack without affecting any other processes, because I don't want anyone to be parked behind me while I finish this transaction, as would happen if someone else was trying to pop a number off the same stack and I had an update lock on the row containing the sequence number.
If you first "post" to a temp table and change the NextVal code to allow for increments other than "1", you would have some very short, set based transactions instead of individual updates. That's how we really solved our problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2008 at 4:48 am
In one sense identity values would help, but in another sense, they create a lot of overhead and could require a lot of re-fashioning the application. For example we have many parent child relationships between sets of records in multiple tables. We can have an AD record that needs an ADID, then multiple DOC records, each of which must point to the AD, so we need the ADID before we can create the DOC records, then we need multiple INSERT records (each with an INSid) that all point to the DOC record using the DOCID. Now we can pop new ADIDs, DOCIDs and INSIDs off a stack and get it all set in RAM, then save the whole batch in a nice encapsulated transaction. If we want to use IDENTITY, we would need to generate an AD record, get the IDENTITY value, then use that to generate each DOC record, stopping each time to retrieve the IDENTITY value for each DOC record, then generate each INS record using the DOCID and ADID values. It becomes a much more complex dance back and forth to the server as opposed to setting it all up and letting it rip in a nice clean encapsulated transaction.
John
December 3, 2008 at 4:56 am
Jeff,
So are you saying that instead of creating a row at a time in the live table, stamping each one with a sequence number as you insert it, you would load them all into a temp table or a table variable, then at the end of the proc, when you are ready to commit, you would look at the temp table and say "I have 17 rows, let me grab the next 17 values of sequence number" (which you would do with a single call to the NextVal proc by allowing an increment of other than 1), then you would stamp each row in the temp table with its sequence number, then commit? If so, I guess that while we are still holding up the Sysdata table for a short spell between the time we call NextVal and the time we commit the rows, it is a much shorter spell. Have I understood the approach you are suggesting?
Thanks,
John
December 3, 2008 at 5:10 am
John Barry (12/3/2008)
If we want to use IDENTITY, we would need to generate an AD record, get the IDENTITY value, then use that to generate each DOC record, stopping each time to retrieve the IDENTITY value for each DOC record, then generate each INS record using the DOCID and ADID values. It becomes a much more complex dance back and forth to the server as opposed to setting it all up and letting it rip in a nice clean encapsulated transaction.
Every SQL Server developer ever has had to deal with this, and the solution is to just let the stored procedure handle everything, including the ID management. With a stored procedure, there is no "back and forth to the server", it's all on the server and it's much faster that way. The problem comes when developers get habituated on the concept of knowing the ID ahead of time, which with a little perspective can be seen to be neither necessary nor even necessarily desirable.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 3, 2008 at 7:13 pm
John Barry (12/3/2008)
Jeff,So are you saying that instead of creating a row at a time in the live table, stamping each one with a sequence number as you insert it, you would load them all into a temp table or a table variable, then at the end of the proc, when you are ready to commit, you would look at the temp table and say "I have 17 rows, let me grab the next 17 values of sequence number" (which you would do with a single call to the NextVal proc by allowing an increment of other than 1), then you would stamp each row in the temp table with its sequence number, then commit? If so, I guess that while we are still holding up the Sysdata table for a short spell between the time we call NextVal and the time we commit the rows, it is a much shorter spell. Have I understood the approach you are suggesting?
Thanks,
John
Yes... All except the stamp each row thing... I'll start the temp table with an identity of "0" and increment of 1... then, and add the first value of NextVal to (reserving 17 numbers with the increment) to that identity as you insert from the temp table to the final table... helps by eliminating an update on the temp table.
Bottom line is, instead of holding a monster transaction open, you boil it all down to a single multi-row insert into each table and the NextVal table is not involved in the transaction so no deadlocking or blocking.
If it's a GUI and you're inserting 1 manual row at a time from the GUI, it still works.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 4, 2008 at 11:15 am
Jeff,
This is great. You're awesome - thanks so much for your help. Death to RBAR !!!
Regards,
John
December 6, 2008 at 8:23 am
John Barry (12/4/2008)
Jeff,This is great. You're awesome - thanks so much for your help. Death to RBAR !!!
Regards,
John
Sorry, John... I got way behind on my emails...
First, thanks for the great compliment, but let's make sure that things are working correctly for you. How'd things work out on all of this?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 7, 2008 at 10:03 am
Jeff,
I took your approach to my team and they loved it. They were a bit squeamish, however, integrating it into their code as a maintenance release of our current major release. They will add it to the baseline and release that version with our next major so we can test it vigorously. For this interim service pack release, they went with another approach that allows them to leave their current code pretty much untouched (and continue their RBAR methodology for now) and still avoid the locking. Here is the link to the other approach:
http://www.mssqltips.com/tip.asp?tip=1293
What are your thoughts?
John
December 9, 2008 at 10:27 pm
That method using insert/rollback is a great place for more RBAR to occur... and it takes twice as long... 1 period for an insert... 1 period for a rollback... and it offers greater opportunity for deadlocks if someone accidently includes the proc in a transaction. Further, it would be a bit more difficult to update by increment to reserve, say, 1000 rows. You'd have to run the proc a thousand times or do one big insert from something like a Tally table and then rollback. Not very effective.
Nah... if you're going to be stuck with a sequence table, then make it so you can use an "increment" for the future. That way, it takes the same amount of time to reserve 10,000 numbers as it does just 1. And, you can make the same sequence table handle all the sequences you need. Your "NextID" table should look something like this and, of course, can handle as many tables as what you'd like to add to it...
CREATE TABLE dbo.NextID
(
TableName SYSNAME NOT NULL,
NextID INT DEFAULT 1 NOT NULL,
CONSTRAINT PK_NextID_TableName
PRIMARY KEY CLUSTERED (TableName)
)
You guys probably already have something similar to that. What you don't have is a "next id" proc that can handle an increment and still avoid deadlocks...
CREATE PROCEDURE dbo.GetNextID
@TableName SYSNAME,
@Increment INT = 1,
@NextID INT OUTPUT
AS
--===== Reserve the number of NextID's according to @Increment
-- Doing it all at once like this negates the need for a
-- Select on the table itself, thus helping prevent deadlocks.
UPDATE dbo.NextID
SET @NextID = NextID = NextID + @Increment
WHERE TableName = @TableName
--===== Now, in memory, recalculate and return the original value
-- of the NextID. Again, helps prevent deadlocks because
-- eliminates the need for a transaction on an UPDATE/SELECT.
SELECT @NextID = @NextID - @Increment
RETURN
GO
So, if you wanted just one new ID for, say, the customer table, you'd make the following call...
EXEC dbo.GetNextID 'Customer', 1, @NextID OUTPUT
If you wanted to reserve 15 numbers for the Customer table, and the current "NextID" for the customer table were 1001, you'd make the following call...
EXEC dbo.GetNextID 'Customer', 15, @NextID OUTPUT
... and @NextID would be returned as 1001 and the NextID table would be updated to 1016 as the next available ID for the next time you call it, effectively reserving the 15 consecutive numbers from 1001 to 1015.
Do the trick I told you about with adding @NextID to the IDENTITY column of a temp table, and you've got it made in the shade.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply