MySQL中的SELECT FOR UPDATE有多安全

Here is my Query:

这是我的查询:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
DROP TEMPORARY TABLE IF EXISTS taken; 

CREATE TEMPORARY Table taken(
    id int,
    invoice_id int
);

INSERT INTO taken(id, invoice_id)
SELECT id, $invoice_id FROM `licenses` l
WHERE l.`status` = 0 AND `type` = $type
LIMIT $serial_count
FOR UPDATE;

UPDATE `licenses` SET `status` = 1
WHERE id IN (SELECT id FROM taken);

If I'm going to face high concurrency is the query above thread-safe? I mean I don't wanna assign records which has already assigned to another one.

如果我要面对高并发性,那么上面的查询是否是线程安全的?我的意思是我不想分配已分配给另一个的记录。

1 个解决方案

#1

With your FOR UPDATE statement, you are locking all selected licenses until you perform an update, so you can be sure that there will not be concurrency problem on those records.

使用FOR UPDATE语句,您将锁定所有选定的许可证,直到执行更新,因此您可以确保这些记录不会出现并发问题。

the only problem i can see is that if your query requires a lot of time to perform (how many licenses do you expect to process at every query?) and other queries requires licenses (even read queries are locked) on the same time, your system will be slowed down.

我能看到的唯一问题是,如果您的查询需要大量时间来执行(您希望在每次查询时处理多少许可证?)而其他查询需要同时执行许可证(甚至读取查询被锁定),系统将放慢速度。