python - Django atomic select for update 不为递归调用锁定表？

Question

我有一个表，它是通过以下命令在 PostgreSQL 中创建和填充的:

CREATE TABLE my_lock (
    id integer,
    CONSTRAINT id_pkey PRIMARY KEY (id)
) ;

INSERT INTO my_lock VALUES (1) ;
INSERT INTO my_lock VALUES (2) ;

此表由以下 Django 模型表示

from django.db import models
from django.db import transaction

class MyLock(models.Model):
    class Meta(object):
        db_table = 'my_lock'

接下来，我有以下方法:

from contextlib import contextmanager

@contextmanager
def acquire_lock():
    with transaction.atomic():
        lock = MyLock.objects.select_for_update().filter(id=1).first()
        yield lock


def first_method():
    print "In first method"
    with acquire_lock():
        print "Lock acquired in first_method()"
        second_method()


def second_method():
    print "In second method"
    first_method()

acquire_lock() 方法是一个 Python 生成器，它在事务中运行一个SELECT FOR UPDATE 查询。这应该获取一个锁在 id = 1 的行上，并且由于调用 yield lock 时事务未完成，因此继续持有该锁。

因此，如果我们调用 first_method()，则应该打印以下输出:

In first method
Lock acquired in first_method()
In second method
In first method

但是，实际上调用first_method() 时，会打印以下内容:

In first method
Lock acquired in first_method()
In second method
In first method
Lock acquired in first_method()
In second method
In first method
Lock acquired in first_method()
In second method
In first method
Lock acquired in first_method()
In second method

(这一直持续到 RuntimeError: maximum recursion depth exceeded)

我在这里遗漏了一些东西。这怎么会发生？如何多次获取 PostgreSQL 中的行锁？

编辑:

如果我将 first_method() 更改为:

def first_method():
    print "In first method"
    with acquire_lock():
        print "Lock acquired in first_method()"
        i = 1
        while True:
            i = i + 1
            i = i - 1

并且，现在从两个不同的终端(或 shell)调用 first_method()，其中一个打印以下内容:

In first method
Lock acquired in first_method()

第二个打印如下:

In first method

因此，锁在这种情况下有效，但不会递归地起作用。

Answer 1

这就是锁的工作原理。在 Row level locks :

Note that a transaction can hold conflicting locks on the same row, even in different subtransactions;

您的代码在单个事务中运行。 Postgres 中的锁旨在防止与其他 事务发生冲突。因此，单个事务可以多次获取同一个锁，但只要当前事务持有该锁，其他事务就无法获取该锁。