Обсуждение: Lock leaking out of Transaction?
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?
# 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
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 3/26588281
pid | 88955
mode | ShareUpdateExclusiveLock
granted | f
fastpath | f
datid | 16428
datname | monitoring
pid | 88955
usesysid |
usename |
application_name |
client_addr |
client_hostname |
client_port |
backend_start | 14-JAN-20 11:50:25.139819 +11:00
xact_start | 14-JAN-20 16:27:40.534726 +11:00
query_start | 14-JAN-20 16:27:40.534726 +11:00
state_change | 14-JAN-20 16:27:40.534726 +11:00
wait_event_type | Lock
wait_event | relation
state | active
backend_xid |
backend_xmin | 1655752595
query | autovacuum: VACUUM supply_nodes (to prevent wraparound)
backend_type | autovacuum worker
-[ RECORD 2 ]------+---------------------------------------------------------------------------------
locktype | relation
database | 16428
relation | 1438729
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 254/8624453
pid | 6839
mode | ExclusiveLock
granted | f
fastpath | f
datid | 16428
datname | monitoring
pid | 6839
usesysid | 10
usename | postgres
application_name | psql.bin
client_addr |
client_hostname |
client_port | -1
backend_start | 14-JAN-20 17:02:53.860451 +11:00
xact_start | 14-JAN-20 18:01:49.211728 +11:00
query_start | 14-JAN-20 18:01:49.211728 +11:00
state_change | 14-JAN-20 18:01:49.21173 +11:00
wait_event_type | Lock
wait_event | relation
state | active
backend_xid |
backend_xmin | 1689815577
query | REFRESH MATERIALIZED VIEW CONCURRENTLY supply_nodes ;
backend_type | client backend
-[ RECORD 3 ]------+---------------------------------------------------------------------------------
locktype | relation
database | 16428
relation | 1438729
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 355/0
pid | 65447
mode | ExclusiveLock
granted | t
fastpath | f
datid | 16428
datname | monitoring
pid | 65447
usesysid | 169436
usename | f_process
application_name | PostgreSQL JDBC Driver
client_addr | 10.153.154.36
client_hostname |
client_port | 40899
backend_start | 14-JAN-20 18:00:02.784211 +11:00
xact_start |
query_start | 14-JAN-20 18:02:26.831979 +11:00
state_change | 14-JAN-20 18:02:26.833197 +11:00
wait_event_type | Client
wait_event | ClientRead
state | idle
backend_xid |
backend_xmin |
query | COMMIT
backend_type | client backend
-[ RECORD 1 ]------+---------------------------------------------------------------------------------
locktype | relation
database | 16428
relation | 1438729
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 3/26588281
pid | 88955
mode | ShareUpdateExclusiveLock
granted | f
fastpath | f
datid | 16428
datname | monitoring
pid | 88955
usesysid |
usename |
application_name |
client_addr |
client_hostname |
client_port |
backend_start | 14-JAN-20 11:50:25.139819 +11:00
xact_start | 14-JAN-20 16:27:40.534726 +11:00
query_start | 14-JAN-20 16:27:40.534726 +11:00
state_change | 14-JAN-20 16:27:40.534726 +11:00
wait_event_type | Lock
wait_event | relation
state | active
backend_xid |
backend_xmin | 1655752595
query | autovacuum: VACUUM supply_nodes (to prevent wraparound)
backend_type | autovacuum worker
-[ RECORD 2 ]------+---------------------------------------------------------------------------------
locktype | relation
database | 16428
relation | 1438729
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 254/8624453
pid | 6839
mode | ExclusiveLock
granted | f
fastpath | f
datid | 16428
datname | monitoring
pid | 6839
usesysid | 10
usename | postgres
application_name | psql.bin
client_addr |
client_hostname |
client_port | -1
backend_start | 14-JAN-20 17:02:53.860451 +11:00
xact_start | 14-JAN-20 18:01:49.211728 +11:00
query_start | 14-JAN-20 18:01:49.211728 +11:00
state_change | 14-JAN-20 18:01:49.21173 +11:00
wait_event_type | Lock
wait_event | relation
state | active
backend_xid |
backend_xmin | 1689815577
query | REFRESH MATERIALIZED VIEW CONCURRENTLY supply_nodes ;
backend_type | client backend
-[ RECORD 3 ]------+---------------------------------------------------------------------------------
locktype | relation
database | 16428
relation | 1438729
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 355/0
pid | 65447
mode | ExclusiveLock
granted | t
fastpath | f
datid | 16428
datname | monitoring
pid | 65447
usesysid | 169436
usename | f_process
application_name | PostgreSQL JDBC Driver
client_addr | 10.153.154.36
client_hostname |
client_port | 40899
backend_start | 14-JAN-20 18:00:02.784211 +11:00
xact_start |
query_start | 14-JAN-20 18:02:26.831979 +11:00
state_change | 14-JAN-20 18:02:26.833197 +11:00
wait_event_type | Client
wait_event | ClientRead
state | idle
backend_xid |
backend_xmin |
query | COMMIT
backend_type | client backend
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.
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