PostgreSQL advisory locks are not working with Doctrine's DBAL

Question

I'm experiencing a very strange behavior when trying to use advisory locks in Doctrine's DBAL.

I have a Symfony 2 application in which I want to obtain an advisory lock for some entity. I'm making the following query to obtain the lock:

SELECT pg_try_advisory_lock(83049, 5)

Via the following code in PHP:

/** @var Doctrine\DBAL\Connection  */
protected $connection;

public function lock()
{
    return $this->connection->fetchColumn(
        "SELECT pg_try_advisory_lock({$this->getTableOid()}, {$this->entity->getLockingId()})"
    );
}

I've created the following script to test the concurrency:

// Obtaining the lock.
$locker->lock();

// Doing something for ten seconds.
sleep(10); 

However, when I run it concurrently, it looks like every instance is successfully getting the lock. Also, after request is terminated, it looks like lock is automatically released, even when I've not called unlock().

Why it behaving that way?

Does doctrine use single connection for all requests?

Does doctrine releases the locks automatically after script is terminated?

Answer 1

13.3.4. Advisory Locks

PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use — it is up to the application to use them correctly. Advisory locks can be useful for locking strategies that are an awkward fit for the MVCC model. For example, a common use of advisory locks is to emulate pessimistic locking strategies typical of so-called "flat file" data management systems. While a flag stored in a table could be used for the same purpose, advisory locks are faster, avoid table bloat, and are automatically cleaned up by the server at the end of the session.

There are two ways to acquire an advisory lock in PostgreSQL: at session level or at transaction level. Once acquired at session level, an advisory lock is held until explicitly released or the session ends. Unlike standard lock requests, session-level advisory lock requests do not honor transaction semantics: a lock acquired during a transaction that is later rolled back will still be held following the rollback, and likewise an unlock is effective even if the calling transaction fails later. A lock can be acquired multiple times by its owning process; for each completed lock request there must be a corresponding unlock request before the lock is actually released. Transaction-level lock requests, on the other hand, behave more like regular lock requests: they are automatically released at the end of the transaction, and there is no explicit unlock operation. This behavior is often more convenient than the session-level behavior for short-term usage of an advisory lock. Session-level and transaction-level lock requests for the same advisory lock identifier will block each other in the expected way. If a session already holds a given advisory lock, additional requests by it will always succeed, even if other sessions are awaiting the lock; this statement is true regardless of whether the existing lock hold and new request are at session level or transaction level.

http://www.postgresql.org/docs/9.1/static/explicit-locking.html