2

Is there a way to issue a postgres update query that just does nothing if the row it wants to update is locked? Or perhaps waits a very short time before giving up, just for this query?

I have a table with an updated_at field which is used for Russian doll caching. Russian doll caching basically uses updated_at data in cache keys so you can invalid the cache by simply modifying the updated_at field. So for example if you have 2 tables:

books:
 id: int
 name: string
 updated_at: datetime


pages:
 id: int
 book_id: int
 text: text
 updated_at: datetime

You can now cache a book based on books.id and books.updated_at. If a page changes you change the updated_at for the parent book and that book's cache is invalidated.

The issue is if a large number of pages for the same book are being modified at the same time, you end up with a flood of queries to update updated_at for the same book record. As result, there is a ton of blocking while each update queries waits for a lock. We actually don't need all the updates to happen since it is just being used to invalid that cache. So if only one actual update happens per sec we are fine.

Any thoughts on how to achieve this?

3 Answers 3

1

set lock_timeout = {integer} should allow you to accomplish that. The {integer} would be the maximum number of milliseconds you want to wait.

set lock_timeout = 100;
update books
set id = 1;

Will wait 100 milliseconds to acquire the required locks for the update statement. The default value is (0), so use this to reset the lock timeout:

set lock_timeout = 0;
0
2

I think what you want is NOWAIT

To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table. Note that NOWAIT and SKIP LOCKED apply only to the row-level lock(s) — the required ROW SHARE table-level lock is still taken in the ordinary way (see Chapter 13). You can use LOCK with the NOWAIT option first, if you need to acquire the table-level lock without waiting.

That said, I'm not sure why you would want to do this. I've never had to do this. It sounds like a bad idea. It's quite normal to wait for a lock to update the rows. Retrying transactions is usually a longer-process. If you're afraid of issuing an update you don't want to a modified row, you don't have to worry about it. Dirty-writes are prevented.

UPDATE foo
SET baz = 1
WHERE bar = 2;

Will never update where a bar isn't 2. If it's waiting for a lock, after it gets the lock it'll check bar=2 and set baz=1 by rewriting the row.

I just can't see this as being more than trivia, or an extremely esoteric use-case.

I would keep it simple if you go the NOWAIT method becuase I don't think it matters anyway.

BEGIN;
  SELECT *
  FROM foo
  WHERE bar=2
  FOR UPDATE NOWAIT;

  UPDATE foo SET baz=1 WHERE bar=2;
END;

That whole transaction will rollback if it can't get the lock right away.

0
0

You may also try something like that , assuming id is unique and Postgres version is 9.5 or higher.

update books b
set updated_at = now() where  exists 
(select null from books a where b.id=a.id and b.id=1 for update of a skip locked) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.