PostgreSQL - how to unlock table record AFTER shutdown

user_5141468 0
Viewed 15209

Sorry I looked for this everywhere but cannot find a working solution :/ I badly needed this for abnormal testing.

What I'm trying to do here is:

  1. Insert row in TABLE A
  2. Lock this record
  3. (At separate terminal) service postgresql-9.6 stop
  4. Wait a few moments
  5. (At separate terminal) service postgresql-9.6 start
  6. "try" to unlock the record by executing "COMMIT;" in the same terminal as #2.

How i did #2 is like this:

BEGIN;
SELECT * FROM TABLE A WHERE X=Y FOR UPDATE;

Problem is that once I did #6, this error shows up:

DB=# commit;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

So when I execute "COMMIT;" again, it only shows:

DB=# commit;
WARNING:  there is no transaction in progress
COMMIT

Now the record cannot be unlocked.

I've tried getting the PID of that locking thing, and then execute pg_terminate (or cancel), but it just doesn't work.

DB=# select pg_class.relname,pg_locks.* from pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation;
DB=# select pg_terminate_backend(2450);
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
DB=# select pg_cancel_backend(3417);
ERROR:  canceling statement due to user request

Please help. Does anyone have any ideas? :/ ..Or is this even possible?

My specs:

  • Postgresql-9.6
  • RedHat Linux
postgresql
1 Answers

There's a fundamental misunderstanding or three here. Lock state is not persistent.

When you lock a record (or table), the lock is associated with the transaction that took the lock. The transaction is part of the running PostgreSQL session, your connection to the server.

Locks are released at the end of transactions.

Transactions end:

  • On explicit COMMIT or ROLLBACK;
  • When a session disconnects without an explicit COMMIT of the open transaction, triggering an implied ROLLBACK;
  • When the server shuts down, terminating all active sessions, again triggering an implied ROLLBACK of all in-progress transactions.

Thus, you have released the lock you took at step 2 when you shut the server down at step 3. The transaction that acquired that lock no longer exists because its session was terminated by server shutdown.

If examine pg_locks you'll see how the locked row is present before restart and vanishes after restart.

user_3986700
Related