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 |
|
karlman
Starting Member
11 Posts |
 Posted - 2009-12-17 : 18:34:17
|
| I have a stored sprocedure that is used to create the next primary key for a table and insert a place holder record.Can stored procedures run simultaneously? Or on the database engine level each request will always happen in sequence?Just curious if it could ever occur that two sp running at the very same momemnt both generate the same primary key and one would fail trying to insert it.Thank you,Karl |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-12-17 : 18:56:08
|
| karlwhy are you doing that? a next primary key in a stored procedure. why don;t you let sql do that for you and not worry about that,<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
karlman
Starting Member
11 Posts |
Posted - 2009-12-17 : 22:50:12
|
quote:
Originally posted by yosiasz
karlwhy are you doing that? a next primary key in a stored procedure. why don;t you let sql do that for you and not worry about that,
Not sure what you mean, are you talking about an Identity column? This is a job ticket table and the PK is a specially formatted job number YYYYMMDD00001. I wanted to use that as the PK instead of a surrogate.ThanksKarl |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-17 : 22:59:10
|
| Yes stored procedures can run concurrently. For your PK, you'll need specifically hold an update lock when retrieving the max value in the table. This will cause performance issues as your inserts will be serialized. With identity, you can have that happening simultaneously. If you have low volume of inserts, then the performance issue may not be relevant.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
karlman
Starting Member
11 Posts |
Posted - 2009-12-18 : 00:55:23
|
quote:
Originally posted by tkizer
Yes stored procedures can run concurrently. For your PK, you'll need specifically hold an update lock when retrieving the max value in the table. This will cause performance issues as your inserts will be serialized. With identity, you can have that happening simultaneously. If you have low volume of inserts, then the performance issue may not be relevant.Tara Kizer
That is exactly what I needed. Inserts on this table are relatively low volume so the chance of a collision is low. However, if it can happen then I want the code to handle it.The other option is two simply let it fail and have the application handle the error and try again but this seems cleaner.Thank you!Karl |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-18 : 01:00:44
|
| You're welcome. Unfortunately I have a system designed like this and it is high volume so we do see huge hot spots on that particular table. Here's what you'll need to add to your SELECT query that gets the current MAX: WITH (UPDLOCK). Put the SELECT and the INSERT into a transaction. Do not commit until the INSERT has completed. Once the transaction has been completed, the UPDLOCK is released which means another query can get the new MAX now.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
|
|
|
|
|
Stored Procedure Concurrence