vicly/postgres_skip_locked.md

Created March 10, 2019 22:10

Select an option

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/vicly/6a80fa080a5ba424040a6d4eb7886900.js"></script>
Save vicly/6a80fa080a5ba424040a6d4eb7886900 to your computer and use it in GitHub Desktop.

[Postgres SKIP LOCKED] #SQL #Postgres

Raw

DB Client 1

-- row 2 is locked
SELECT * FROM t_demo WHERE id=2 FOR UPDATE;

DB Client 2: BAD performace

SELECT * FROM my_table WHERE id=2 OR id=3 FOR UPDATE;

-- waiting until the client 1 releases the lock on row 2.

DB Client 3: GOOD performance

SELECT * FROM my_table WHERE id=2 OR id=3 FOR UPDATE SKIP LOCKED

-- No waiting, just skip the locked row 2
id
----
3

Select an option