为什么要使用SELECT FOR UPDATE? [英] Why use SELECT FOR UPDATE?

问题描述

我对我们使用SELECT FOR UDPATE的目的有疑问?它到底是做什么的?

I have question regarding what purpose we are using SELECT FOR UDPATE? What does it do exactly?

我有2个表，因此我需要从表中选择行并更新相同的行.

I have 2 tables, from that I need to select rows from table and update the same rows.

例如:

选择查询

SELECT * from  t1 WHERE city_id=2 for update

更新查询

UPDATE t1 SET final_balance = final_balance - 100 WHERE city_id ='2'

我的问题-这真的会锁定读取操作，直到我完成更新，或者它究竟处理了什么?

My question - Does this really lock the read operation till my update is done, or what does it exactly deal with?

我的想法是，在我完成更新之前，没有人可以读取/更新此行.

谢谢！

推荐答案

SELECT ... FOR UPDATE将使用写(独占)锁锁定记录，直到事务完成(提交或回滚)为止.

SELECT ... FOR UPDATE will lock the record with a write (exclusive) lock until the transaction is completed (committed or rolled back).

要选择一条记录并确保在更新之前不对其进行修改，可以启动事务，使用SELECT ... FOR UPDATE选择记录，进行一些快速处理，更新记录，然后提交(或回滚)事务

To select a record and ensure that it's not modified until you update it, you can start a transaction, select the record using SELECT ... FOR UPDATE, do some quick processing, update the record, then commit (or roll back) the transaction.

如果在事务(自动提交为ON)之外使用SELECT ... FOR UPDATE，则该锁仍将立即释放，因此请确保使用事务来保留该锁.

If you use SELECT ... FOR UPDATE outside of a transaction (autocommit ON), then the lock will still be immediately released, so be sure to use a transaction to retain the lock.

为了提高性能，请不要长时间打开事务，因此应立即进行更新.

For performance, do not keep transactions open for very long, so the update should be done immediately.

这篇关于为什么要使用SELECT FOR UPDATE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！