While locking table in PostgreSQL, I am getting ERROR: LOCK TABLE can only be used in transaction blocks

Viewed 504

The error is

ERROR:  LOCK TABLE can only be used in transaction blocks
SQL state: 25P01

Why do I get that error, and what can I do to lock a table?

1 Answers

Locks in relational database systems are held until the end of the current transaction.

Now PostgreSQL uses autocommit mode, so if you don't start a transaction explicitly with BEGIN or START TRANSACTION, every statement will run in its own transaction.

An explicit table lock that is not part of an explicitly started multi-statement transaction is useless, because it would be gone at the end of the statement. But it could still harm concurrent transactions by blocking them, so it is forbidden.

Please reconsider before taking explicit table locks. In 99% of all cases they are unnecessary and a sign that the programmer didn't understand database concurrency techniques or didn't think hard enough. Explicit locks harm concurrency and can block autovacuum (sometimes with disastrous consequences).

Related