选择更新 [英] SELECT FOR UPDATE

问题描述

可能重复:
选择… FOR UPDATE和MAX()

Possible Duplicate:
SELECT … FOR UPDATE and MAX()

此查询锁中的哪些行?

select max(id) from table where id like '9%' for update

如果另一个用户运行此查询会发生什么?

what occur if another user run this query ?

这是相关问题.

推荐答案

如果id上没有索引，这将锁定所有记录.但是我想你有这样的索引.因此，这将锁定所有匹配的记录，包括中间的一些记录(如果您锁定3和5，则4也被锁定)

If you have no index on id, this will lock all of the records. But I guess you have such index. So this will lock all records that are matching, including some records in between (if you are locking 3 and 5, 4 is also being locked)

SELECT ... FOR UPDATE读取最新的可用数据，并在读取的每一行上设置排他锁.因此，它设置了与行中搜索的SQL UPDATE相同的锁.

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

修改 在SELECT max(id)FROM ...的情况下，您不需要从表中读取任何行，因为可以从索引中获取此信息.如果您只想锁定一行，则正确的查询将是SELECT * FROM table WHERE id = SELECT max(id)FROM table

edit In the case of SELECT max(id) FROM ... you don't need to read any rows from the table, because this information can be optained from the index. If you want to lock exactly one row, the correct query would be SELECT * FROM table WHERE id = SELECT max(id) FROM table

这篇关于选择更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！