如何从表中选择未引用的行并锁定它？
我有一张小木屋表，其中的小木屋由帐户引用...
CHALET
------
int ChaletId PK
int Berth
ACCOUNT
-------
int AccountId PK
int ChaletId FK
小木屋一开始没有引用。当用户购买小木屋时，代码需要找到未引用的小木屋并将其分配给新创建的帐户。我认为返回的小木屋需要有一个 UPDLOCK，直到引用它的帐户已被提交，以阻止并发购物者被分配到相同的小木屋。
我如何编写获取小木屋的 SELECT 语句？我在想这样的事情..
SELECT * FROM CHALET WITH (UPDLOCK) c
LEFT JOIN ACCOUNT a
ON c.ChaletId = a.ChaletID
WHERE a.ChaletID is null
AND Berth = 4
我认为问题是，如果这个查询同时运行，那么一个查询可能会锁定一个表的一半，而另一个查询可能会锁定另一半，从而确保死锁。有办法解决这个问题吗？例如，是否可以以相同的顺序锁定选定的小屋行？
干杯，伊恩。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
(UPDLOCK, ROWLOCK, READPAST)做你需要做的事情< /a>?Would
(UPDLOCK, ROWLOCK, READPAST)do what you need?我认为当您确实需要应用程序处理临时保留时，您正在尝试使用 SQL 并发锁定。
I think you are trying to use SQL Concurrency locking when you really need your application to handle provisional reservations.
尝试
但是为什么哦为什么你不使用这种东西的身份属性而不是尝试推出自己的身份属性？
try
But why oh why are you not using an identity property for this kind of stuff instead of trying to roll your own?