Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id DB77B14E23D0
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 03:55:39 -0400 (AST)
Received: from mail-iy0-f174.google.com ([209.85.210.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ruralhunter@gmail.com>) id 1RdH1W-00019I-7V
	for pgsql-admin@postgresql.org; Wed, 21 Dec 2011 07:55:40 +0000
Received: by iaeh11 with SMTP id h11so11513244iae.19
	for <pgsql-admin@postgresql.org>; Tue, 20 Dec 2011 23:55:25 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type:content-transfer-encoding;
	bh=hcNQ9zzXr4JQ8GTJK3qxTfUGwKDeEtuRtNaLQ2UwMDE=;
	b=k58ULZYC4euLRM4d2TQDBhezBXjyv3JPNZ4zBqrVTPyVFyf/RqbTS/bNPJSwBq5dnX
	N189gKxzDMC3jnLuCkPQUvTXwBNk+1mnQXWIlz7/8FmA+dtVkRMBQu5UKMFZRZIwZt5Y
	nR/NHOVxxiewOH0Grb+9TYnlkXR+2iT1Z6TiQ=
Received: by 10.42.19.195 with SMTP id d3mr5429098icb.21.1324454125742;
	Tue, 20 Dec 2011 23:55:25 -0800 (PST)
Received: from [192.168.1.4] ([116.226.65.236])
	by mx.google.com with ESMTPS id j3sm14192521ibj.1.2011.12.20.23.55.21
	(version=TLSv1/SSLv3 cipher=OTHER);
	Tue, 20 Dec 2011 23:55:24 -0800 (PST)
Message-ID: <4EF190E6.3030509@gmail.com>
Date: Wed, 21 Dec 2011 15:55:18 +0800
From: Rural Hunter <ruralhunter@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1;
 rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: lock problem
Content-Type: text/html; charset=GB18030
Content-Transfer-Encoding: 8bit
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201112/105
X-Sequence-Number: 38863

<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>