How does FOR NO KEY UPDATE SKIP LOCKED handle workers running on different machines?

[gmhawash]

1. I am a bit confused about how the queue#lock function works since the switch to the FOR NO KEY UPDATE SKIP LOCKED. When I look at the PostgreSQL manuals, it indicates that the row level locking will happen within a DB transaction; and when a SELECT FOR NO KEY UPDATE SKIP LOCKED finds a row within a DB transaction; any other DB sessions will skip that row when using the same FOR NO KEY ....

I don't see that QC does any DB transactions anywhere; so how does this work?

2. Why is there no ORDER BY id ASC in the query so that jobs are handled in FIFO manner?

Thanks

[radanskoric]

@gmhawash

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

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

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

[gmhawash]

Thanks @radanskoric for the explanation

[ukd1]

Closing as @radanskoric answered this really well imho.