如何在不阻塞的情况下锁定mysql中的行? [英] How to lock row in mysql without blocking?

问题描述

我在mariadb中有一个表，我在其中保存了一些RSS feed的链接:

I have a table in mariadb where I save links to some RSS feeds:

id | url                    | done
------------------------------------
 1 | http://example.com/rss | true
 2 | http://example.org/rss | false
 3 | http://google.com/rss  | false

现在一个流程/工作人员正在更新其中一个RSS feed时，我希望它从其他流程/工作人员中隐藏/隐藏该行，以使他们不会重复工作或陷入僵局.

When now one process/worker is updating one of the RSS feeds I want it to hide the row from other processes/workers so that they don't do the work twice and deadlock.

使用SELECT ... IN SHARE MODE或SELECT ... FOR UPDATE无效，因为该行仍然可见，然后将被所有其他工作程序死锁.

Using SELECT ... IN SHARE MODE or SELECT ... FOR UPDATE does not work as the row is still visible and will then be deadlocked by all other workers.

所以下一个想法是使用在另一个locked列上设置标志的事务:

So the next idea was using a transaction which sets a flag on an additional locked column:

BEGIN;
    // find row and select its id
    SET @id := (SELECT id
    FROM url
    WHERE done = false
        AND locked = false
    LIMIT 1);

    // lock row by setting locked flag to true
    UPDATE url
    SET locked = true
    WHERE id = @id;

    // give us some time to copy and paste this sql in another console :)
    SELECT SLEEP(10);

    // unlock row and mark as done
    UPDATE url
    SET
        locked = false,
        done = true
    WHERE id = @id;
COMMIT;

以下SQL通过 sql 程序包执行/golang.org"rel =" nofollow> Google Go .

Following SQL is executed over the sql package for Google Go.

此确实起作用，但是通过阻止所有其他事务来起作用.这意味着当我同时在两个sql控制台中执行上述sql事务时，我可以看到后面的事务如何等待第一个事务完成. 这种效果破坏了拥有多个工作程序的完整想法，因为最终事务将同步它们.

This does work as intended but it works by blocking all other transactions. This means when I execute the above sql transaction into two sql consoles at the same time I can see how the later transaction waits for the first to finish. This effect destroys the complete idea of having multiple worker as they will be synced by the transaction in the end.

如何在不阻止的情况下从其他交易中锁定"行?

推荐答案

您正在使用LOCK IN SHARE MODE，它可以锁定UPDATE或DELETE操作中的行，但允许读取该行.有关更多信息，请参考本文档.文档的简要内容如下.

You are using LOCK IN SHARE MODE which locks the row from UPDATE or DELETE operations, but allows the row to be read. Refer this documentation for more info. A brief from the documentation is below.

SELECT ...共享模式锁定在读取的任何行上设置共享模式锁定.其他会话可以读取行，但是在事务提交之前不能修改它们.

SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits.

对于索引记录，记录搜索遇到的问题，SELECT ... FOR UPDATE阻止其他会话执行SELECT ... LOCK IN SHARE MODE或读取某些事务隔离级别.

For index records the search encounters, SELECT ... FOR UPDATE blocks other sessions from doing SELECT ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels.

您的查询将同一行返回给所有进程/工作人员，他们只需要等待先前的锁释放，然后再将自己的锁放在该行上即可.要完成所需的锁定，请将SELECT ... LOCK IN SHARE MODE替换为SELECT ... FOR UPDATE.

Your query returns the same row to all processes/workers and they simply wait for the previous lock to release before placing their own lock on the row. To accomplish the kind of lock you need, replace your SELECT ... LOCK IN SHARE MODE with SELECT ... FOR UPDATE.

这篇关于如何在不阻塞的情况下锁定mysql中的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！