Re: Exclusive Locks Taken on User Tables?
| От | Marc |
|---|---|
| Тема | Re: Exclusive Locks Taken on User Tables? |
| Дата | |
| Msg-id | 809128960711061132i45186c4fl67aff913cc59864f@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Exclusive Locks Taken on User Tables? (Richard Huxton <dev@archonet.com>) |
| Ответы |
Re: Exclusive Locks Taken on User Tables?
|
| Список | pgsql-general |
This is the query that I'm running to view locks:
I'm pretty sure this filters out transactionid lock types because I'm joining to pg_database and pg_class. Pls correct me if I'm wrong though.
SELECT pg_class.relname AS table,
pg_database.datname AS database,
transaction, pid, mode, granted
FROM pg_locks, pg_class, pg_database
WHERE pg_locks.relation = pg_class.oid
AND pg_locks.database = pg_database.oid
ORDER BY pg_class.relname, mode
pg_database.datname AS database,
transaction, pid, mode, granted
FROM pg_locks, pg_class, pg_database
WHERE pg_locks.relation = pg_class.oid
AND pg_locks.database = pg_database.oid
ORDER BY pg_class.relname, mode
I'm pretty sure this filters out transactionid lock types because I'm joining to pg_database and pg_class. Pls correct me if I'm wrong though.
On Nov 6, 2007 2:22 PM, Richard Huxton <dev@archonet.com> wrote:
Marc wrote:Hmm - are you sure?
> Hi,
>
> I'm seeing an "EXCLUSIVE" lock being taken on a table even though the
> documentation says that "This lock mode is not automatically acquired on
> user tables by any PostgreSQL command."Difficult to believe that's locking the whole table.
> My SQL is
> UPDATE users SET online = $1 where username = $2
>
> username is the PK on the users table.Fair enough.
> Other locks taken by the transaction are 1 RowExclusiveLock for the users
> table and 1 RowExclusiveLock on each of the 6 explict indexes on that table
> and another for the implicity users_pkey index.Doesn't sound right.
> The result of these locks is that concurrent calls for the same statement
> are being serialized because the ExclusiveLock being requested is not being
> granted.
Are you sure your ExclusiveLock isn't on a "transactionid" rather than a
"relation"? Every transaction has an exclusive lock on itself.
Are you sure subsequent transactions affecting that row aren't just
waiting to see if the original commits? That's normal behaviour.
--
Richard Huxton
Archonet Ltd
В списке pgsql-general по дате отправления: