pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcascading lock issue
2+ messages / 2 participants
[nested] [flat]
* cascading lock issue
@ 2024-03-27 11:33 James Pang <jamespang886@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: James Pang @ 2024-03-27 11:33 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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;
-[ 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
Thanks,
James
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: cascading lock issue
@ 2024-03-27 12:15 Laurenz Albe <laurenz.albe@cybertec.at>
parent: James Pang <jamespang886@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2024-03-27 12:15 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-03-27 12:15 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-27 11:33 cascading lock issue James Pang <jamespang886@gmail.com>
2024-03-27 12:15 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox