From ruralhunter@gmail.com Wed Dec  21 07:55:39 2011
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>



From Kevin.Grittner@wicourts.gov Wed Dec  21 13:28:28 2011
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 3F45A14E23D2
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 09:28:28 -0400 (AST)
Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <Kevin.Grittner@wicourts.gov>) id 1RdMDa-0001uS-Kg
	for pgsql-admin@postgresql.org; Wed, 21 Dec 2011 13:28:28 +0000
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Wed, 21 Dec 2011 07:28:12 -0600
Message-Id: <4EF18A8A0200002500043F0A@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Wed, 21 Dec 2011 07:28:10 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <ruralhunter@gmail.com>,<pgsql-admin@postgresql.org>
Subject: Re: lock problem
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Pg-Spam-Score: -6.8 (------)
X-Archive-Number: 201112/106
X-Sequence-Number: 38864

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




From berto.d.sera@gmail.com Wed Dec  21 13:51:28 2011
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 153EB14E23D3
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 09:51:28 -0400 (AST)
Received: from mail-yx0-f174.google.com ([209.85.213.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <berto.d.sera@gmail.com>) id 1RdMZr-0006bz-DG
	for pgsql-admin@postgresql.org; Wed, 21 Dec 2011 13:51:27 +0000
Received: by yenm11 with SMTP id m11so4614806yen.19
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 05:51:15 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=ZDjpeRhV6tgD0ysA3aSZF226iaiuf9hZB0PjDSWkXMI=;
	b=OV4/KxWf3uYHwaXR6olrkY7gfrCJrf/dK+EZ9G0DRtjiiW5HFjtCetMyIgR7mio/iq
	JdjXvsLx/xGFmzhSsahqpBnXnApMxIjQCJrHjN9ZI0SKXp435dA8WtUfPGbna+ekiB2T
	0pUDQfMnrlsDOMz+WsoaAQqPGKqz75nticzZY=
MIME-Version: 1.0
Received: by 10.236.77.72 with SMTP id c48mr9309827yhe.55.1324475474928; Wed,
	21 Dec 2011 05:51:14 -0800 (PST)
Received: by 10.147.146.16 with HTTP; Wed, 21 Dec 2011 05:51:14 -0800 (PST)
In-Reply-To: <4EF18A8A0200002500043F0A@gw.wicourts.gov>
References: <4EF18A8A0200002500043F0A@gw.wicourts.gov>
Date: Wed, 21 Dec 2011 16:51:14 +0300
Message-ID: 
 <CAKwGa_-C0Gcp2PaNWS9_kq0nz7KfcbV8hb5XYmxzUyHNt6AF-w@mail.gmail.com>
Subject: Re: lock problem
From: =?UTF-8?B?QsOocnRvIMOrZCBTw6hyYQ==?= <berto.d.sera@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: ruralhunter@gmail.com, pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundary=20cf300512bc32643e04b49a7aab
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201112/107
X-Sequence-Number: 38865

--20cf300512bc32643e04b49a7aab
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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=C3=A8rto
--=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
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.

--20cf300512bc32643e04b49a7aab
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote">Hi!<div>=C2=A0</div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1e=
x">I don&#39;t see a WHERE clause, so it looks like you&#39;re updating the=
<br>
whole table each time. =C2=A0</blockquote><div><br></div><div>it&#39;s got =
a=C2=A0<span style>substr(pg_stat_activity.</span><span style>current_query=
,1,30) in it, so we shall hardly see anything about the WHERE clause, but w=
e&#39;d really need to have more info about it.</span></div>
<div><span style><br></span></div><div><span style>B=C3=A8rto</span></div><=
div>--=C2=A0</div></div>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br><span style=3D"font-family:Verda=
na,Arial,Helvetica,sans-serif;font-size:12px;line-height:17px">If Pac-Man h=
ad affected us as kids, we&#39;d all be running around in a darkened room m=
unching pills and listening to repetitive music.</span><br>


--20cf300512bc32643e04b49a7aab--



From ruralhunter@gmail.com Wed Dec  21 14:42:10 2011
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 1823114E23D3
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 10:42:10 -0400 (AST)
Received: from mail-gx0-f174.google.com ([209.85.161.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ruralhunter@gmail.com>) id 1RdNMs-0007UL-1K
	for pgsql-admin@postgresql.org; Wed, 21 Dec 2011 14:42:09 +0000
Received: by ggdk6 with SMTP id k6so5547644ggd.19
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 06:41:53 -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:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	bh=6aypJPnb/UNTD6wu31KfwN9b2uY6Q13CGEVmW2CL2/E=;
	b=o//1KkiuUvAjtRu/sCR4mj8midj23+TKs7ZpMI9CUUOE27CiIPS8UPl+C1Ci3kS7t0
	kVvEeBjRVy8hLgIrowBxtYHWTSVdKHwK6RkMrq+vqeH5ulyJetS1V/bFl60gxmCJ/rO3
	4enMfiDKScz4eVwwYXefrp60DSYThT3ecnpwQ=
Received: by 10.50.191.200 with SMTP id ha8mr3707563igc.27.1324478513479;
	Wed, 21 Dec 2011 06:41:53 -0800 (PST)
Received: from [192.168.11.4] ([114.95.167.241])
	by mx.google.com with ESMTPS id py4sm30024057igc.2.2011.12.21.06.41.47
	(version=TLSv1/SSLv3 cipher=OTHER);
	Wed, 21 Dec 2011 06:41:52 -0800 (PST)
Message-ID: <4EF1F027.507@gmail.com>
Date: Wed, 21 Dec 2011 22:41:43 +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: =?UTF-8?B?QsOocnRvIMOrZCBTw6hyYQ==?= <berto.d.sera@gmail.com>
CC: pgsql-admin@postgresql.org, Kevin Grittner <kevin.grittner@wicourts.gov>
Subject: Re: lock problem
References: <4EF18A8A0200002500043F0A@gw.wicourts.gov>
	<CAKwGa_-C0Gcp2PaNWS9_kq0nz7KfcbV8hb5XYmxzUyHNt6AF-w@mail.gmail.com>
In-Reply-To: 
 <CAKwGa_-C0Gcp2PaNWS9_kq0nz7KfcbV8hb5XYmxzUyHNt6AF-w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201112/108
X-Sequence-Number: 38866

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.





From gsievers19@comcast.net Wed Dec  21 15:25:58 2011
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 26B0014E23D3
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 11:25:58 -0400 (AST)
Received: from qmta08.emeryville.ca.mail.comcast.net ([76.96.30.80])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <gsievers19@comcast.net>) id 1RdO3H-0004KP-7W
	for pgsql-admin@postgresql.org; Wed, 21 Dec 2011 15:25:58 +0000
Received: from omta12.emeryville.ca.mail.comcast.net ([76.96.30.44])
	by qmta08.emeryville.ca.mail.comcast.net with comcast
	id BrQX1i0040x6nqcA8rRaXA; Wed, 21 Dec 2011 15:25:34 +0000
Received: from homie ([69.59.255.28])
	by omta12.emeryville.ca.mail.comcast.net with comcast
	id BrRx1i0170dYFoM8YrS1Ri; Wed, 21 Dec 2011 15:26:11 +0000
Received: by homie (Postfix, from userid 1000)
	id 77FE21834A6; Wed, 21 Dec 2011 10:25:24 -0500 (EST)
To: Rural Hunter <ruralhunter@gmail.com>
Cc: =?iso-8859-1?Q?B=E8rto_=EBd_S=E8ra?= <berto.d.sera@gmail.com>,
	pgsql-admin@postgresql.org, Kevin Grittner <kevin.grittner@wicourts.gov>
Subject: Re: lock problem
References: <4EF18A8A0200002500043F0A@gw.wicourts.gov>
	<CAKwGa_-C0Gcp2PaNWS9_kq0nz7KfcbV8hb5XYmxzUyHNt6AF-w@mail.gmail.com>
	<4EF1F027.507@gmail.com>
From: Jerry Sievers <gsievers19@comcast.net>
Date: Wed, 21 Dec 2011 10:25:24 -0500
In-Reply-To: <4EF1F027.507@gmail.com> (Rural Hunter's message of "Wed\,
	21 Dec 2011 22\:41\:43 +0800")
Message-ID: <87aa6m7xqz.fsf@comcast.net>
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -2.5 (--)
X-Archive-Number: 201112/110
X-Sequence-Number: 38868

Rural Hunter <ruralhunter@gmail.com> writes:

> yes, it's truncated. the full sql is like this:
> "update article set
> tm_update=3D$1,rply_cnt=3D$2,read_cnt=3D$3,tm_last_rply=3D$4 where
> title_hash=3D$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=3D# select pl1.*,pl2.pid,pa.query_start,pa.waiting,pa.current_query
> from pg_locks pl1 left join pg_locks pl2 on
> pl1.transactionid=3Dpl2.transactionid and pl2.granted
> left join pg_stat_activity pa on pl2.pid=3Dpa.procpid where pl1.pid=3D6053
> 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=3D$1,rply_cnt=3D$2,read_cnt=3D$3,tm_last_rply=3D$4 where
> title_hash=3D$5
> (1 row)
> db=3D# select pl1.*,pl2.pid,pa.query_start,pa.waiting,pa.current_query
> from pg_locks pl1 left join pg_locks pl2 on
> pl1.transactionid=3Dpl2.transactionid and pl2.granted
> left join pg_stat_activity pa on pl2.pid=3Dpa.procpid where pl1.pid=3D6053
> 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=3D$1,rply_cnt=3D$2,read_cnt=3D$3,tm_last_rply=3D$4 where
> title_hash=3D$5
> (1 row)
>
> db=3D# select pl1.*,pl2.pid,pa.query_start,pa.waiting,pa.current_query
> from pg_locks pl1 left join pg_locks pl2 on
> pl1.transactionid=3Dpl2.transactionid and pl2.granted
> left join pg_stat_activity pa on pl2.pid=3Dpa.procpid where pl1.pid=3D6053
> 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=3D$1,rply_cnt=3D$2,read_cnt=3D$3,tm_last_rply=3D$4 where
> title_hash=3D$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?
>
>
> =C3=A4=C2=BA=C2=8E2011=C3=A5=C2=B9=C2=B412=C3=A6=C2=9C=C2=8821=C3=A6=C2=
=97=C2=A5 21:51:14,B=C3=83=C2=A8rto =C3=83=C2=ABd S=C3=83=C2=A8ra=C3=A5=C2=
=86=C2=99=C3=A5=C2=88=C2=B0:
>> Hi!
>>
>>     I don't see a WHERE clause, so it looks like you're updating the
>>     whole table each time.=20
>>
>>
>> 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=C3=83=C2=A8rto
>> --=20
>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
>> 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.
>
>
>
> --=20
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--=20
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144



From berto.d.sera@gmail.com Wed Dec  21 15:40:51 2011
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id ED80C14E23D3
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 11:40:51 -0400 (AST)
Received: from mail-yx0-f174.google.com ([209.85.213.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <berto.d.sera@gmail.com>) id 1RdOHf-000078-SE
	for pgsql-admin@postgresql.org; Wed, 21 Dec 2011 15:40:51 +0000
Received: by yenm11 with SMTP id m11so4690121yen.19
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 07:40:35 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=ozoyjw2+eJBWajuUMVg3K/v+5YKaVVV/ZjfKwoB9lOs=;
	b=IoZV+fGEDcJs2Osf2xBpqFJMHSVIxvnVLlBzAZBTCQE4NOOCDgmeSJNDsXMzzarJpk
	2EqJQQjFxx9Ggwm6nrOnjPGqeMMDN5iQdKTlPFto16UzlnemFW5yn+SuvHaBQQpfkgS+
	S/WpDjk3UkWfbN9gnSHUqm2/NqRcslxXfi6iE=
MIME-Version: 1.0
Received: by 10.236.183.133 with SMTP id q5mr10062010yhm.38.1324482035317;
	Wed, 21 Dec 2011 07:40:35 -0800 (PST)
Received: by 10.147.146.16 with HTTP; Wed, 21 Dec 2011 07:40:35 -0800 (PST)
In-Reply-To: <87aa6m7xqz.fsf@comcast.net>
References: <4EF18A8A0200002500043F0A@gw.wicourts.gov>
	<CAKwGa_-C0Gcp2PaNWS9_kq0nz7KfcbV8hb5XYmxzUyHNt6AF-w@mail.gmail.com>
	<4EF1F027.507@gmail.com> <87aa6m7xqz.fsf@comcast.net>
Date: Wed, 21 Dec 2011 18:40:35 +0300
Message-ID: 
 <CAKwGa__-7ncap5sji9x0=3g5V=Fv1cpZYGO5bKDHgxeT6z9rzg@mail.gmail.com>
Subject: Re: lock problem
From: =?UTF-8?B?QsOocnRvIMOrZCBTw6hyYQ==?= <berto.d.sera@gmail.com>
To: Jerry Sievers <gsievers19@comcast.net>
Cc: Rural Hunter <ruralhunter@gmail.com>, pgsql-admin@postgresql.org,
	Kevin Grittner <kevin.grittner@wicourts.gov>
Content-Type: multipart/alternative; boundary=20cf305495fd39fd7004b49c013c
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201112/111
X-Sequence-Number: 38869

--20cf305495fd39fd7004b49c013c
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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=C3=A8rto
--=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
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.

--20cf305495fd39fd7004b49c013c
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote">Hi,<div>=C2=A0</div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1e=
x"><div class=3D"im">
&gt; I dig another case more and found something interesting. it&#39;s actu=
ally<br>
&gt; waiting for a lock of type transactionid. I ran the query below 3<br>
<br>
</div>Normal. =C2=A0That&#39;s the kind of lock you are waiting for when so=
me other<br>
transaction has touched the same rows for update that you are<br>
attempting.</blockquote><div><br></div>Record level locks are stored on the=
 records themselves, so you won&#39;t see them explicitly mentioned in view=
s like pg_locks:<br>&quot;Although tuples are a lockable type of object, in=
formation about row-level locks is stored on disk, not in memory, and there=
fore 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 wait=
ing for the permanent transaction ID of the current holder of that row lock=
.&quot;</div>
<div><span style=3D"background-color:rgb(255,255,255);font-family:verdana,s=
ans-serif;font-size:medium">=C2=A0</span></div>See=C2=A0<a href=3D"http://w=
ww.postgresql.org/docs/9.1/static/explicit-locking.html">http://www.postgre=
sql.org/docs/9.1/static/explicit-locking.html</a><div>
<br></div><div>B=C3=A8rto</div>-- <br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br><span style=3D"fo=
nt-family:Verdana,Arial,Helvetica,sans-serif;font-size:12px;line-height:17p=
x">If Pac-Man had affected us as kids, we&#39;d all be running around in a =
darkened room munching pills and listening to repetitive music.</span><br>


--20cf305495fd39fd7004b49c013c--



From ruralhunter@gmail.com Wed Dec  21 16:03:22 2011
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 88F4014E23D3
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 12:03:22 -0400 (AST)
Received: from mail-gy0-f174.google.com ([209.85.160.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ruralhunter@gmail.com>) id 1RdOdS-0000S5-KH
	for pgsql-admin@postgresql.org; Wed, 21 Dec 2011 16:03:22 +0000
Received: by ghbz12 with SMTP id z12so4895526ghb.19
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 08:03:06 -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:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	bh=TbMXyYO8u9+K5S1izD0QLEZ5k0hSmzJLxpYSPF0Rijc=;
	b=QELAEL2SgfWoFM6ptDf6uQjPs0UP2+8F3HJ6u29YEGLpk2QFBSN6cEwW/Dk7FEAk/r
	N877q9iffN9Hzx2R8Q1Zr+urn5L/wODWvj6IDhLWYEvS3i/8M5Msq+FRpvcbIIUD9Dc/
	8z7JeLKYYzdMFDHkDMwG2mq7Kihp4/s/QpYos=
Received: by 10.50.193.229 with SMTP id hr5mr4139893igc.15.1324483385976;
	Wed, 21 Dec 2011 08:03:05 -0800 (PST)
Received: from [192.168.11.4] ([114.95.167.241])
	by mx.google.com with ESMTPS id 36sm18398238ibc.6.2011.12.21.08.03.01
	(version=TLSv1/SSLv3 cipher=OTHER);
	Wed, 21 Dec 2011 08:03:05 -0800 (PST)
Message-ID: <4EF20333.8020404@gmail.com>
Date: Thu, 22 Dec 2011 00:02:59 +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: =?UTF-8?B?QsOocnRvIMOrZCBTw6hyYQ==?= <berto.d.sera@gmail.com>
CC: pgsql-admin@postgresql.org, Kevin Grittner <kevin.grittner@wicourts.gov>,
	Jerry Sievers <gsievers19@comcast.net>
Subject: Re: lock problem
References: <4EF18A8A0200002500043F0A@gw.wicourts.gov>
	<CAKwGa_-C0Gcp2PaNWS9_kq0nz7KfcbV8hb5XYmxzUyHNt6AF-w@mail.gmail.com>
	<4EF1F027.507@gmail.com> <87aa6m7xqz.fsf@comcast.net>
	<CAKwGa__-7ncap5sji9x0=3g5V=Fv1cpZYGO5bKDHgxeT6z9rzg@mail.gmail.com>
In-Reply-To: 
 <CAKwGa__-7ncap5sji9x0=3g5V=Fv1cpZYGO5bKDHgxeT6z9rzg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201112/112
X-Sequence-Number: 38870

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.





From Kevin.Grittner@wicourts.gov Wed Dec  21 16:09:39 2011
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 8F1D014E23D3
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 12:09:39 -0400 (AST)
Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <Kevin.Grittner@wicourts.gov>) id 1RdOjb-0000Yx-BK
	for pgsql-admin@postgresql.org; Wed, 21 Dec 2011 16:09:39 +0000
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Wed, 21 Dec 2011 10:09:26 -0600
Message-Id: <4EF1B04D0200002500043F4C@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Wed, 21 Dec 2011 10:09:17 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <berto.d.sera@gmail.com>,"Rural Hunter" <ruralhunter@gmail.com>
Cc: "Jerry Sievers" <gsievers19@comcast.net>,
	 <pgsql-admin@postgresql.org>
Subject: Re: lock problem
References: <4EF18A8A0200002500043F0A@gw.wicourts.gov>
	<CAKwGa_-C0Gcp2PaNWS9_kq0nz7KfcbV8hb5XYmxzUyHNt6AF-w@mail.gmail.com>
	<4EF1F027.507@gmail.com> <87aa6m7xqz.fsf@comcast.net>
	<CAKwGa__-7ncap5sji9x0=3g5V=Fv1cpZYGO5bKDHgxeT6z9rzg@mail.gmail.com>
	<4EF20333.8020404@gmail.com>
In-Reply-To: <4EF20333.8020404@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Pg-Spam-Score: -6.8 (------)
X-Archive-Number: 201112/113
X-Sequence-Number: 38871

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



From ruralhunter@gmail.com Thu Dec  22 01:45:03 2011
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 8DD3AEB289A
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 21:45:03 -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 1RdXiP-0001jM-Et
	for pgsql-admin@postgresql.org; Thu, 22 Dec 2011 01:45:03 +0000
Received: by iaeh11 with SMTP id h11so12712909iae.19
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 17:44:48 -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:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	bh=Hfy2d1ZckryHMFwJeN/eZ5QgGooKE7JZ4qZO07HYlsU=;
	b=COKgAJIR2Fbtup9hkCBye83lO0VWesEQivBLBeouvNrDuJkq/XmUFSsvV3dPsoQ5M2
	hzXbmeunJJn3oVzeFR4xCfQi/R0pwVO7fVkGz0EcH3vSnGfy8+U/T1kKE0qo08zxvymO
	TsJ4lySEtOdSVKsxB2fIlXr01Lwak1z/pytNU=
Received: by 10.50.190.201 with SMTP id gs9mr6501509igc.1.1324518288760;
	Wed, 21 Dec 2011 17:44:48 -0800 (PST)
Received: from [192.168.1.4] ([116.226.65.236])
	by mx.google.com with ESMTPS id rc7sm32811733igb.0.2011.12.21.17.44.44
	(version=TLSv1/SSLv3 cipher=OTHER);
	Wed, 21 Dec 2011 17:44:47 -0800 (PST)
Message-ID: <4EF28B8A.1060602@gmail.com>
Date: Thu, 22 Dec 2011 09:44:42 +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: Kevin Grittner <kevin.grittner@wicourts.gov>
CC: Jerry Sievers <gsievers19@comcast.net>, pgsql-admin@postgresql.org,
	berto.d.sera@gmail.com
Subject: Re: lock problem
References: <4EF18A8A0200002500043F0A@gw.wicourts.gov>
	<CAKwGa_-C0Gcp2PaNWS9_kq0nz7KfcbV8hb5XYmxzUyHNt6AF-w@mail.gmail.com>
	<4EF1F027.507@gmail.com> <87aa6m7xqz.fsf@comcast.net>
	<CAKwGa__-7ncap5sji9x0=3g5V=Fv1cpZYGO5bKDHgxeT6z9rzg@mail.gmail.com>
	<4EF20333.8020404@gmail.com>
	<4EF1B04D0200002500043F4C@gw.wicourts.gov>
In-Reply-To: <4EF1B04D0200002500043F4C@gw.wicourts.gov>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201112/121
X-Sequence-Number: 38879

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
>





From Kevin.Grittner@wicourts.gov Thu Dec  22 03:47:04 2011
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 0AD6E17220FC
	for <pgsql-admin@postgresql.org>; Wed, 21 Dec 2011 23:47:04 -0400 (AST)
Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <Kevin.Grittner@wicourts.gov>) id 1RdZcT-0001xu-GI
	for pgsql-admin@postgresql.org; Thu, 22 Dec 2011 03:47:03 +0000
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Wed, 21 Dec 2011 21:26:36 -0600
Message-Id: <4EF24F020200002500043FA3@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Wed, 21 Dec 2011 21:26:26 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <ruralhunter@gmail.com>
Cc: <gsievers19@comcast.net>,<berto.d.sera@gmail.com>,
	<pgsql-admin@postgresql.org>
Subject: Re: lock problem
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Pg-Spam-Score: -6.8 (------)
X-Archive-Number: 201112/122
X-Sequence-Number: 38880

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