如何在mysql中使用UPDATE原子地声明行或资源 [英] how to atomically claim a row or resource using UPDATE in mysql

问题描述

我有一张要自动要求的资源表(比如汽车).然后，我需要有关我刚刚索取哪种资源的信息.

如果每个用户限制一个资源，我可以执行以下技巧:

UPDATE cars SET user = 'bob' WHERE user IS NULL LIMIT 1
SELECT * FROM cars WHERE user = 'bob'

这样，我自动获取了资源，然后可以看到我刚刚声明的那一行.

当"bob"可以要求多辆车时，此方法不起作用.我意识到我可以得到bob已经提出的汽车清单，再声明另一辆，然后再次SELECT来查看发生了什么变化，但这感觉有些骇人听闻.

我想知道的是，有什么办法可以查看我最近一次更新时更新的行吗?

如果失败，是否还有其他技巧可以自动声明一行?我真的想避免使用SERIALIZABLE隔离级别.如果我做这样的事情:

1 SELECT id FROM cars WHERE user IS NULL
2 <here, my PHP or whatever picks a car id>
3 UPDATE cars SET user = 'bob' WHERE id = <the one i picked>

在这里REPEATABLE READ就足够了吗?换句话说，我是否可以保证其他一些交易不会声明我的软件在步骤2中选择的行?

解决方案

UPDATE cars SET user = 'bob' WHERE id = 123 AND user IS NULL;

更新查询返回已更改的行数.如果尚未更新，则表示您已经被其他人领取了汽车.

或者，您可以使用SELECT ... FOR UPDATE.

i have a table of resources (lets say cars) which i want to claim atomically. I then want information about which resource I just claimed.

If there's a limit of one resource per one user, i can do the following trick:

UPDATE cars SET user = 'bob' WHERE user IS NULL LIMIT 1
SELECT * FROM cars WHERE user = 'bob'

This way, I claim the resource atomically and then I can see which row I just claimed.

This doesn't work when 'bob' can claim multiple cars. I realize I can get a list of cars already claimed by bob, claim another one, and then SELECT again to see what's changed, but that feels hackish.

What I'm wondering is, is there some way to see which rows I just updated with my last UPDATE?

Failing that, is there some other trick to atomically claiming a row? I really want to avoid using SERIALIZABLE isolation level. If I do something like this:

1 SELECT id FROM cars WHERE user IS NULL
2 <here, my PHP or whatever picks a car id>
3 UPDATE cars SET user = 'bob' WHERE id = <the one i picked>

would REPEATABLE READ be sufficient here? In other words, could I be guaranteed that some other transactions won't claim the row my software has picked during step 2?

解决方案

UPDATE cars SET user = 'bob' WHERE id = 123 AND user IS NULL;

The update query returns the number of changed rows. If it has not updated any, you know the car has already been claimed by someone else.

Alternatively, you can use SELECT ... FOR UPDATE.

这篇关于如何在mysql中使用UPDATE原子地声明行或资源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！