| Author |
Topic |
|
mweber26
Starting Member
4 Posts |
 Posted - 2009-01-02 : 15:56:50
|
| I need some help with an identity-type column in a table. I have a order table that has an order number field, the order number is a incrementing value but I can't use an identity column because the order number sequence is different for each customer.So I added another table that hold the "current number" for each customer, and have a stored procedure that does a select for N, then updates the table with the N+1. But there is a problem with locks/deadlocks when two threads try to add a order at the same time. Also, the logic just seems wrong since two threads could execute the select and get the same number before trying to lock for the update.Any thoughts? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-01-02 : 16:30:31
|
| well if all your access to these 2 columns is from that stored procedure i guess the simplest thing would be to use sql server application locks:http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005they basicaly lock a part of code that executs so any sproc call will wait for previous sproc call to finsih. Note that they don't lock data.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
mweber26
Starting Member
4 Posts |
Posted - 2009-01-02 : 16:37:29
|
| Right, I looked into that. I was really hoping to do it with some sort of row level data lock hold on the select. This would allow multiple, different customers to produce an order number at the same time. The problem with the application lock is that only one customer ever can generate a number at the same time. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-01-02 : 16:40:44
|
| not if you name your transaction name nas a customer id in it.if that doesn't work for you'll have to chage the db design as there is no nice solution to this problem with data locking.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
mweber26
Starting Member
4 Posts |
Posted - 2009-01-02 : 17:04:52
|
| Oh -- I didn't realize that they were a per name resource, but now that I think about it, obviously they would have to me.Going slightly off topic, I am just trying to understand a little more. In my imagination, what I would try to do if I wanted to pull off what I described would bebegin transelect number from num_list where customer_id=1111update set number=N+1 where customer_id=1111end tranwhere the select statement would be the one to request the exclusive row-level lock on the data, the update would use the already existing lock and the end tran would release the lock. When you say it can't be done, are you saying that there is no way for a select to request an exclusive lock? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-01-02 : 17:08:04
|
| selects don't take exclusive locks. you may force it with hints but that doesn't really help you.any solution based on data locks for this scenarion will give you deadlocks eventually.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-01-02 : 17:13:29
|
| Have you considered simply using an identity for order number. You can always derive each customer's order numbers based on their identity sequence when you report them. That would avoid all the locking (and deadlocking) mess.Be One with the OptimizerTG |
|
|
|
mweber26
Starting Member
4 Posts |
Posted - 2009-01-02 : 17:21:48
|
| In the system, the order number needs to be reported to when it is initially created, so any post-processing wouldn't work in this situation.The application locks worked perfectly, thanks. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-01-02 : 17:28:20
|
| great!___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
|
locks for identity-type column