I'm seeing connection hang issue these days. many concurrent
connections are hanging on db. They basically do the same thing:
update different rows in same table. The sql itself should run very
fast as it's updating just one row based on an unique key. I though
it might be lock problem. The I list the locks ordered by query
start time(see list below). I'm confused why the oldest connection
are still waiting for the lock? anything else can hold that lock?
| 1580056836 |
ShareLock | f | update article set tm_update=$ |
2011-12-21 13:28:56.643105+08 | 00:24:51.599424 | 21020
select
pg_class.relname,pg_locks.transactionid, pg_locks.mode,
pg_locks.granted,
substr(pg_stat_activity.current_query,1,30),
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid
from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid order by query_start
limit 50;
relname | transactionid | mode |
granted | substr |
query_start | age | procpid
----------------------------+---------------+------------------+---------+--------------------------------+-------------------------------+-----------------+---------
article_title_hash_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
| 1580056836 | ShareLock |
f | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
| 1579897513 | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article_fid_author_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article_stage_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article_fid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article_cid_time_style_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
| | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article_tm_spider_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article_tm_update_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article_guid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article_url_hash | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article_rfid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article_url_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article_pkey | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020
article | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_cid_time_style_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_fid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_rfid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_pkey | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
| 1579921995 | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_url_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_title_hash_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_guid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_tm_update_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
| 1580056836 | ShareLock |
f | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_fid_author_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_tm_spider_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_stage_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_url_hash | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
| | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706
article_title_hash_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
| 1580056836 | ShareLock |
f | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_stage_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_fid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_pkey | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_url_hash | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_cid_time_style_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_fid_author_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_tm_update_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_rfid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_url_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
| | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_guid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_tm_spider_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
| 1579925267 | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892
article_title_hash_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:26.843451+08 | 00:23:21.399078 | 32700
article_fid_author_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:26.843451+08 | 00:23:21.399078 | 32700
(50 rows)