锁定池中的资源，同时允许其他人从资源池中订购其他资源
我正在建立一个用于订购资源的网站。我有一个资源池（比如 10 个资源），当用户想要订购资源时，我会检查哪些资源是空闲的并将其分配给用户。 我正在使用 MYSQL innoDB 表并锁定行，
update ResourceTable SET SOMEFIELD='1'
但如果第一个事务尚未完成，现在另一个用户正在搜索（使用
select * FROM ResourceTable WHERE SOMEFIELD!='1' LOCK IN SHARE MODE
，用户正在等待第一个事务完成（尽管还有其他免费资源）。 如果我删除锁定共享模式，我将获得应该锁定的资源。
如何让多个用户无需等待即可订购资源？
（我想以某种方式选择未锁定的行 -> MYSQL 中不存在）
I'm building a website for ordering resources. I have a pool of resources (say 10 resources) and when a user wants to order a resource, I check which is free and assigning it to the user.
I'm using MYSQL innoDB table and locking the row using
update ResourceTable SET SOMEFIELD='1'
but if the first transaction didn't finish yet and now another user is searching (using
select * FROM ResourceTable WHERE SOMEFIELD!='1' LOCK IN SHARE MODE
, the user is waiting for the 1st transaction to finish (although there are other free resources).
If I remove the LOCK IN SHARE MODE, I will get a resource that is supposed to be locked.
How can I allow multiple users to order resources without waits?
(I though of somehow selecting rows that are not locked -> not exist in MYSQL)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
解决方案可能是在更改 SOMEFIELD 的值之前锁定整个表。更新此值是一项非常快速的操作，因此即使站点的一些传入请求需要几乎同时访问资源池，并且一个请求正在更改 SOMEFIELD，其他请求也只需等待一小部分。第二个是释放表上的锁。
The solution may be to lock the entire table before changing the value of SOMEFIELD. Updating this value is a very quick operation, so even if a few incoming requests to your site need to access the resource pool near-simultaneously, and one request is in the process of changing SOMEFIELD, the others have to wait only a fraction of a second for it to release the lock on the table.