函数内的Postgres咨询锁允许并发执行

Question

我遇到了一个问题，我有一个函数，根据某些情况需要序列化访问。这似乎是使用咨询锁的好案例。然而，在相当大的负载下，我发现序列化访问并没有发生，我看到了对该函数的并发访问。

该函数的目的是为事件提供“库存控制”。也就是说，它旨在限制给定事件的并发购票，以使事件不被超售。这些是应用程序/数据库中仅使用的咨询锁。

我发现有时会有更多的门票比eventTicketMax值多。由于使用了咨询锁，这似乎不可能发生。当进行低容量测试（或手动引入延迟，如在获取锁后pg_sleep）时，一切都按预期工作。

CREATE OR REPLACE FUNCTION createTicket(
        userId int,
        eventId int,
        eventTicketMax int
    ) RETURNS integer AS $$
        DECLARE insertedId int;
        DECLARE numTickets int;
    BEGIN
            -- first get the event lock
            PERFORM pg_advisory_lock(eventId);

            -- make sure we aren't over ticket max
            numTickets := (SELECT count(*) FROM api_ticket
                WHERE event_id = eventId and status <> 'x');

            IF numTickets >= eventTicketMax THEN
                -- raise an exception if this puts us over the max
                -- and bail
                PERFORM pg_advisory_unlock(eventId);
                RAISE EXCEPTION 'Maximum entries number for this event has been reached.';
            END IF;

            -- create the ticket
            INSERT INTO api_ticket (
                user_id,
                event_id,
                created_ts
            )
            VALUES (
                userId,
                eventId,
                now()
            )
            RETURNING id INTO insertedId;

            -- update the ticket count
            UPDATE api_event SET ticket_count = numTickets + 1 WHERE id = eventId;

            -- release the event lock
            PERFORM pg_advisory_unlock(eventId);

        RETURN insertedId;
    END;
    $$ LANGUAGE plpgsql;

这是我的环境设置：

• Django 1.8.1 (使用CONN_MAX_AGE 300的django.db.backends.postgresql_psycopg2)
• PGBouncer 1.7.2 (会话模式)
• Amazon RDS上的Postgres 9.3.10

我尝试调整的其他变量：

• 将CONN_MAX_AGE设置为0
• 移除pgbouncer并直接连接到数据库

在我的测试中，我注意到，在某些超售事件中，门票是从不同的web服务器购买的，因此我认为没有关于共享会话的任何有趣的问题，但我不能确定。

- Brock Haywood

Answer 1

一旦执行PERFORM pg_advisory_unlock(eventId)，另一个会话即可获取该锁，但由于第一会话的INSERT尚未提交，因此不会计入第二个会话的COUNT(*)中，导致超预订。

如果采用咨询锁策略，则必须使用事务级别的咨询锁（pg_advisory_xact_lock），而不是会话级别的锁。这些锁在提交时自动释放。

- Daniel Vérité

---

是的，这正是问题所在。转换为事务级别锁已解决了该问题。谢谢！ - Brock Haywood

我有什么遗漏吗？如果隔离级别是“可重复读”或“串行化”，那么会话#2不是会在事务中等待并继续使用旧数据（=可重复读）直到获得锁吗？ - Mikko Rantalainen

@MikkoRantalainen：你说得对。我已经删除了“可重复读”这个参考，因为在重新阅读后，它似乎是不正确的（在PG中，默认的隔离级别是“读取已提交”），并且在这个问题中是不必要的。 - Daniel Vérité