Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 26B0014E23D3 for ; 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 ) 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 Cc: =?iso-8859-1?Q?B=E8rto_=EBd_S=E8ra?= , pgsql-admin@postgresql.org, Kevin Grittner Subject: Re: lock problem References: <4EF18A8A0200002500043F0A@gw.wicourts.gov> <4EF1F027.507@gmail.com> From: Jerry Sievers 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 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