Implement FOR UPDATE SKIP LOCKED

[BenMorel]

Feature Request

Q	A
New Feature	yes
RFC	no
BC Break	no

Summary

As far as I can see, there is currently no way to perform a SELECT FOR UPDATE SKIP LOCKED (MySQL, PostgreSQL) with Doctrine.

This is really useful, for example when lauching several concurrent workers picking jobs from a single table, as it effectively prevents two workers from getting the same job, and automatically and immediately makes the job available again in case the transaction is aborted.

Would you be willing to add this feature? This could be implemented this way:

$em->find($id, LockMode::PESSIMISTIC_WRITE | LockMode::SKIP_LOCKED);

I can open a PR if you agree with this.

[Ocramius]

For that specific use-case, I use the RDBMS-specific syntax:

UPDATE
  bernard_messages
SET
  visible = FALSE
WHERE
  id IN (
    SELECT
      id
    FROM
      bernard_messages
    WHERE
      queue = :queue
      AND
      visible = :visible
    ORDER BY
      id ASC
    LIMIT 1
    FOR UPDATE
  )
RETURNING
  id,
  message

Would this be sufficient for your use-case? It seems very much aimed at queue management...

[BenMorel]

Hi Marco, I can already do this with native SQL, my request is to be able to perform such a SELECT using the ORM.

Also, your syntax above is Postgres-only (not supported on MySQL), and, at least on MySQL, it kills all concurrency by using FOR UPDATE: a concurrent query would have to wait for this one to release the lock, so it's not a replacement for FOR UPDATE SKIP LOCKED I'm afraid!

[Gamesh]

would also love to have this

[allan-simon]

I have a use case which is not queue related , I have a set of payment for which I need to pull the 3rd-party API to know about their last status, and in case of change of status, to notify the end users . I need to rotate over the list of payments , and I don't want to have a "singleton" worker to do this. (much better to have N workers that can scale up and down ) . And for this the FOR UPDATE SKIP LOCKED is very elegant solution to the concurrency problem

[allan-simon]

also for reference I've seen this Stackoverflow question https://stackoverflow.com/questions/40586294/doctrine-postgresql-pessimistic-locking-doesnt-throw-pessimisticlockexcepti

[adrianrudnik]

Same here, wanted to use it for assigning a ticket to "the next best slot available" while working with pessimistic locks. Both MySQL and PostgreSQL seem to support it, but I can't figuire out how to implement it, even with hints. Is there no node to walk in an SqlWalker/AST to append something at the very end?

[artworx]

In my use case, we have multiple short transactions waiting for a big batch. I need to add SKIP LOCKED to the small transactions to avoid waiting for a long time.

Currently, I'm doing this with native queries, and it's a huge PITA.

[adlpz]

This appears to be live in DBAL's QueryBuilder. Are there any plans to mirror the functionality in ORM?

[greg0ire]

@adlpz there is this comment

You can give it a try yourself if you want to speed things up.