From: Jerry Sievers <gsievers19@comcast.net>
To: Rural Hunter <ruralhunter@gmail.com>
Cc: Bèrto ëd Sèra <berto.d.sera@gmail.com>
Cc: pgsql-admin@postgresql.org, Kevin Grittner <kevin.grittner@wicourts.gov>
Subject: Re: lock problem
Date: Wed, 21 Dec 2011 10:25:24 -0500
Message-ID: <87aa6m7xqz.fsf@comcast.net> (raw)
In-Reply-To: <4EF1F027.507@gmail.com>
References: <4EF18A8A0200002500043F0A@gw.wicourts.gov>
<CAKwGa_-C0Gcp2PaNWS9_kq0nz7KfcbV8hb5XYmxzUyHNt6AF-w@mail.gmail.com>
<4EF1F027.507@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gsievers19@comcast.net, ruralhunter@gmail.com, berto.d.sera@gmail.com, kevin.grittner@wicourts.gov
Subject: Re: lock problem
In-Reply-To: <87aa6m7xqz.fsf@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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