如何在 MySQL 中锁定一行以供选择 [英] How to lock a row for select in MySQL

问题描述

一个程序会从一个表中选择几条记录并更新每一行，但它可以执行多次，这将导致多个进程同时完成相同的任务.

A program will SELECT several records from a table and update each row, while it can be executed many times, which will lead to several process will complete the same task simultaneously.

如何防止两个不同的进程更新表中的同一行.也就是说，如何保证每个进程可以SELECT不同的记录?MySQL 中的行选择级别是否有任何锁定?或者在这种情况下，有没有更好的方案来防止单行多次更新?

How can I prevent two different processes to update the same row in the table. That's to say, how can I ensure each process can SELECT different records? Is there any locks on row-select level in MySQL? Or in this situation, is there any better solution to prevent a single row updating for many times?

推荐答案

您可以使用 SELECT FOR UPDATE.在您的事务中，开始选择您想要锁定"的行，如下所示:

You can use a SELECT FOR UPDATE. Inside your transaction, start out selecting the rows that you want to "lock", something like this:

 SELECT * from TABLE where id = 123 FOR UPDATE;

如果两个不同的事务同时尝试执行此操作，MySQL 将使第二个事务等待，直到第一个事务提交事务.这样，您就可以放心，第二个事务只会在第一个事务完成后查看该行.

If two different transactions try to do this at the same time, MySQL will make the second one wait until the first one has committed the transaction. That way, you'll be assured that the second transaction only looks at the row after the first one is done with it.

这篇关于如何在 MySQL 中锁定一行以供选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！