主页 / 问题 / 729438
Accepted
AseN
Asked:2020-10-11 19:52:30 +0000 UTC2020-10-11 19:52:30 +0000 UTC

粒度选择，然后更新

  • 772

有没有办法在选择集上执行选择后更新，这样在两个查询之间不执行来自其他连接的查询，同时不锁定整个表？

任务是平庸的 - 选择 N 条记录然后更新所有记录（几个字段），以便在选择和更新相同记录之间不会在其他连接中并行选择。

诸如 select for update 之类的锁不适合，因为如果在索引字段的“空间”上设置了条件，它们只会锁定带有索引的记录。

有没有足够的方法来解决这个问题？

更新：

请求看起来像这样（它们都在触发器中）：

START TRANSACTION ;

        DROP TABLE IF EXISTS TempTable;
        CREATE TEMPORARY TABLE TempTable AS(
            SELECT units.id AS id, units.authkey AS authkey
            FROM availablePublicUnitsView units
                LEFT JOIN media_info
                    ON units.id=media_info.unit_id AND media_info.media_id=mediaId
            WHERE media_info.media_id IS NULL
            LIMIT unitsCount
            #FOR UPDATE
        );

        UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash
        WHERE id IN (SELECT id from TempTable) AND reserved=false;

        SELECT * FROM TempTable;

COMMIT ;

我不是 SQL 专家，所以如果我做错了什么，我会很高兴听到它是什么。

mysql

2 个回答

  1. Best Answer

    结果，在进行了几次负载测试后，不使用临时表（每次都重新创建，占用大部分时间）但使用锁进行更新的选项被证明是最佳选择。

    首先，所有必要的记录都用 SHA-256 散列形式的选择“密钥”更新（算法不是关键的），然后选择所有具有设置散列的记录，但在事务完成之后。该解决方案运行速度非常快，并且阻塞最小。

    同样重要的是要注意availablePublicUnitsView选择视图中的数据，按随机顺序（ORDER BY RAND）预先排序，将锁减少到几乎为零。

    CREATE PROCEDURE select_and_reserve_units(
    IN unitsCount INT,
    IN mediaId VARCHAR(40),
    IN reservationHash BINARY(64))
BEGIN

START TRANSACTION ;

    UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash
    WHERE id IN (

        SELECT units.id
        FROM availablePublicUnitsView units
            LEFT JOIN media_info
                ON units.id=media_info.unit_id AND media_info.media_id=mediaId
        WHERE media_info.media_id IS NULL    
        FOR UPDATE

    ) LIMIT unitsCount;

COMMIT ;

SELECT unit.id AS id, unit.authkey AS authkey FROM units
WHERE reservation_hash=reservationHash AND reserved=true;

END;//

    PS 显然，是时候切换到 PostgreSQL 了……

    • 2

  2. 我已经明白了，有必要禁止在相同的记录上并行执行这些请求吗？

    尝试更改隔离级别。因此使用 SELECT FOR UPDATE。

    将事务隔离级别设置为未提交；

    READ UNCOMMITTED - 允许其他人读取数据，即使事务未提交。

    或 REPEATABLE READ - 应该只阻止可更改的行。

    • 1

相关问题