锁定和并发执行存储过程 [英] Locking and concurrent executions of a stored procedure

问题描述

我有一个存储过程，该过程由远程客户端持续执行.通过阅读文档，我给人的印象是，使用适当的锁定技术，我不需要从外部管理这些客户端，并且我可以自由地让它们尽可能频繁地并发运行.

I have a stored procedure that is executed by remote clients on an ongoing basis. From reading the docs I am under the impression that with the proper locking techniques I shouldn't need to externally manage these clients and I would be free to have them run as often and as concurrently as they like.

此过程将更新并插入到多个表中.该过程的概要如下:

This procedure updates and inserts to multiple tables. A skeleton outline of the procedure is below:

LOCK TABLE table1 IN EXCLUSIVE MODE;

LOOP
    UPDATE table1 SET "var1"="var1"+1, WHERE "var2"=var2;
    IF found THEN
        EXIT;
    END IF; 

    BEGIN
        INSERT INTO table1 ("col") VALUES (1) RETURNING "ID" INTO id;
        EXIT;
    EXCEPTION WHEN unique_violation THEN
        EXIT;
    END;
    EXIT;
END LOOP;

LOCK TABLE table2 IN EXCLUSIVE MODE;

LOOP
    BEGIN
        INSERT INTO table2 ("var3","var4") values (var3,var4);
        EXIT;
    EXCEPTION WHEN unique_violation THEN
        EXIT;
    END;
END LOOP;

此后还有第三张表，以与table1相同的方式锁定和更新/插入.事情按原样进行，但是Web应用程序也在同一表上执行一些缓慢的select语句，并且排他锁经常导致较大的延迟.我意识到排他锁定不是必需的，但是无数的锁定类型以及将其应用于过程中的哪些表会有点令人困惑，因此我选择了这种方法以使其尽快完成.

There's also a third table after this that is locked and updated/inserted in the same fashion as table1. Things are working as-is however a web application is also performing some slow select statements on the same tables and the exclusive locks cause large delays every so often. I realize exclusive locking should not be necessary however the myriad of locking types and which to apply to which tables in my procedure was a bit confusing so I went for this approach to get it going asap.

我有几个问题:

此过程的最佳锁定策略是什么?我想正确地执行此操作，请避免使用排他锁，以使Web应用程序能够在不干扰的情况下运行选择.

What is the best locking strategy for this procedure? I'd like to do it properly, avoid exclusive locks where I can to enable the web application to run selects without interference.

随着客户数量的增长，我将面临什么样的局限性?最好完全放弃这种方法，将所有客户端的数据推送到一个中央位置，然后仅从该位置运行过程，这会更好吗?

What kind of limitations will I face as the number of clients grows? Would it be better to abandon this approach entirely and have the data from all clients pushed to a central location and only run the procedure from there?

推荐答案

让数据库句柄为您锁定.PostgreSQL和所有其他数据库都有锁定代码，这些代码将锁定适当的行以根据需要进行修改.PostgreSQL使用称为多版本并发控制的东西，这实际上意味着读者永远不会阻止或阻止作者.

Let the database handle locking for you. PostgreSQL, as well as every other database, has locking code that will lock the appropriate rows for modification as needed. PostgreSQL uses something called Multi-Version Concurrency Control that effectively means readers will never block or be blocked by writers.

使用当前的解决方案，随着客户端数量的增加，锁定将增加，直到您的应用程序完全无法使用为止.再次，让数据库为您管理锁定-根据需要，它在解决锁定方面非常有效.

With your current solution, as the number of clients grow, you'll see locking increase until your application becomes completely unusable. Again, let the database manage locking for you - it is very effective at resolving locks as needed.

这篇关于锁定和并发执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！