Concurrency issues

From: Clarence Gardner <clarence(at)silcom(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Concurrency issues
Date: 2003-01-06 02:49:31
Message-ID: Pine.LNX.4.44.0301051847340.1399-100000@liberty.sba2.netlojix.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've recently moved a system from Oracle to Postgre, and I'm having
locking problems.

Here's a simple scenario, with a one-row table:
Process 1 does the following:
netbill=> begin;
BEGIN
netbill=> update t1 set f1=2;
UPDATE 1
netbill=>

and at this point, the locks are:
netbill=> select * from pg_locks order by pid;
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+------------------+---------
| | 422665 | 28283 | ExclusiveLock | t
17715590 | 17203 | | 28283 | AccessShareLock | t
17715590 | 17203 | | 28283 | RowExclusiveLock | t

Now Process 2 does the same thing:
netbill=> begin;
BEGIN
netbill=> update t1 set f1=3;
(blocks)

And now, the locks:
netbill=> select * from pg_locks order by pid;
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+------------------+---------
| | 422665 | 28283 | ExclusiveLock | t
17715590 | 17203 | | 28283 | AccessShareLock | t
17715590 | 17203 | | 28283 | RowExclusiveLock | t
| | 422665 | 28284 | ShareLock | f
17715590 | 17203 | | 28284 | AccessShareLock | t
17715590 | 17203 | | 28284 | RowExclusiveLock | t
| | 422670 | 28284 | ExclusiveLock | t

Some questions:
1) What exactly is the transaction id? I see that not only does Process 2
have two different xid's (in addition to the nulls), but that one of
them is shared with process 1. This seems odd to the casual observer.
2) What resource is the blocking lock attempting to lock? It has no
relation or database value.
3) According to http://www.postgresql.org/idocs/index.php?locking-tables.html,
all of those locking modes are table-level locks. Am I silly in expecting
that the blocked Process 2 should be waiting on a row-level lock?
4) What is it that both processes have an ExclusiveLock on?

5) Have I missed some documentation somewhere?

Thanks for any help.

clarence(at)netlojix(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albert Cervera Areny 2003-01-06 03:40:31 Fwd: Stock update like application
Previous Message Jean-Christian Imbeault 2003-01-06 02:21:11 serverlog problems