Fail SELECT Query if table is locked in PostgreSQL-CodePudding

Is there a way to fail a select query if a table is locked for access instead of waiting for the lock to be released?

I would prefer resolving it with SQL but doing it at JDBC level in the code is also OK.

I cannot use LOCK_TIMEOUT because Postgres version is too old.

basically I would like this

SELECT * 
FROM SOME_TABLE

to fail immediately instead of waiting for this

BEGIN;
LOCK TABLE SOME_TABLE IN ACCESS EXCLUSIVE MODE;
SELECT pg_sleep(10);
COMMIT TRANSACTION;

EDIT: Version is unknown because the DBMS I'm using is actually GaussDB, PostghreSQL, but it basically behaves like Postgres since it's developed based on Postgres. So I don't know which version of Postgres my version corresponds to. SELECT VERSION() will give me Gauss version instead of Postgre version.

CodePudding user response：

As suggested in the comment earlier: it sounds like it's exactly what NOWAIT is for:

NOWAIT Specifies that LOCK TABLE should not wait for any conflicting locks to be released: if the specified lock(s) cannot be acquired immediately without waiting, the transaction is aborted.

So you should be able to replace this:

LOCK TABLE SOME_TABLE IN ACCESS EXCLUSIVE MODE;

With this:

LOCK TABLE SOME_TABLE IN ACCESS EXCLUSIVE MODE NOWAIT;

Without the need for pg_sleep() and it will fail if the lock isn't available immediately.

Guessing from the fact that you're already using 8.2's pg_sleep() but don't yet have the 9.3's lock_timeout, your version should be covered by NOWAIT support starting with 8.0.