Re: DB Locks

Jasbinder Bali wrote:
> Hello everyone,
>
> I had a concern about DB locks lately.
> If have a scenario where in a Cron job is running. This cron job updates a
> table and would take about 20 minutes to complete.

> In the meantime, if I query the same table using an application, will my
> application have to wait as the cron job has held a write lock on the table
> or the application gets the latest available snapshot of the data?

It depends on the commands involved and the resulting locking. Have a
look at:

http://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-TABLES

http://www.postgresql.org/docs/current/static/sql-lock.html

A ROW EXCLUSIVE lock will be taken out on the table by the UPDATE . That
blocks SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE and ACCESS EXCLUSIVE .
Given that, you cannot concurrently run:

CREATE INDEX
ALTER TABLE
DROP TABLE
TRUNCATE
REINDEX
CLUSTER
VACUUM FULL

... but anything else should be fine, at least according to the
documentation.

In addition, UPDATE will lock individual records that are updated (see
13.3.2), preventing a concurrent UPDATE or SELECT FOR UPDATE from
proceeding. A SELECT will work fine.

So, unless you are requesting explicit locks, I expect the only issue
you're likely to hit is that an update that attempts to alter a row your
big batch transaction affects will stall until the batch transaction
finishes.

I don't do much in the way of big batch updates, though, so I've never
had cause to personally confirm this - I can only make a suggestion
based on the documentation. Writing a test script or two might be a good
idea just to be really confident that you understand it all and that it
works how you expect.

--
Craig Ringer