pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedlock problem
10+ messages / 4 participants
[nested] [flat]
* lock problem
@ 2011-12-21 07:55 Rural Hunter <ruralhunter@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rural Hunter @ 2011-12-21 07:55 UTC (permalink / raw)
To: pgsql-admin
<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.�0�2 The I list the locks ordered by query
start time(see list below).�0�2 I'm confused why the oldest connection
are still waiting for the lock? anything else can hold that lock?<br>
�0�2�0�2<font color="#ff0000">�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2 1580056836 |
ShareLock�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | f�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ |
2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020</font><br>
<br>
select <br>
�0�2�0�2�0�2�0�2 pg_class.relname,pg_locks.transactionid, pg_locks.mode,
pg_locks.granted,�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 �0�2<br>
�0�2�0�2�0�2�0�2 substr(pg_stat_activity.current_query,1,30),
pg_stat_activity.query_start,�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 �0�2<br>
�0�2�0�2�0�2�0�2 age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid <br>
�0�2�0�2 from pg_stat_activity,pg_locks left <br>
�0�2�0�2�0�2�0�2 outer join pg_class on (pg_locks.relation = pg_class.oid) �0�2<br>
�0�2�0�2 where pg_locks.pid=pg_stat_activity.procpid order by query_start
limit 50;<br>
�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 relname�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | transactionid |�0�2�0�2�0�2�0�2�0�2�0�2 mode�0�2�0�2�0�2�0�2�0�2�0�2 |
granted |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 substr�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2
query_start�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2 age�0�2�0�2�0�2�0�2�0�2�0�2 | procpid <br>
----------------------------+---------------+------------------+---------+--------------------------------+-------------------------------+-----------------+---------<br>
�0�2article_title_hash_idx�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2 1580056836 | ShareLock�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |
f�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2 1579897513 | ExclusiveLock�0�2�0�2�0�2 |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article_fid_author_idx�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article_stage_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article_fid_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article_cid_time_style_idx |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | ExclusiveLock�0�2�0�2�0�2 |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article_tm_spider_idx�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article_tm_update_idx�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article_guid_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article_url_hash�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article_rfid_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article_url_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article_pkey�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:28:56.643105+08 | 00:24:51.599424 |�0�2�0�2 21020<br>
�0�2article�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_cid_time_style_idx |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_fid_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_rfid_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_pkey�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2 1579921995 | ExclusiveLock�0�2�0�2�0�2 |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_url_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_title_hash_idx�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_guid_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_tm_update_idx�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2 1580056836 | ShareLock�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |
f�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_fid_author_idx�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_tm_spider_idx�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_stage_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_url_hash�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | ExclusiveLock�0�2�0�2�0�2 |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:01.947787+08 | 00:23:46.294742 |�0�2�0�2�0�2�0�2 706<br>
�0�2article_title_hash_idx�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2 1580056836 | ShareLock�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |
f�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_stage_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_fid_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_pkey�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_url_hash�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_cid_time_style_idx |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_fid_author_idx�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_tm_update_idx�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_rfid_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_url_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | ExclusiveLock�0�2�0�2�0�2 |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_guid_idx�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_tm_spider_idx�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2 1579925267 | ExclusiveLock�0�2�0�2�0�2 |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:11.735228+08 | 00:23:36.507301 |�0�2�0�2 22892<br>
�0�2article_title_hash_idx�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:26.843451+08 | 00:23:21.399078 |�0�2�0�2 32700<br>
�0�2article_fid_author_idx�0�2�0�2�0�2�0�2 |�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2 | RowExclusiveLock |
t�0�2�0�2�0�2�0�2�0�2�0�2 | update article set tm_update=$ | 2011-12-21
13:30:26.843451+08 | 00:23:21.399078 |�0�2�0�2 32700<br>
(50 rows)
</body>
</html>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: lock problem
@ 2011-12-21 13:28 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-21 13:51 ` Re: lock problem Bèrto ëd Sèra <berto.d.sera@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Kevin Grittner @ 2011-12-21 13:28 UTC (permalink / raw)
To: ruralhunter@gmail.com; pgsql-admin
Rural Hunter wrote:
> 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.
> update article set tm_update=$
I don't see a WHERE clause, so it looks like you're updating the
whole table each time. Once a row is updated by one of the
transactions, others will block on attempts to update the same row
until the first transaction commits.
-Kevin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: lock problem
2011-12-21 13:28 Re: lock problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-12-21 13:51 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-21 14:41 ` Re: lock problem Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bèrto ëd Sèra @ 2011-12-21 13:51 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: ruralhunter@gmail.com; pgsql-admin
Hi!
> I don't see a WHERE clause, so it looks like you're updating the
> whole table each time.
it's got a substr(pg_stat_activity.current_query,1,30) in it, so we shall
hardly see anything about the WHERE clause, but we'd really need to have
more info about it.
Bèrto
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: lock problem
2011-12-21 13:28 Re: lock problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-21 13:51 ` Re: lock problem Bèrto ëd Sèra <berto.d.sera@gmail.com>
@ 2011-12-21 14:41 ` Rural Hunter <ruralhunter@gmail.com>
2011-12-21 15:25 ` Re: lock problem Jerry Sievers <gsievers19@comcast.net>
0 siblings, 1 reply; 10+ messages in thread
From: Rural Hunter @ 2011-12-21 14:41 UTC (permalink / raw)
To: Bèrto ëd Sèra <berto.d.sera@gmail.com>; +Cc: pgsql-admin@postgresql.org, Kevin Grittner <kevin.grittner@wicourts.gov>
yes, it's truncated. the full sql is like this:
"update article set
tm_update=$1,rply_cnt=$2,read_cnt=$3,tm_last_rply=$4 where
title_hash=$5"
the title_hash is unique.
I dig another case more and found something interesting. it's actually
waiting for a lock of type transactionid. I ran the query below 3 times
very quickly and each time it showed a different lock holder.
db=# select pl1.*,pl2.pid,pa.query_start,pa.waiting,pa.current_query
from pg_locks pl1 left join pg_locks pl2 on
pl1.transactionid=pl2.transactionid and pl2.granted
left join pg_stat_activity pa on pl2.pid=pa.procpid where pl1.pid=6053
and not pl1.granted;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid
| mode | granted | pid |
query_start | waiting |
current_query
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------+---------+------+---------
----------------------+---------+---------------------------------------------------------------------------------------------
transactionid | | | | | |
1586721800 | | | | 238/39230 | 6053 |
ShareLock | f | 3026 | 2011-12-
21 22:24:20.027493+08 | t | update article set
tm_update=$1,rply_cnt=$2,read_cnt=$3,tm_last_rply=$4 where title_hash=$5
(1 row)
db=# select pl1.*,pl2.pid,pa.query_start,pa.waiting,pa.current_query
from pg_locks pl1 left join pg_locks pl2 on
pl1.transactionid=pl2.transactionid and pl2.granted
left join pg_stat_activity pa on pl2.pid=pa.procpid where pl1.pid=6053
and not pl1.granted;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid
| mode | granted | pid |
query_start | waiting |
current_query
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------+---------+------+---------
----------------------+---------+---------------------------------------------------------------------------------------------
transactionid | | | | | |
1586739901 | | | | 238/39230 | 6053 |
ShareLock | f | 3254 | 2011-12-
21 22:25:15.133554+08 | t | update article set
tm_update=$1,rply_cnt=$2,read_cnt=$3,tm_last_rply=$4 where title_hash=$5
(1 row)
db=# select pl1.*,pl2.pid,pa.query_start,pa.waiting,pa.current_query
from pg_locks pl1 left join pg_locks pl2 on
pl1.transactionid=pl2.transactionid and pl2.granted
left join pg_stat_activity pa on pl2.pid=pa.procpid where pl1.pid=6053
and not pl1.granted;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid
| mode | granted | pid |
query_start | waiting |
current_query
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------+---------+------+---------
----------------------+---------+---------------------------------------------------------------------------------------------
transactionid | | | | | |
1586626482 | | | | 238/39230 | 6053 |
ShareLock | f | 1518 | 2011-12-
21 22:19:28.880025+08 | t | update article set
tm_update=$1,rply_cnt=$2,read_cnt=$3,tm_last_rply=$4 where title_hash=$5
(1 row)
I found the description of transactionid type here:
http://archives.postgresql.org/pgsql-novice/2010-05/msg00066.php
Currently, the only case where anything will try to take a sharelock on
transaction id is when it is blocking on a row-level lock as a result of
trying to modify or delete or SELECT FOR UPDATE/FOR SHARE a row that the
other transaction already modified or deleted or selected FOR
UPDATE/SHARE.
I'm pretty sure those queries are updating different rows each. why
they are waiting for row lock for each other?
Another question is: query A waiting for B, then waiting for C, then
waiting for D. I checked the query start time, A is much earlier than
B/C/D. Why A still couldn't get the lock while looks B/C/D seems have
gotten the lock even ABCD are all similar transaction?
于2011年12月21日 21:51:14,Bèrto ëd Sèra写到:
> Hi!
>
> I don't see a WHERE clause, so it looks like you're updating the
> whole table each time.
>
>
> it's got a substr(pg_stat_activity.current_query,1,30) in it, so we
> shall hardly see anything about the WHERE clause, but we'd really need
> to have more info about it.
>
> Bèrto
> --
> ==============================
> If Pac-Man had affected us as kids, we'd all be running around in a
> darkened room munching pills and listening to repetitive music.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: lock problem
2011-12-21 13:28 Re: lock problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-21 13:51 ` Re: lock problem Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-21 14:41 ` Re: lock problem Rural Hunter <ruralhunter@gmail.com>
@ 2011-12-21 15:25 ` Jerry Sievers <gsievers19@comcast.net>
2011-12-21 15:40 ` Re: lock problem Bèrto ëd Sèra <berto.d.sera@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jerry Sievers @ 2011-12-21 15:25 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: Bèrto ëd Sèra <berto.d.sera@gmail.com>; pgsql-admin@postgresql.org, Kevin Grittner <kevin.grittner@wicourts.gov>
Rural Hunter <ruralhunter@gmail.com> writes:
> yes, it's truncated. the full sql is like this:
> "update article set
> tm_update=$1,rply_cnt=$2,read_cnt=$3,tm_last_rply=$4 where
> title_hash=$5"
> the title_hash is unique.
>
> I dig another case more and found something interesting. it's actually
> waiting for a lock of type transactionid. I ran the query below 3
Normal. That's the kind of lock you are waiting for when some other
transaction has touched the same rows for update that you are
attempting.
> times very quickly and each time it showed a different lock holder.
> db=# select pl1.*,pl2.pid,pa.query_start,pa.waiting,pa.current_query
> from pg_locks pl1 left join pg_locks pl2 on
> pl1.transactionid=pl2.transactionid and pl2.granted
> left join pg_stat_activity pa on pl2.pid=pa.procpid where pl1.pid=6053
> and not pl1.granted;
> locktype | database | relation | page | tuple | virtualxid |
> transactionid | classid | objid | objsubid | virtualtransaction | pid
> | mode | granted | pid |
> query_start | waiting |
> current_query
> ---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------+---------+------+---------
> ----------------------+---------+---------------------------------------------------------------------------------------------
> transactionid | | | | | |
> 1586721800 | | | | 238/39230 | 6053 |
> ShareLock | f | 3026 | 2011-12-
> 21 22:24:20.027493+08 | t | update article set
> tm_update=$1,rply_cnt=$2,read_cnt=$3,tm_last_rply=$4 where
> title_hash=$5
> (1 row)
> db=# select pl1.*,pl2.pid,pa.query_start,pa.waiting,pa.current_query
> from pg_locks pl1 left join pg_locks pl2 on
> pl1.transactionid=pl2.transactionid and pl2.granted
> left join pg_stat_activity pa on pl2.pid=pa.procpid where pl1.pid=6053
> and not pl1.granted;
> locktype | database | relation | page | tuple | virtualxid |
> transactionid | classid | objid | objsubid | virtualtransaction | pid
> | mode | granted | pid |
> query_start | waiting |
> current_query
> ---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------+---------+------+---------
> ----------------------+---------+---------------------------------------------------------------------------------------------
> transactionid | | | | | |
> 1586739901 | | | | 238/39230 | 6053 |
> ShareLock | f | 3254 | 2011-12-
> 21 22:25:15.133554+08 | t | update article set
> tm_update=$1,rply_cnt=$2,read_cnt=$3,tm_last_rply=$4 where
> title_hash=$5
> (1 row)
>
> db=# select pl1.*,pl2.pid,pa.query_start,pa.waiting,pa.current_query
> from pg_locks pl1 left join pg_locks pl2 on
> pl1.transactionid=pl2.transactionid and pl2.granted
> left join pg_stat_activity pa on pl2.pid=pa.procpid where pl1.pid=6053
> and not pl1.granted;
> locktype | database | relation | page | tuple | virtualxid |
> transactionid | classid | objid | objsubid | virtualtransaction | pid
> | mode | granted | pid |
> query_start | waiting |
> current_query
> ---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------+---------+------+---------
> ----------------------+---------+---------------------------------------------------------------------------------------------
> transactionid | | | | | |
> 1586626482 | | | | 238/39230 | 6053 |
> ShareLock | f | 1518 | 2011-12-
> 21 22:19:28.880025+08 | t | update article set
> tm_update=$1,rply_cnt=$2,read_cnt=$3,tm_last_rply=$4 where
> title_hash=$5
> (1 row)
>
> I found the description of transactionid type here:
> http://archives.postgresql.org/pgsql-novice/2010-05/msg00066.php
> Currently, the only case where anything will try to take a sharelock on
> transaction id is when it is blocking on a row-level lock as a result of
> trying to modify or delete or SELECT FOR UPDATE/FOR SHARE a row that the
> other transaction already modified or deleted or selected FOR
> UPDATE/SHARE.
>
> I'm pretty sure those queries are updating different rows each. why
> they are waiting for row lock for each other?
> Another question is: query A waiting for B, then waiting for C, then
> waiting for D. I checked the query start time, A is much earlier than
> B/C/D. Why A still couldn't get the lock while looks B/C/D seems have
> gotten the lock even ABCD are all similar transaction?
>
>
> äº2011å¹´12æ21æ¥ 21:51:14,Bèrto ëd Sèraåå°:
>> Hi!
>>
>> I don't see a WHERE clause, so it looks like you're updating the
>> whole table each time.
>>
>>
>> it's got a substr(pg_stat_activity.current_query,1,30) in it, so we
>> shall hardly see anything about the WHERE clause, but we'd really
>> need to have more info about it.
>>
>> Bèrto
>> --
>> ==============================
>> If Pac-Man had affected us as kids, we'd all be running around in a
>> darkened room munching pills and listening to repetitive music.
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: lock problem
2011-12-21 13:28 Re: lock problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-21 13:51 ` Re: lock problem Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-21 14:41 ` Re: lock problem Rural Hunter <ruralhunter@gmail.com>
2011-12-21 15:25 ` Re: lock problem Jerry Sievers <gsievers19@comcast.net>
@ 2011-12-21 15:40 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-21 16:02 ` Re: lock problem Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bèrto ëd Sèra @ 2011-12-21 15:40 UTC (permalink / raw)
To: Jerry Sievers <gsievers19@comcast.net>; +Cc: Rural Hunter <ruralhunter@gmail.com>; pgsql-admin@postgresql.org, Kevin Grittner <kevin.grittner@wicourts.gov>
Hi,
> > I dig another case more and found something interesting. it's actually
> > waiting for a lock of type transactionid. I ran the query below 3
>
> Normal. That's the kind of lock you are waiting for when some other
> transaction has touched the same rows for update that you are
> attempting.
Record level locks are stored on the records themselves, so you won't see
them explicitly mentioned in views like pg_locks:
"Although tuples are a lockable type of object, information about row-level
locks is stored on disk, not in memory, and therefore row-level locks
normally do not appear in this view. If a transaction is waiting for a
row-level lock, it will usually appear in the view as waiting for the
permanent transaction ID of the current holder of that row lock."
See http://www.postgresql.org/docs/9.1/static/explicit-locking.html
Bèrto
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: lock problem
2011-12-21 13:28 Re: lock problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-21 13:51 ` Re: lock problem Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-21 14:41 ` Re: lock problem Rural Hunter <ruralhunter@gmail.com>
2011-12-21 15:25 ` Re: lock problem Jerry Sievers <gsievers19@comcast.net>
2011-12-21 15:40 ` Re: lock problem Bèrto ëd Sèra <berto.d.sera@gmail.com>
@ 2011-12-21 16:02 ` Rural Hunter <ruralhunter@gmail.com>
2011-12-21 16:09 ` Re: lock problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 10+ messages in thread
From: Rural Hunter @ 2011-12-21 16:02 UTC (permalink / raw)
To: Bèrto ëd Sèra <berto.d.sera@gmail.com>; +Cc: pgsql-admin@postgresql.org, Kevin Grittner <kevin.grittner@wicourts.gov>; Jerry Sievers <gsievers19@comcast.net>
well, thanks. I checked the application and found there was a bug
causing many queries were updating the same row. However, those updates
are just single statement, no multi-statement transaction involved. So
I still have this question:
same statement A,B,C,D update same row. The start order is A->B->C-D.
From what I've gotten, B/C/D got the lock before A. Why did that
happen?
于2011年12月21日 23:40:35,Bèrto ëd Sèra写到:
> Hi,
>
> > I dig another case more and found something interesting. it's
> actually
> > waiting for a lock of type transactionid. I ran the query below 3
>
> Normal. That's the kind of lock you are waiting for when some other
> transaction has touched the same rows for update that you are
> attempting.
>
>
> Record level locks are stored on the records themselves, so you won't
> see them explicitly mentioned in views like pg_locks:
> "Although tuples are a lockable type of object, information about
> row-level locks is stored on disk, not in memory, and therefore
> row-level locks normally do not appear in this view. If a transaction
> is waiting for a row-level lock, it will usually appear in the view as
> waiting for the permanent transaction ID of the current holder of that
> row lock."
> See http://www.postgresql.org/docs/9.1/static/explicit-locking.html
>
> Bèrto
> --
> ==============================
> If Pac-Man had affected us as kids, we'd all be running around in a
> darkened room munching pills and listening to repetitive music.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: lock problem
2011-12-21 13:28 Re: lock problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-21 13:51 ` Re: lock problem Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-21 14:41 ` Re: lock problem Rural Hunter <ruralhunter@gmail.com>
2011-12-21 15:25 ` Re: lock problem Jerry Sievers <gsievers19@comcast.net>
2011-12-21 15:40 ` Re: lock problem Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-21 16:02 ` Re: lock problem Rural Hunter <ruralhunter@gmail.com>
@ 2011-12-21 16:09 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-22 01:44 ` Re: lock problem Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Kevin Grittner @ 2011-12-21 16:09 UTC (permalink / raw)
To: berto.d.sera@gmail.com; Rural Hunter <ruralhunter@gmail.com>; +Cc: Jerry Sievers <gsievers19@comcast.net>; pgsql-admin
Rural Hunter <ruralhunter@gmail.com> wrote:
> I still have this question:
> same statement A,B,C,D update same row. The start order is
> A->B->C-D. From what I've gotten, B/C/D got the lock before A.
> Why did that happen?
Did you do anything to prevent it from happening? If not, the OS
scheduler is going to give time to one process or another in a
fairly unpredictable way.
-Kevin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: lock problem
2011-12-21 13:28 Re: lock problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-21 13:51 ` Re: lock problem Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-21 14:41 ` Re: lock problem Rural Hunter <ruralhunter@gmail.com>
2011-12-21 15:25 ` Re: lock problem Jerry Sievers <gsievers19@comcast.net>
2011-12-21 15:40 ` Re: lock problem Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-21 16:02 ` Re: lock problem Rural Hunter <ruralhunter@gmail.com>
2011-12-21 16:09 ` Re: lock problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-12-22 01:44 ` Rural Hunter <ruralhunter@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rural Hunter @ 2011-12-22 01:44 UTC (permalink / raw)
To: Kevin Grittner <kevin.grittner@wicourts.gov>; +Cc: Jerry Sievers <gsievers19@comcast.net>; pgsql-admin; berto.d.sera@gmail.com
hmm....no I didn't do anything. is the lock priority decided by OS not
the DB? I'm confused here. B/C/D started several mins later than A here
while the update statement takes no more than 1 second. of coz there
are hundreds of connections trying to acquire the lock during that
time.
于2011年12月22日 0:09:17,Kevin Grittner写到:
> Rural Hunter<ruralhunter@gmail.com> wrote:
>
>> I still have this question:
>> same statement A,B,C,D update same row. The start order is
>> A->B->C-D. From what I've gotten, B/C/D got the lock before A.
>> Why did that happen?
>
> Did you do anything to prevent it from happening? If not, the OS
> scheduler is going to give time to one process or another in a
> fairly unpredictable way.
>
> -Kevin
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: lock problem
@ 2011-12-22 03:26 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 10+ messages in thread
From: Kevin Grittner @ 2011-12-22 03:26 UTC (permalink / raw)
To: ruralhunter@gmail.com; +Cc: gsievers19@comcast.net; berto.d.sera@gmail.com; pgsql-admin
Rural Hunter wrote:
> Kevin Grittner wrote:
>> Rural Hunter wrote:
>>
>>> I still have this question:
>>> same statement A,B,C,D update same row. The start order is
>>> A->B->C-D. From what I've gotten, B/C/D got the lock before A.
>>> Why did that happen?
>>
>> Did you do anything to prevent it from happening? If not, the OS
>> scheduler is going to give time to one process or another in a
>> fairly unpredictable way.
>
> hmm....no I didn't do anything. is the lock priority decided by OS
> not the DB?
What the heck is "lock priority"? I'm not familiar with that term.
> I'm confused here. B/C/D started several mins later than A here
> while the update statement takes no more than 1 second. of coz
> there are hundreds of connections trying to acquire the lock during
> that time.
Well, that sounds like your problem right there. If you have
hundreds of connections, among a great many other problems,
competition for locks can leave any one connection shut out for quite
a long time. If you have hundreds of connections, you will have this
and other problems indefinitely. Right now, look into setting up
pgpool or some other connection pooler to accept your hundreds of
connections, and funnel them into a number of connections somewhere
around double the number of cores on your server. Use transaction-
based pooling, so that when you have more transactions than the
server can efficiently handle, the extras will queue. This will
improve both your overall throughput and your latency (response
time).
Many people naively think that even when the server is saturated,
throwing another transaction at it right away will get a response for
it sooner. That is dead wrong.
-Kevin
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2011-12-22 03:26 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-21 07:55 lock problem Rural Hunter <ruralhunter@gmail.com>
2011-12-21 13:28 Re: lock problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-21 13:51 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-21 14:41 ` Rural Hunter <ruralhunter@gmail.com>
2011-12-21 15:25 ` Jerry Sievers <gsievers19@comcast.net>
2011-12-21 15:40 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-21 16:02 ` Rural Hunter <ruralhunter@gmail.com>
2011-12-21 16:09 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-22 01:44 ` Rural Hunter <ruralhunter@gmail.com>
2011-12-22 03:26 Re: lock problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
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