PostgreSQL Locking Questions

Question

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.

Why do you want to lock the table?

Frank Heikens
– Frank Heikens

2011-02-18 20:14:24 +00:00
Commented Feb 18, 2011 at 20:14 — Frank Heikens
– Frank Heikens, Commented Feb 18, 2011 at 20:14

criticus · Accepted Answer · 2011-02-18 20:00:19Z

10

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.

answered Feb 18, 2011 at 20:00

criticus

1,6199 silver badges16 bronze badges

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

1 Comment

araqnid Over a year ago

note that access exclusive mode prevents any other process from even reading from the table, not just writing to it. You can use share row exclusive mode to lock the table specifically against other processes running updates or inserts on it.

araqnid · Accepted Answer · 2011-02-18 19:53:22Z

5

Bear in mind that "lock table" only lasts until the end of a transaction. So it is ineffective unless you have already issued a "begin" in psql.

(in 9.0 this gives an error: "LOCK TABLE can only be used in transaction blocks". 8.1 is very old)

answered Feb 18, 2011 at 19:53

araqnid

135k25 gold badges164 silver badges139 bronze badges

Comments

Jens · Accepted Answer · 2023-01-25 21:02:38Z

The lock is only active until the end of the current transaction and released when the transaction is committed (or rolled back).

Therefore, you have to embed the statement into a BEGIN and COMMIT/ROLLBACK block. After executing:

BEGIN;
LOCK TABLE users IN EXCLUSIVE MODE;

you could run the following query to see which locks are active on the users table at the moment:

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid WHERE relation = 'users'::regclass::oid;

The query should show the exclusive lock on the users table. After you perform a COMMIT and you re-run the above-mentioned query, the lock should not longer be present.

In addition, you could use a lock tracing tool like https://github.com/jnidzwetzki/pg-lock-tracer/ to get real-time insights into the locking activity of the PostgreSQL server. Using such lock tracing tools, you can see which locks are taken and released in real-time.

Collectives™ on Stack Overflow

PostgreSQL Locking Questions

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related