Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnjbarryjr
Starting Member
1 Post |
 Posted - 2008-12-02 : 22:10:16
|
| 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 NextValASDECLARE @x INTUPDATE SysData set @x = val + 1, val = val + 1RETURN @xThank You,John |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-12-03 : 05:26:10
|
| get the next number before you start the transaction. or use nolock in the function.but i guess you could introduce new problems with that because you could get same two numbers for different pr_insert_order sprocs___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-03 : 06:23:33
|
| If you don't want an identity column in the table, how about a seperate table with only a single column plus an identity column (id). Inside your transaction do an insert to this table and retrieve the id value created e.g INSERT INTO TableName ([column] ) VALUES (getdate()) OUTPUT INSERTED.ID --use this as IDIf the transaction is rolled back this insert will be also. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-03 : 06:58:47
|
[code]CREATE PROCEDURE NextValASDECLARE @x INTUPDATE SysData set @x = val = val + 1RETURN @x - 1[/code]
E 12°55'05.63"N 56°04'39.26" |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-12-04 : 01:37:43
|
| What's the issue with identity? It's fast, scalable, does what you want. I fail to see any downside, especially given the sort of hoops you've already resorted to. Why reinvent your own square wheel? |
|
|
|
|
|
|
|
|
Processing a non-locked row update within TRAN