Search code examples
sqlpostgresqllocking

PostgreSQL Locking Questions

I am trying to figure out how to lock an entire table from writing in Postgres however it doesn't seem to be working so I am assuming I am doing something wrong.

Table name is 'users' for example.

LOCK TABLE users IN EXCLUSIVE MODE;

When I check the view pg_locks it doesn't seem to be in there. I've tried other locking modes as well to no avail.

Other transactions are also capable of performing the LOCK function and do not block like I assumed they would.

In the psql tool (8.1) I simply get back LOCK TABLE.

Any help would be wonderful.

Solution

  • There is no LOCK TABLE in the SQL standard, which instead uses SET TRANSACTION to specify concurrency levels on transactions. You should be able to use LOCK in transactions like this one

    BEGIN WORK;
LOCK TABLE table_name IN ACCESS EXCLUSIVE MODE;
SELECT * FROM table_name WHERE id=10;
Update table_name SET field1=test WHERE id=10;
COMMIT WORK;

    I actually tested this on my db.