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 |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2008-09-05 : 13:38:57
|
| Hi there,I've got some holiday camp chalets to sell. When a user's online order completes, I need to pick a chalet appropriate to the class they've purchased (number of beds) and assign it their order. This assignment is done by inserting a row into a join table between their order and the chalet. (I'm using a join table because multiple orders can be assigned to a chalet if a user pays for additional features at a later date.)So to select a chalet that's free for assignment I select all the chalets and left join them to the join table. Any chalets that are avaiable will have null values in the right side of the join. Also, I'm ordering the chalets because I want to release them away in a particular order.Now the problem is that there's going to be some time in between selecting a chalet and inserting the row in the join table when a completely independent order could also select exactly the same still un-assigned chalet. So the problem is how to lock the retrieved chalet so that no one else can retrieve it whilst its not quite assigned to an order.I thought the Repeatable Read transaction level would help here but it doesn't because it doesn't stop concurrent users from seeing the read row - it only stops them from updating it.Another idea I had was to update a single free chalet with a guid and then retrieve the updated chalet using the guid as a key. The fact that the chalet was updated would mean it was locked so that no one else could even read it. Problem here is how do I know which chalet to update? And I don't think you can do an update with an ORDER BY clause which would spoil assigning the chalets in a particular order.Anyhoyw, any ideas? I think I may be left with checking for duplicate assignments after the assignment and then just make one user keep trying until they get a free chalet. Bit clunky though.Cheers, XF. |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-09-05 : 14:16:51
|
| Ahhh, looks like I can use a lock hint of XLOCK when doing the read to block concurrent users from reading the same row.Should it be XLOCK or UPDLOCK? Does it matter? |
 |
|
|
|
|
|
Locking a row upon a read