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 |
|
xpandre
Posting Yak Master
212 Posts |
 Posted - 2012-07-24 : 14:33:38
|
| I have 1 scenario where in:1 sp has a select statement which selects 1 record from a table and that would need to be assigned to an employee to be worked on..kind of assigning a ticket..and inserts that ticket no. with employee ID in a table, to confirm someone is working on it..Now the catch is at one point of time, around 40-50 employees log in to the system and call the SP to get the ticket no to be worked on.Now since I cannot have any kind of lock on my select, it happens at times that multiple employees selects 1 ticket in the select statement in the SP, but since it can be assigned to only 1 employee at a time, 1 call succeeds while the rest of the call fails with the unique key error.Just wanted to ask, if we have a way in sql server where in I can lock my table when a select is fired on it till the SP inserts the needed data and exists, so that we can process 1 employee at a time. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-24 : 15:13:40
|
| Put the SELECT and the INSERT in an explicit transaction. Post the SP if you want help.Be One with the OptimizerTG |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-07-24 : 17:24:03
|
| If you show us what you are trying to do we can probalby help. It sounds like a race condition, so you (probably) need to do your select/update in one statement, or at least lock the resources so another processes cannot access them. |
|
|
|
|
|
|
Data locking