Implement FOR UPDATE SKIP LOCKED #6251

New Issue

admin · 2026-01-22T15:29:34+01:00

admin commented

2026-01-22 15:29:34 +01:00

Originally created by @BenMorel on GitHub (Jun 19, 2019).

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.

Originally created by @BenMorel on GitHub (Jun 19, 2019). ### 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: ```php $em->find($id, LockMode::PESSIMISTIC_WRITE | LockMode::SKIP_LOCKED); ``` I can open a PR if you agree with this.

admin commented

2026-01-22 15:29:35 +01:00

@Ocramius commented on GitHub (Jun 22, 2019):

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...

@Ocramius commented on GitHub (Jun 22, 2019): For that specific use-case, I use the RDBMS-specific syntax: ```sql 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...

admin commented

2026-01-22 15:29:35 +01:00

@BenMorel commented on GitHub (Jun 23, 2019):

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!

@BenMorel commented on GitHub (Jun 23, 2019): 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!

admin commented

2026-01-22 15:29:36 +01:00

@jusurb commented on GitHub (Nov 22, 2019):

would also love to have this

@jusurb commented on GitHub (Nov 22, 2019): would also love to have this

admin commented

2026-01-22 15:29:36 +01:00

@allan-simon commented on GitHub (Dec 17, 2020):

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 commented on GitHub (Dec 17, 2020): 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

admin commented

2026-01-22 15:29:36 +01:00

@allan-simon commented on GitHub (Dec 17, 2020):

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

@allan-simon commented on GitHub (Dec 17, 2020): also for reference I've seen this Stackoverflow question https://stackoverflow.com/questions/40586294/doctrine-postgresql-pessimistic-locking-doesnt-throw-pessimisticlockexcepti

admin commented

2026-01-22 15:29:36 +01:00

@adrianrudnik commented on GitHub (Mar 7, 2021):

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?

@adrianrudnik commented on GitHub (Mar 7, 2021): 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?

admin commented

2026-01-22 15:29:36 +01:00

@artworx commented on GitHub (Jun 2, 2021):

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.

@artworx commented on GitHub (Jun 2, 2021): 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.

admin commented

2026-01-22 15:29:37 +01:00

@adlpz commented on GitHub (Feb 22, 2024):

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

@adlpz commented on GitHub (Feb 22, 2024): This appears [to be live in DBAL's QueryBuilder](https://github.com/doctrine/dbal/pull/6191). Are there any plans to mirror the functionality in ORM?

admin commented

2026-01-22 15:29:38 +01:00

@greg0ire commented on GitHub (Feb 22, 2024):

@adlpz there is this comment

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

@greg0ire commented on GitHub (Feb 22, 2024): @adlpz there is [this comment](https://github.com/doctrine/orm/pull/11061#issue-1995605196) You can give it a try yourself if you want to speed things up.

admin referenced this issue

2026-01-22 16:05:50 +01:00

[PR #6251] Add missing group to DDC2660Test.php #9917

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6251