<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=GB18030">
</head>
<body bgcolor="#FFFFFF" text="#000000">
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?<br>
<font color="#ff0000"> | 1580056836 |
ShareLock | f | update article set tm_update=$ |
2011-12-21 13:28:56.643105+08 | 00:24:51.599424 | 21020</font><br>
<br>
select <br>
pg_class.relname,pg_locks.transactionid, pg_locks.mode,
pg_locks.granted, <br>
substr(pg_stat_activity.current_query,1,30),
pg_stat_activity.query_start, <br>
age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid <br>
from pg_stat_activity,pg_locks left <br>
outer join pg_class on (pg_locks.relation = pg_class.oid) <br>
where pg_locks.pid=pg_stat_activity.procpid order by query_start
limit 50;<br>
relname | transactionid | mode |
granted | substr |
query_start | age | procpid <br>
----------------------------+---------------+------------------+---------+--------------------------------+-------------------------------+-----------------+---------<br>
article_title_hash_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
| 1580056836 | ShareLock |
f | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
| 1579897513 | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article_fid_author_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article_stage_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article_fid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
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<br>
| | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article_tm_spider_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article_tm_update_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article_guid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article_url_hash | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article_rfid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article_url_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article_pkey | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 | 21020<br>
article | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
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<br>
article_fid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
article_rfid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
article_pkey | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
| 1579921995 | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
article_url_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
article_title_hash_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
article_guid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
article_tm_update_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
| 1580056836 | ShareLock |
f | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
article_fid_author_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
article_tm_spider_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
article_stage_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
article_url_hash | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
| | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 | 706<br>
article_title_hash_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
| 1580056836 | ShareLock |
f | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
article_stage_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
article_fid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
article_pkey | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
article_url_hash | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
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<br>
article | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
article_fid_author_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
article_tm_update_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
article_rfid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
article_url_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
| | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
article_guid_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
article_tm_spider_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
| 1579925267 | ExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 | 22892<br>
article_title_hash_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:26.843451+08 | 00:23:21.399078 | 32700<br>
article_fid_author_idx | | RowExclusiveLock |
t | update article set tm_update=$ | 2011-12-21
13:30:26.843451+08 | 00:23:21.399078 | 32700<br>
(50 rows)
</body>
</html>