Table lock in PostgreSQL

Question

I'm working on a application which should get data from a one table and record a result of processing in another table. All this in more that one thread and on a few computers. So I need to use some synchronize mechanism for that. As far as I need to use more that one computers I have to use some sync mechanism in DB (in my case it's PostgresSQL) instead of use lock in code. I know that postgres provide some lock on table, but i didn't find any docs how to setup it.

Possible some standard solution already exist for that?

You mean there is more than one copy of the database? One in each computer? — Clodoaldo Neto, Jul 04 '14 at 08:43
I think you will need to show the processing logic and how is it distributed. But I may be wrong. — Clodoaldo Neto, Jul 04 '14 at 08:57

score 1 · Accepted Answer · edited Jul 04 '14 at 09:39

Yes, Postgres provides locking mechanism at table level. You can lock the table using SQL and then unlock it when your job is done.

See the explicit locking chapter of the manual for details.

BUT considering your case, you can achieve the same using some simple mechanism like, adding one more column to table - locking_status boolean. When one machine uses it, you can make it TRUE and then toggle as per your requirement. If you have more than 1 tables, then however, locking of table is only option.

You also need to take care of highly scalable multi-threaded program because, it is possible that, one application has locked the table and then that machine may go down. In that case, your table remains locked and application simply become non-responsive. (depends however on how you have handled this state). In this case, some sort of expire mechanism may help which will unlock the table after particular amount of time.

Hope this helps.

score 1 · Answer 2 · edited May 23 '17 at 12:11

<clippy>
It looks like you're trying to write a task queue or message queue. These are really hard to get right. Would you like me to suggest an existing well-tested implementation for you to use instead?
</clippy>

More seriously: Table-level locks are done with LOCK TABLE statements; see Ved's answer. There are also row-level locks (SELECT ... FOR [KEY] UPDATE|SHARE) and optimistic predicate locking in SERIALIZABLE isolation. You can also implement optimistic concurrency control (see Wikipedia).

However, it's really hard to actually make a concurrent task or message queue. Most solutions you come up with will land up actually being serialized so only one task can run at once, or will fail to handle tasks that abort/crash, etc.

See:

and look into tools like Celery, ZeroMQ, ActiveMQ, RabbitMQ, Octobot, etc. See http://queues.io/

Yep. My task is trying to write task queue or something like that. — Vartlok, Jul 07 '14 at 11:44
@Vartlok Then don't! Use one of the existing tested implimentations. — Craig Ringer, Jul 07 '14 at 12:10

Table lock in PostgreSQL

2 Answers2

Linked