How does `FOR NO KEY UPDATE SKIP LOCKED` handle workers running on different machines?
gmhawash opened this issue · 3 comments
- I am a bit confused about how the
queue#lock
function works since the switch to theFOR NO KEY UPDATE SKIP LOCKED
. When I look at the PostgreSQL manuals, it indicates that the row level locking will happenwithin a DB transaction
; and when a SELECTFOR NO KEY UPDATE SKIP LOCKED
finds a row within a DB transaction; any other DB sessions will skip that row when using the sameFOR NO KEY ...
.
I don't see that QC does any DB transactions anywhere; so how does this work?
- Why is there no
ORDER BY id ASC
in the query so that jobs are handled in FIFO manner?
Thanks
-
Your title question. :) It doesn't matter where the workers are running if you have just one database. The database is the place where
FOR NO KEY UPDATE SKIP LOCKED
runs its logic and that is on one server. If you have multiple master database's then that's a completely different story. -
Every SQL query is implicitly wrapped in a transaction by the database itself. It's equivalent to you wrapping each single query with
BEGIN ... COMMIT
. -
Because there is no FIFO guarantee. FIFO is meaningless anyway, unless you will run exactly one worker. Even if you added mechanisms to have workers pull them in FIFO manner, workers will process them at varying speeds to FIFO order could break there. And if you're not providing a hard guarantee
ORDER BY
is just additional load on the database. In reality, database will in most cases return it in the order of creation due to how the data is laid out internally but it's much faster if it doesn't have to guarantee it.ORDER BY
requires at least one sorting pass, even if the rows are in reality already sorted.
Thanks @radanskoric for the explanation
Closing as @radanskoric answered this really well imho.