64

I have a table called deposits

When a deposit is made, the table is locked, so the query looks something like:

SELECT * FROM deposits WHERE id=123 FOR UPDATE

I assume FOR UPDATE is locking the table so that we can manipulate it without another thread stomping on the data.

The problem occurs though, when other deposits are trying to get the lock for the table. What happens is, somewhere in between locking the table and calling psql_commit() something is failing and keeping the lock for a stupidly long amount of time. There are a couple of things I need help addressing:

  1. Subsequent queries trying to get the lock should fail, I have tried achieving this with NOWAIT but would prefer a timeout method (because it may be ok to wait, just not wait for a 'stupid amount of time')

  2. Ideally I would head this off at the pass, and have my initial query only hold the lock for a certain amount of time, is this possible with postgresql?

  3. Is there some other magic function I can tack onto the query (similar to NOWAIT) which will only wait for the lock for 4 seconds before failing?

  4. Due to the painfully monolithic spaghetti code nature of the code base, its not simply a matter of changing global configs, it kinda needs to be a per-query based solution

Thanks for your help guys, I will keep poking around but I haven't had much luck. Is this a non-existing function of psql, because I found this: http://www.postgresql.org/message-id/[email protected]

Improve this question
4
  • Define "stupidly long". Also: PostgreSQL version? Do you ever get any PostgreSQL deadlock errors in your error log? Commented Jan 7, 2014 at 4:11
  • stupidly long = 30 seconds | 9.2.6 is the version. Commented Jan 7, 2014 at 4:16
  • 7
    Just FYI, "Deadlock" has a specific meaning: Two transactions where each is waiting on a lock that is held by the other. PostgreSQL will detect this situation and abort one of the transactions. The situation you describe is not a deadlock, it's an indefinite lock wait. Commented Jan 7, 2014 at 4:28
  • 1
    Related: if you want to see lock_timeout setting, you can use SELECT CASE WHEN setting = '0' THEN 'deactivated' ELSE setting || unit END AS lock_timeout FROM pg_settings WHERE name = 'lock_timeout';. Commented Nov 8, 2019 at 1:18

1 Answer 1

Reset to default
122

I assume FOR UPDATE is locking the table so that we can manipulate it without another thread stomping on the data.

Nope. FOR UPDATE locks only those rows, so that another transaction that attempts to lock them (with FOR SHARE, FOR UPDATE, UPDATE or DELETE) blocks until your transaction commits or rolls back.

If you want a whole table lock that blocks inserts/updates/deletes you probably want LOCK TABLE ... IN EXCLUSIVE MODE.

  1. Subsequent queries trying to get the lock should fail, I have tried achieving this with NOWAIT but would prefer a timeout method (because it may be ok to wait, just not wait for a 'stupid amount of time')

    See the lock_timeout setting. This was added in 9.3 and is not available in older versions.

    Crude approximations for older versions can be achieved with statement_timeout, but that can lead to statements being cancelled unnecessarily. If statement_timeout is 1s and a statement waits 950ms on a lock, it might then get the lock and proceed, only to be immediately cancelled by a timeout. Not what you want.

    There's no query-level way to set lock_timeout, but you can and should just:

    SET LOCAL lock_timeout = '1s';

    after you BEGIN a transaction.

  2. Ideally I would head this off at the pass, and have my initial query only hold the lock for a certain amount of time, is this possible with postgresql?

    There is a statement timeout, but locks are held at transaction level. There's no transaction timeout feature.

    If you're running single-statement transactions you can just set a statement_timeout before running the statement to limit how long it can run for. This isn't quite the same thing as limiting how long it can hold a lock, though, because it might wait 900ms of an allowed 1s for the lock, only actually hold the lock for 100ms, then get cancelled by the timeout.

  3. Is there some other magic function I can tack onto the query (similar to NOWAIT) which will only wait for the lock for 4 seconds before failing?

    No. You must:

    BEGIN;
SET LOCAL lock_timeout = '4s';
SELECT ....;
COMMIT;

  4. Due to the painfully monolithic spaghetti code nature of the code base, its not simply a matter of changing global configs, it kinda needs to be a per-query based solution

    SET LOCAL is suitable, and preferred, for this.

    There's no way to do it in the text of the query, it must be a separate statement.

    The mailing list post you linked to is a proposal for an imaginary syntax that was never implemented (at least in a public PostgreSQL release) and does not exist.

In a situation like this you may want to consider "optimistic concurrency control", often called "optimistic locking". It gives you greater control over locking behaviour at the cost of increased rates of query repetition and the need for more application logic.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

One of the best answers I have seen on stack overflow - thanks heaps!
Update: Postgres 9.5 added SKIP LOCKED option to SELECT… FOR UPDATE along with the existing NOWAIT option. Documentation: 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.
@asm0dey It's a time valued field in millisecond units, they all accept time notation like that. See SELECT unit FROM pg_settings WHERE name = 'lock_timeout';
@BryanLarsen If you can put it into a concrete proposal with use cases, well thought out and with scripts illustrating the behaviour, you could post to pgsql-hackers. I don't think it'd be accepted as-is though. We'd want some way to mark a transaction as a "preferred victim" that should be terminated on lock timeout instead of the waiter.
I think it should emphasized that if FOR UPDATE is used, the rows will be locked until the end of transaction, not until those rows have been updated. In my experience, many developers get this wrong.
|

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.