Weird problem that enormous locks

On 13/07/2011 12:52 AM, Tony Wang wrote:

Have any one experienced that, or any suggestion on researching/debugging?

Capture the contents of pg_catalog.pg_stat_activity whenever your cron
job notices high lock counts. That'll give you some more information to
work with.

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

On Wed, Jul 13, 2011 at 08:40, Craig Ringer <craig@postnewspapers.com.au>wrote:

On 13/07/2011 12:52 AM, Tony Wang wrote:

Have any one experienced that, or any suggestion on researching/debugging?

Capture the contents of pg_catalog.pg_stat_activity whenever your cron

job notices high lock counts. That'll give you some more information to work
with.

Thanks, but the cron job query has already joined pg_stat_activity table

Could I consider it a hardware problem, or postgresql running too long which
causes problems? (It ran about half a month, however, it ran much longer
than that without problems)

On Wed, Jul 13, 2011 at 00:52, Tony Wang <wwwjfy@gmail.com> wrote:

On 07/13/11 6:55 PM, Tony Wang wrote:

Could I consider it a hardware problem, or postgresql running too long
which causes problems? (It ran about half a month, however, it ran
much longer than that without problems)

i have postgres servers that run for months and even years without problems.

based on what I see in your original posting, there's no way anyone on
this list could possibly guess what is happening on your server.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On Thu, Jul 14, 2011 at 10:01, John R Pierce <pierce@hogranch.com> wrote:

On 07/13/11 6:55 PM, Tony Wang wrote:

Could I consider it a hardware problem, or postgresql running too long
which causes problems? (It ran about half a month, however, it ran much
longer than that without problems)

i have postgres servers that run for months and even years without
problems.

Yeah, same for me.

based on what I see in your original posting, there's no way anyone on this
list could possibly guess what is happening on your server.

Sorry but is there anything I'm missing? I just want to know any possible
situation can cause high locks. The server runs for more than a year, and I
didn't do any related update recently and it just happened.

On 07/13/11 7:16 PM, Tony Wang wrote:

On Thu, Jul 14, 2011 at 10:01, John R Pierce <pierce@hogranch.com
<mailto:pierce@hogranch.com>> wrote:

On 07/13/11 6:55 PM, Tony Wang wrote:

Could I consider it a hardware problem, or postgresql running
too long which causes problems? (It ran about half a month,
however, it ran much longer than that without problems)

i have postgres servers that run for months and even years without
problems.

Yeah, same for me.

based on what I see in your original posting, there's no way
anyone on this list could possibly guess what is happening on your
server.

Sorry but is there anything I'm missing? I just want to know any
possible situation can cause high locks. The server runs for more than
a year, and I didn't do any related update recently and it just happened.

If I run into locking problems, the first thing *I* do is look at
pg_stat_activity to see what sort of queries are active, and relate the
transaction OIDs to the pg_locks and the queries to figure out whats
locking on what, which it appears your join is doing.... If you had
that many exclusive_locks, just what were the queries making these
locks doing?

We don't know what sort of schema you have, what kind of queries your
applications make, etc etc etc. were there any hardware events related
to storage in the kernel message buffer (displayed by dmesg (1) on most
unix and linux systems) ? If linux, has the oomkiller run amok? (this
also should be logged in dmesg)

800 concurrent connections is a very large number for a server that has
at most a dozen cores. (you say you have x5650, thats a 6 core
processor, which supports at most 2 sockets, for 12 cores total. these
12 cores support hyperthreading, which allows 24 total threads). With
24 hardware threads and 800 queries running, you'd have 33 queries
contending for each CPU, which likely will result in LOWER total
performance than if you tried to execute fewer queries at once. If
most of those connections are idle at a given time, you likely should
consider using a connection pooler with a lot fewer max_connections,
say, no more than 100 or so.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com> wrote:

On 07/13/11 7:16 PM, Tony Wang wrote:

On Thu, Jul 14, 2011 at 10:01, John R Pierce <pierce@hogranch.com<mailto:

pierce@hogranch.com>> wrote:

On 07/13/11 6:55 PM, Tony Wang wrote:

Could I consider it a hardware problem, or postgresql running
too long which causes problems? (It ran about half a month,
however, it ran much longer than that without problems)

i have postgres servers that run for months and even years without
problems.

Yeah, same for me.

based on what I see in your original posting, there's no way
anyone on this list could possibly guess what is happening on your
server.

Sorry but is there anything I'm missing? I just want to know any possible
situation can cause high locks. The server runs for more than a year, and I
didn't do any related update recently and it just happened.

If I run into locking problems, the first thing *I* do is look at
pg_stat_activity to see what sort of queries are active, and relate the
transaction OIDs to the pg_locks and the queries to figure out whats locking
on what, which it appears your join is doing.... If you had that many
exclusive_locks, just what were the queries making these locks doing?

It's a game server, and the queries are updating users' money, as normal.
The sql is like "UPDATE player SET money = money + 100 where id = 12345".
The locks were RowExclusiveLock for the table "player" and the indexes. The
weird thing is there was another ExclusiveLock for the table "player", i.e.
"player" got two locks, one RowExclusiveLock and one ExclusiveLock.

In the postgresql documentation (
http://www.postgresql.org/docs/8.4/static/explicit-locking.html), it's said
about the Exclusive "This lock mode is not automatically acquired on user
tables by any PostgreSQL command."

We don't know what sort of schema you have, what kind of queries your
applications make, etc etc etc. were there any hardware events related to
storage in the kernel message buffer (displayed by dmesg (1) on most unix
and linux systems) ? If linux, has the oomkiller run amok? (this also
should be logged in dmesg)

Mostly update players' info, and another table called items for the items
ownership for users.
As I listed, I'm using ubuntu 10.04. I didn't find useful messages there.
Does oomkiller means out of memory killer? from the munin graph, the memory
usage is quite normal.

800 concurrent connections is a very large number for a server that has at
most a dozen cores. (you say you have x5650, thats a 6 core processor, which
supports at most 2 sockets, for 12 cores total. these 12 cores support
hyperthreading, which allows 24 total threads). With 24 hardware threads
and 800 queries running, you'd have 33 queries contending for each CPU,
which likely will result in LOWER total performance than if you tried to
execute fewer queries at once. If most of those connections are idle at a
given time, you likely should consider using a connection pooler with a lot
fewer max_connections, say, no more than 100 or so.

Yeah, that's what I planned to do next.

Thanks for your concerns! :)

On 07/13/11 8:47 PM, Tony Wang wrote:

It's a game server, and the queries are updating users' money, as
normal. The sql is like "UPDATE player SET money = money + 100 where
id = 12345". The locks were RowExclusiveLock for the table "player"
and the indexes. The weird thing is there was another ExclusiveLock
for the table "player", i.e. "player" got two locks,
one RowExclusiveLock and one ExclusiveLock.

that query should be quite fast. is it part of a larger transaction? is
there any possibility of multiple sessions/connections accessing the
same player.id?

it would be interesting to identify the process that issued the
exclusive lock and determine what query/queries its made. if its not
apparent in pg_stat_activity, perhaps enable logging of all DDL
commands, and check the logs.

if there's a lot of active queries (you ahve 800 connections)

select count(*),current_query from pg_stat_activity group by
current_query order by count(*) desc;

can help you make sense of them.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On Thu, Jul 14, 2011 at 12:35, John R Pierce <pierce@hogranch.com> wrote:

On 07/13/11 8:47 PM, Tony Wang wrote:

It's a game server, and the queries are updating users' money, as normal.
The sql is like "UPDATE player SET money = money + 100 where id = 12345".
The locks were RowExclusiveLock for the table "player" and the indexes. The
weird thing is there was another ExclusiveLock for the table "player", i.e.
"player" got two locks, one RowExclusiveLock and one ExclusiveLock.

that query should be quite fast. is it part of a larger transaction? is
there any possibility of multiple sessions/connections accessing the same
player.id?

That's possible, but I think only one row will be locked for a while, but
not thousands of locks for an hour. It's rare that thousands of users update
the value at once.

it would be interesting to identify the process that issued the exclusive
lock and determine what query/queries its made. if its not apparent in
pg_stat_activity, perhaps enable logging of all DDL commands, and check the
logs.

yeah, I've made the log_statement to "all" now. Previously, it only logged
slow queries more than 50ms. I could know something from logs if it happens
again (hope not).

if there's a lot of active queries (you ahve 800 connections)

select count(*),current_query from pg_stat_activity group by
current_query order by count(*) desc;

that's helpful, thanks.

Once time I've read 9.x PostgreSQL locks everything before offset, if You execute select for update offset. Do you call such query at least once? It's the way why we think about having 9.x server.

------------------------
Regards,
Radoslaw Smogura
(mobile)

-----Original Message-----
From: Tony Wang
Sent: 14 lipca 2011 07:00
To: John R Pierce
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Weird problem that enormous locks

On Thu, Jul 14, 2011 at 12:35, John R Pierce <pierce@hogranch.com> wrote:

On 07/13/11 8:47 PM, Tony Wang wrote:

It's a game server, and the queries are updating users' money, as normal. The sql is like "UPDATE player SET money = money + 100 where id = 12345". The locks were RowExclusiveLock for the table "player" and the indexes. The weird thing is there was another ExclusiveLock for the table "player", i.e. "player" got two locks, one RowExclusiveLock and one ExclusiveLock.

that query should be quite fast. is it part of a larger transaction? is there any possibility of multiple sessions/connections accessing the same player.id?

That's possible, but I think only one row will be locked for a while, but not thousands of locks for an hour. It's rare that thousands of users update the value at once.

it would be interesting to identify the process that issued the exclusive lock and determine what query/queries its made. if its not apparent in pg_stat_activity, perhaps enable logging of all DDL commands, and check the logs.

yeah, I've made the log_statement to "all" now. Previously, it only logged slow queries more than 50ms. I could know something from logs if it happens again (hope not).

if there's a lot of active queries (you ahve 800 connections)

select count(*),current_query from pg_stat_activity group by current_query order by count(*) desc;

that's helpful, thanks.

can help you make sense of them.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks, I've checked the "for update". No such queries there.

On Thu, Jul 14, 2011 at 15:36, Radoslaw Smogura <rsmogura@softperience.eu>wrote:

On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com> wrote:
It's a game server, and the queries are updating users' money, as normal.
The sql is like "UPDATE player SET money = money + 100 where id = 12345".
The locks were RowExclusiveLock for the table "player" and the indexes. The
weird thing is there was another ExclusiveLock for the table "player", i.e.
"player" got two locks, one RowExclusiveLock and one ExclusiveLock.
In the postgresql documentation
(http://www.postgresql.org/docs/8.4/static/explicit-locking.html), it's said
about the  Exclusive "This lock mode is not automatically acquired on user
tables by any PostgreSQL command."

You need to figure out what part of your app, or maybe a rogue
developer etc is throwing an exclusive lock.

On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com>wrote:

On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com>

wrote:

It's a game server, and the queries are updating users' money, as normal.
The sql is like "UPDATE player SET money = money + 100 where id = 12345".
The locks were RowExclusiveLock for the table "player" and the indexes.

The

weird thing is there was another ExclusiveLock for the table "player",

i.e.

"player" got two locks, one RowExclusiveLock and one ExclusiveLock.
In the postgresql documentation
(http://www.postgresql.org/docs/8.4/static/explicit-locking.html), it's

said

about the Exclusive "This lock mode is not automatically acquired on

user

tables by any PostgreSQL command."

You need to figure out what part of your app, or maybe a rogue
developer etc is throwing an exclusive lock.

Yeah, that's what I'm trying to do

On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com>
wrote:
It's a game server, and the queries are updating users' money, as
normal.
The sql is like "UPDATE player SET money = money + 100 where id =
12345".
The locks were RowExclusiveLock for the table "player" and the indexes.
The
weird thing is there was another ExclusiveLock for the table "player",
i.e.
"player" got two locks, one RowExclusiveLock and one ExclusiveLock.
In the postgresql documentation
(http://www.postgresql.org/docs/8.4/static/explicit-locking.html), it's
said
about the  Exclusive "This lock mode is not automatically acquired on
user
tables by any PostgreSQL command."

You need to figure out what part of your app, or maybe a rogue
developer etc is throwing an exclusive lock.

Yeah, that's what I'm trying to do

Cool. In your first post you said:

select pg_class.relname, pg_locks.mode, pg_locks.granted, pg_stat_activity.current_query, pg_stat_activity.query_start,
pg_stat_activity.xact_start as transaction_start, age(now(),pg_stat_activity.query_start) as query_age,
age(now(),pg_stat_activity.xact_start) as transaction_age, pg_stat_activity.procpid from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg_stat_activity.procpid and
substr(pg_class.relname,1,3) != 'pg_' order by query_start;

The only special thing I can find is that there were a lot ExclusiveLock, while it's normal the locks are
only AccessShareLock and RowExclusiveLock.

So what did / does current_query say when it's happening? If it says
you don't have access permission then run that query as root when it
happens again.

On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com>wrote:

On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com>
wrote:
It's a game server, and the queries are updating users' money, as
normal.
The sql is like "UPDATE player SET money = money + 100 where id =
12345".
The locks were RowExclusiveLock for the table "player" and the

indexes.

The
weird thing is there was another ExclusiveLock for the table "player",
i.e.
"player" got two locks, one RowExclusiveLock and one ExclusiveLock.
In the postgresql documentation
(http://www.postgresql.org/docs/8.4/static/explicit-locking.html),

it's

said
about the Exclusive "This lock mode is not automatically acquired on
user
tables by any PostgreSQL command."

You need to figure out what part of your app, or maybe a rogue
developer etc is throwing an exclusive lock.

Yeah, that's what I'm trying to do

Cool. In your first post you said:

select pg_class.relname, pg_locks.mode, pg_locks.granted,

pg_stat_activity.current_query, pg_stat_activity.query_start,

pg_stat_activity.xact_start as transaction_start,

age(now(),pg_stat_activity.query_start) as query_age,

age(now(),pg_stat_activity.xact_start) as transaction_age,

pg_stat_activity.procpid from pg_stat_activity,pg_locks left

outer join pg_class on (pg_locks.relation = pg_class.oid) where

pg_locks.pid=pg_stat_activity.procpid and

substr(pg_class.relname,1,3) != 'pg_' order by query_start;

The only special thing I can find is that there were a lot ExclusiveLock,

while it's normal the locks are

only AccessShareLock and RowExclusiveLock.

So what did / does current_query say when it's happening? If it says
you don't have access permission then run that query as root when it
happens again.

As I said, it's normal update like "UPDATE player SET money = money + 100
WHERE id=12345", but there are quite many

On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com>
wrote:
It's a game server, and the queries are updating users' money, as
normal.
The sql is like "UPDATE player SET money = money + 100 where id =
12345".
The locks were RowExclusiveLock for the table "player" and the
indexes.
The
weird thing is there was another ExclusiveLock for the table
"player",
i.e.
"player" got two locks, one RowExclusiveLock and one ExclusiveLock.
In the postgresql documentation
(http://www.postgresql.org/docs/8.4/static/explicit-locking.html),
it's
said
about the  Exclusive "This lock mode is not automatically acquired on
user
tables by any PostgreSQL command."

You need to figure out what part of your app, or maybe a rogue
developer etc is throwing an exclusive lock.

Yeah, that's what I'm trying to do

Cool.  In your first post you said:

select pg_class.relname, pg_locks.mode, pg_locks.granted,
pg_stat_activity.current_query, pg_stat_activity.query_start,
pg_stat_activity.xact_start as transaction_start,
age(now(),pg_stat_activity.query_start) as query_age,
age(now(),pg_stat_activity.xact_start) as transaction_age,
pg_stat_activity.procpid from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid) where
pg_locks.pid=pg_stat_activity.procpid and
substr(pg_class.relname,1,3) != 'pg_' order by query_start;

The only special thing I can find is that there were a lot
ExclusiveLock, while it's normal the locks are
only AccessShareLock and RowExclusiveLock.

So what did / does current_query say when it's happening?  If it says
you don't have access permission then run that query as root when it
happens again.

As I said, it's normal update like "UPDATE player SET money = money + 100
WHERE id=12345", but there are quite many

A regular update like that can't get a full exclusive lock by itself,
there'd have to be a previous query in the same transaction that took
out an explicit lock. Is it possible for you to set up query logging
such that you can track connections to see which one does that in the
future?

Were there more than 1 exclusive lock (now row exclusive, but just
plain exclusive)?

On Fri, Jul 15, 2011 at 10:05, Scott Marlowe <scott.marlowe@gmail.com>wrote:

On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com

wrote:

On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com>
wrote:
It's a game server, and the queries are updating users' money, as
normal.
The sql is like "UPDATE player SET money = money + 100 where id =
12345".
The locks were RowExclusiveLock for the table "player" and the
indexes.
The
weird thing is there was another ExclusiveLock for the table
"player",
i.e.
"player" got two locks, one RowExclusiveLock and one ExclusiveLock.
In the postgresql documentation
(http://www.postgresql.org/docs/8.4/static/explicit-locking.html),
it's
said
about the Exclusive "This lock mode is not automatically acquired

on

user
tables by any PostgreSQL command."

You need to figure out what part of your app, or maybe a rogue
developer etc is throwing an exclusive lock.

Yeah, that's what I'm trying to do

Cool. In your first post you said:

select pg_class.relname, pg_locks.mode, pg_locks.granted,
pg_stat_activity.current_query, pg_stat_activity.query_start,
pg_stat_activity.xact_start as transaction_start,
age(now(),pg_stat_activity.query_start) as query_age,
age(now(),pg_stat_activity.xact_start) as transaction_age,
pg_stat_activity.procpid from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid) where
pg_locks.pid=pg_stat_activity.procpid and
substr(pg_class.relname,1,3) != 'pg_' order by query_start;

The only special thing I can find is that there were a lot
ExclusiveLock, while it's normal the locks are
only AccessShareLock and RowExclusiveLock.

So what did / does current_query say when it's happening? If it says
you don't have access permission then run that query as root when it
happens again.

As I said, it's normal update like "UPDATE player SET money = money + 100
WHERE id=12345", but there are quite many

A regular update like that can't get a full exclusive lock by itself,
there'd have to be a previous query in the same transaction that took
out an explicit lock. Is it possible for you to set up query logging
such that you can track connections to see which one does that in the
future?

Yeah, and I also wonder when will an ExclusiveLock acquired.
I set up query logging after that, that'll be really big file.

Were there more than 1 exclusive lock (now row exclusive, but just
plain exclusive)?

There were many such locks (not row exclusive) updating different player id.

On Thu, Jul 14, 2011 at 8:28 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 10:05, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 01:13, Scott Marlowe
<scott.marlowe@gmail.com>
wrote:

On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com>
wrote:
It's a game server, and the queries are updating users' money, as
normal.
The sql is like "UPDATE player SET money = money + 100 where id =
12345".
The locks were RowExclusiveLock for the table "player" and the
indexes.
The
weird thing is there was another ExclusiveLock for the table
"player",
i.e.
"player" got two locks, one RowExclusiveLock and one
ExclusiveLock.
In the postgresql documentation
(http://www.postgresql.org/docs/8.4/static/explicit-locking.html),
it's
said
about the  Exclusive "This lock mode is not automatically acquired
on
user
tables by any PostgreSQL command."

You need to figure out what part of your app, or maybe a rogue
developer etc is throwing an exclusive lock.

Yeah, that's what I'm trying to do

Cool.  In your first post you said:

select pg_class.relname, pg_locks.mode, pg_locks.granted,
pg_stat_activity.current_query, pg_stat_activity.query_start,
pg_stat_activity.xact_start as transaction_start,
age(now(),pg_stat_activity.query_start) as query_age,
age(now(),pg_stat_activity.xact_start) as transaction_age,
pg_stat_activity.procpid from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid) where
pg_locks.pid=pg_stat_activity.procpid and
substr(pg_class.relname,1,3) != 'pg_' order by query_start;

The only special thing I can find is that there were a lot
ExclusiveLock, while it's normal the locks are
only AccessShareLock and RowExclusiveLock.

So what did / does current_query say when it's happening?  If it says
you don't have access permission then run that query as root when it
happens again.

As I said, it's normal update like "UPDATE player SET money = money +
100
WHERE id=12345", but there are quite many

A regular update like that can't get a full exclusive lock by itself,
there'd have to be a previous query in the same transaction that took
out an explicit lock.  Is it possible for you to set up query logging
such that you can track connections to see which one does that in the
future?

Yeah, and I also wonder when will an ExclusiveLock acquired.
I set up query logging after that, that'll be really big file.

Were there more than 1 exclusive lock (now row exclusive, but just
plain exclusive)?

There were many such locks (not row exclusive) updating different player id.

How many just plain exclusive locks were there?

On Fri, Jul 15, 2011 at 10:42, Scott Marlowe <scott.marlowe@gmail.com>wrote:

On Thu, Jul 14, 2011 at 8:28 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 10:05, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com

wrote:

On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:

On Fri, Jul 15, 2011 at 01:13, Scott Marlowe
<scott.marlowe@gmail.com>
wrote:

On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com>

wrote:

On Thu, Jul 14, 2011 at 10:35, John R Pierce <

pierce@hogranch.com>

wrote:
It's a game server, and the queries are updating users' money,

as

normal.
The sql is like "UPDATE player SET money = money + 100 where id

=

12345".
The locks were RowExclusiveLock for the table "player" and the
indexes.
The
weird thing is there was another ExclusiveLock for the table
"player",
i.e.
"player" got two locks, one RowExclusiveLock and one
ExclusiveLock.
In the postgresql documentation
(

http://www.postgresql.org/docs/8.4/static/explicit-locking.html),

it's
said
about the Exclusive "This lock mode is not automatically

acquired

on
user
tables by any PostgreSQL command."

You need to figure out what part of your app, or maybe a rogue
developer etc is throwing an exclusive lock.

Yeah, that's what I'm trying to do

Cool. In your first post you said:

select pg_class.relname, pg_locks.mode, pg_locks.granted,
pg_stat_activity.current_query, pg_stat_activity.query_start,
pg_stat_activity.xact_start as transaction_start,
age(now(),pg_stat_activity.query_start) as query_age,
age(now(),pg_stat_activity.xact_start) as transaction_age,
pg_stat_activity.procpid from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid) where
pg_locks.pid=pg_stat_activity.procpid and
substr(pg_class.relname,1,3) != 'pg_' order by query_start;

The only special thing I can find is that there were a lot
ExclusiveLock, while it's normal the locks are
only AccessShareLock and RowExclusiveLock.

So what did / does current_query say when it's happening? If it says
you don't have access permission then run that query as root when it
happens again.

As I said, it's normal update like "UPDATE player SET money = money +
100
WHERE id=12345", but there are quite many

A regular update like that can't get a full exclusive lock by itself,
there'd have to be a previous query in the same transaction that took
out an explicit lock. Is it possible for you to set up query logging
such that you can track connections to see which one does that in the
future?

Yeah, and I also wonder when will an ExclusiveLock acquired.
I set up query logging after that, that'll be really big file.

Were there more than 1 exclusive lock (now row exclusive, but just
plain exclusive)?

There were many such locks (not row exclusive) updating different player

id.

How many just plain exclusive locks were there?

There were 2519 RowExclusiveLock and 85 ExclusiveLock