Doctrine (postgresql) Pessimistic Locking - doesn't throw PessimisticLockException

Question

I try to use Pessimistic Locking with Doctrine ORM for PostgreSql. Doctrine and PostgreSql with default configurations (without any changes).

This is code example (Symfony Command).

$sleep - this is time in seconds

$manager = $this->getContainer()->get('mmi.manager.message');
$conn = $manager->em()->getConnection();

$manager->em()->getConnection()->beginTransaction();
try {
    $entity = $manager->repo()->find('cd7eb9e9', LockMode::PESSIMISTIC_WRITE);

    $entity->setState(EntityActionInterface::STATE_IN_PROGRESS);
    $manager->em()->persist($entity);
    $manager->em()->flush();

    $ts = (new \DateTime())->getTimestamp();
    $output->writeln("TS: {$ts}");

    if ($sleep) {
        $output->writeln("Sleep: {$sleep}");
        sleep($sleep);
    }

    $entity->setMessage([$ts]);
    $manager->em()->persist($entity);
    $manager->em()->flush();

    $conn->commit();
} catch (PessimisticLockException $ex) {
    var_dump(get_class($ex));

    $conn->rollBack();
    throw $ex;
} catch (\Exception $ex) {
    var_dump(get_class($ex));

    $conn->rollBack();
    throw $ex;
}

How tested

Run two command. First command runs with timeout 20 seconds. Second command runs without any timeout.

Expected result

Second command throws PessimisticLockException

Actual result

Second command waits for first transaction commit and then updates row.

Question

What should I do to make Doctrine throw PessimisticLockException if row is now locked?

Answer 1

Fo first: How working PESSIMISTIC_WRITE for PostgreSql platform

PESSIMISTIC_WRITE - this is query SELECT ... FOR UPDATE. This query lock selected row and other conections, which requested the same row, waitng for current connection finish it's work.

In my case i start two processes and second one waiting for finish first one. And this is correct behavior.

My mistake: i'm explore Doctrine source code and find PessimisticLockException class. So, i decide that Doctrine throw this exception when used pessimistic lock. But this class don't used anywhere in Doctrine.

So, how i resolved this issue.

My current implementation required nowait behavior for locked rows. And PostgreSql 9.5 has this feature - SKIP LOCKED. But Doctrine doesn't have implementation for this feature.

What we can do?

We can override doctrine postgresql platfrom class.

use Doctrine\DBAL\Platforms\PostgreSqlPlatform;

class PgSqlPlatform extends PostgreSqlPlatform
{
    /**
     * Returns the FOR UPDATE expression.
     *
     * @return string
     */
    public function getForUpdateSQL()
    {
        return 'FOR UPDATE SKIP LOCKED';
    }
}

Define it's as service

#app/config/services.yml
services:
    mmi.dbal.pgsql_platform:
        class: {Namespace}\PgSqlPlatform

And set tot doctrine config

#app/config/config.yml
doctrine:
    dbal:
        connections:
            mmi:
                driver:   pdo_pgsql
                host:     ...
                ...
                platform_service: 'mmi.dbal.pgsql_platform'

That's all. Now we can use Pessimistic lock without waiting.