How do I SELECT an un-referenced row from a table and lock it?

I have a table of chalets where a chalet is referenced by an account...

CHALET
------
int ChaletId PK
int Berth

ACCOUNT
-------
int AccountId PK
int ChaletId FK

The chalets start off un-referenced. When a user buys a chalet the code needs to find an unreferenced chalet and assign it to a newly created account. I think that the returned chalet needs to have an UPDLOCK on it until the account that will reference it has been commited in order to stop a concurrent shopper from being assigned the same chalet.

How can I write the SELECT that fetches a chalet? I was thinking something like this..

SELECT * FROM CHALET WITH (UPDLOCK) c
LEFT JOIN ACCOUNT a
ON c.ChaletId = a.ChaletID
WHERE a.ChaletID is null
AND Berth = 4

I think the problem is that if this query is being run concurrently then one query might lock half of one table and another might lock the other half and a dead lock would ensure. Is there a way around this? For example, is it possible to lock the selected chalet rows in the same orders?

Cheers, Ian.

1
задан Ian Warburton 11 September 2010 в 15:29
поделиться