Open BenMorel opened 5 years ago
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...
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!
would also love to have this
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
also for reference I've seen this Stackoverflow question https://stackoverflow.com/questions/40586294/doctrine-postgresql-pessimistic-locking-doesnt-throw-pessimisticlockexcepti
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?
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.
This appears to be live in DBAL's QueryBuilder. Are there any plans to mirror the functionality in ORM?
@adlpz there is this comment
You can give it a try yourself if you want to speed things up.
Feature Request
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:
I can open a PR if you agree with this.