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

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.