Thread: [MASSMAIL]cascading lock issue
Hi,
below is a cascading lock and blocking issue, pid(4490 holding RowExclusiveLock and blocking pid(4732) on "alter table", when pid(4732) waiting, new coming select on the table got blocked too. since only RowExclusiveLock granted on relation, why the query select got blocked too ? just because there is another "alter table" that's sitting in the queue before this "select session" ? is it expected in PGv14?
details as below:
pid=4490
test=# begin;
BEGIN
stest=*# update test1 set relname=relname||'test' where oid<1000;
UPDATE 27
pid=4732
alter table test1 alter column relkind type char(10);
pid=5151
select count(*) from test1;
# select pid,query,state,wait_event,wait_event_type from pg_stat_activity;
pid query state wait_event wait_event_type backend_xid
4490 | update test1 set relname=relname||'test' where oid<1000; | idle in transaction | ClientRead | Client | 1053128912
4732 | alter table test1 alter column relkind type char(10); | active | relation | Lock | 1053128921
5151 | select count(*) from test1; | active | relation | Lock |
2024-03-27 11:05:53.239 UTC:[local]:postgres@test:[4732]:[4-1]:psqlLOG: statement: alter table test1 alter column relkind type char(10);
2024-03-27 11:05:54.240 UTC:[local]:postgres@test:[4732]:[5-1]:psqlLOG: process 4732 still waiting for AccessExclusiveLock on relation 46869 of database 16446 after 1000.037 ms
2024-03-27 11:05:54.240 UTC:[local]:postgres@test:[4732]:[6-1]:psqlDETAIL: Process holding the lock: 4490. Wait queue: 4732.
2024-03-27 11:05:54.240 UTC:[local]:postgres@test:[4732]:[7-1]:psqlSTATEMENT: alter table test1 alter column relkind type char(10);
2024-03-27 11:06:25.278 UTC:[local]:postgres@test:[5151]:[4-1]:psqlLOG: process 5151 still waiting for AccessShareLock on relation 46869 of database 16446 after 1000.032 ms
2024-03-27 11:06:25.278 UTC:[local]:postgres@test:[5151]:[5-1]:psqlDETAIL: Process holding the lock: 4490. Wait queue: 4732, 5151.
2024-03-27 11:06:25.278 UTC:[local]:postgres@test:[5151]:[6-1]:psqlSTATEMENT: select count(*) from test1;
test=# begin;
BEGIN
stest=*# update test1 set relname=relname||'test' where oid<1000;
UPDATE 27
pid=4732
alter table test1 alter column relkind type char(10);
pid=5151
select count(*) from test1;
# select pid,query,state,wait_event,wait_event_type from pg_stat_activity;
pid query state wait_event wait_event_type backend_xid
4490 | update test1 set relname=relname||'test' where oid<1000; | idle in transaction | ClientRead | Client | 1053128912
4732 | alter table test1 alter column relkind type char(10); | active | relation | Lock | 1053128921
5151 | select count(*) from test1; | active | relation | Lock |
2024-03-27 11:05:53.239 UTC:[local]:postgres@test:[4732]:[4-1]:psqlLOG: statement: alter table test1 alter column relkind type char(10);
2024-03-27 11:05:54.240 UTC:[local]:postgres@test:[4732]:[5-1]:psqlLOG: process 4732 still waiting for AccessExclusiveLock on relation 46869 of database 16446 after 1000.037 ms
2024-03-27 11:05:54.240 UTC:[local]:postgres@test:[4732]:[6-1]:psqlDETAIL: Process holding the lock: 4490. Wait queue: 4732.
2024-03-27 11:05:54.240 UTC:[local]:postgres@test:[4732]:[7-1]:psqlSTATEMENT: alter table test1 alter column relkind type char(10);
2024-03-27 11:06:25.278 UTC:[local]:postgres@test:[5151]:[4-1]:psqlLOG: process 5151 still waiting for AccessShareLock on relation 46869 of database 16446 after 1000.032 ms
2024-03-27 11:06:25.278 UTC:[local]:postgres@test:[5151]:[5-1]:psqlDETAIL: Process holding the lock: 4490. Wait queue: 4732, 5151.
2024-03-27 11:06:25.278 UTC:[local]:postgres@test:[5151]:[6-1]:psqlSTATEMENT: select count(*) from test1;
-[ RECORD 4 ]------+-----------------
locktype | relation
database | 16446
relation | 46869
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 4/7
pid | 4490
mode | RowExclusiveLock
granted | t
fastpath | f
waitstart |
-[ RECORD 2 ]------+------------------------------
locktype | relation
database | 16446
relation | 46869
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 5/52
pid | 4732
mode | AccessExclusiveLock
granted | f
fastpath | f
waitstart | 2024-03-27 11:05:53.240797+00
-[ RECORD 3 ]------+------------------------------
locktype | transactionid
database |
relation |
page |
tuple |
virtualxid |
transactionid | 1053128921
classid |
objid |
objsubid |
virtualtransaction | 5/52
pid | 4732
mode | ExclusiveLock
granted | t
fastpath | f
waitstart |
-[ RECORD 2 ]------+------------------------------
locktype | relation
database | 16446
relation | 46869
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 6/90
pid | 5151
mode | AccessShareLock
granted | f
fastpath | f
waitstart | 2024-03-27 11:06:24.278703+00
locktype | relation
database | 16446
relation | 46869
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 4/7
pid | 4490
mode | RowExclusiveLock
granted | t
fastpath | f
waitstart |
-[ RECORD 2 ]------+------------------------------
locktype | relation
database | 16446
relation | 46869
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 5/52
pid | 4732
mode | AccessExclusiveLock
granted | f
fastpath | f
waitstart | 2024-03-27 11:05:53.240797+00
-[ RECORD 3 ]------+------------------------------
locktype | transactionid
database |
relation |
page |
tuple |
virtualxid |
transactionid | 1053128921
classid |
objid |
objsubid |
virtualtransaction | 5/52
pid | 4732
mode | ExclusiveLock
granted | t
fastpath | f
waitstart |
-[ RECORD 2 ]------+------------------------------
locktype | relation
database | 16446
relation | 46869
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 6/90
pid | 5151
mode | AccessShareLock
granted | f
fastpath | f
waitstart | 2024-03-27 11:06:24.278703+00
Thanks,
James
On Wed, 2024-03-27 at 19:33 +0800, James Pang wrote: > below is a cascading lock and blocking issue [...], > new coming select on the table got blocked too. since only RowExclusiveLock > granted on relation, why the query select got blocked too ? just because > there is another "alter table" that's sitting in the queue before this > "select session" ? is it expected in PGv14? Yes, it must be th ACCESS EXCLUSIVE lock that is waiting in line and blocking everything else (locks have to queue). Don't run ALTER TABLE when you have long running transactions/queries. Yours, Laurenz Albe