Thread: Lock leaking out of Transaction?

Lock leaking out of Transaction?

From

James Sewell

Date:

15 January 2020, 02:42:06

Hi all,

I am trying to chase down a locking issue - it looks like a materialized view refresh is being held up by a relation lock which is held by an out of transaction session. My understanding was that this was not possible (see SQL output below).

The locking session is making progress (I can see query_start advancing), which makes it even more confusing.

Any advice?

James Sewell,

The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.

Re: Lock leaking out of Transaction?

From

Laurenz Albe

Date:

15 January 2020, 09:34:22

On Wed, 2020-01-15 at 10:42 +1100, James Sewell wrote:
> I am trying to chase down a locking issue - it looks like a materialized view refresh is being
> held up by a relation  lock which is held by an out of transaction session. My understanding was that
> this was not possible (see SQL output below).
> 
> The locking session is making progress (I can see query_start advancing), which makes it even more confusing.
> 
> Any advice?
> 
> # select * from pg_locks l join pg_stat_activity a on l.pid = a.pid where relation = 1438729::regclass;
> -[ RECORD 1 ]------+---------------------------------------------------------------------------------
> locktype | relation
> database | 16428
> relation | 1438729
> mode | ShareUpdateExclusiveLock
> granted | f
> fastpath | f
> pid | 88955
> wait_event_type | Lock
> wait_event | relation
> state | active
> query | autovacuum: VACUUM supply_nodes (to prevent wraparound)
> backend_type | autovacuum worker
> -[ RECORD 2 ]------+---------------------------------------------------------------------------------
> locktype | relation
> database | 16428
> relation | 1438729
> mode | ExclusiveLock
> granted | f
> pid | 6839
> wait_event_type | Lock
> wait_event | relation
> state | active
> query | REFRESH MATERIALIZED VIEW CONCURRENTLY supply_nodes ;
> backend_type | client backend
> -[ RECORD 3 ]------+---------------------------------------------------------------------------------
> locktype | relation
> database | 16428
> relation | 1438729
> mode | ExclusiveLock
> granted | t
> pid | 65447
> application_name | PostgreSQL JDBC Driver
> wait_event_type | Client
> wait_event | ClientRead
> state | idle
> query | COMMIT
> backend_type | client backend

I cannot explain that either; could it be shared memory corruption?

What I would try is

   SELECT pg_terminate_backend(65447);

and see if the session and its lock go away.

If that does not do the trick, I would restart PostgreSQL, which should get
rid of any possible memory corruption.

Then perhaps the anti-wraparoung autovacuum can succeed.
This autovacuum would also block you, but you should let it finish, since
it is an important system task.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com