Add support for row-level locking (via FOR UPDATE SKIP LOCKED)

[milancermak]

I have a table that acts as a job queue. I want to read the first row where processed_at is null. There can be multiple workers reading from the same table. To ensure only a single worker is processing one job, I can use row-level locking. In postgres, I can do:

begin;

-- 1)
select * from job_queue where processed_at is null order by created_at limit 1 for update skip locked;
-- returns a job with job_id = 42

-- 💻 ⏳ 

-- 2)
update job_queue set processed_at = now() where job_id = 42;

commit;

In 1), it returns the data associated with the job, but still keeps the transaction open. If there is another worker running the same select, it would not get back a job with job_id = 42.

Between 1) and 2), I want to do some work on the server and call update only when that work is done. I didn't find a way how this is currently possible with Prisma (I've read the Transaction guide).

I imagine this is a significant feature to implement, but is that something on the roadmap?

[meotimdihia]

Yes, this feature is missing on Prisma that I can't completely replace Sequelize for Prisma.

https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/

[anonrig]

Any news on this issue? FOR UPDATE SKIP LOCKED is an important feature.

[rankjie]

I ran into a situation where I needed to gain an X lock at the start of a transaction with a find query since the default behavior from Prisma is to gain an S lock, which would cause deadlocks in race conditions.

I ended up with $queryRaw, hope there is an option for find methods to specify lockings, especially in transactions.