Thread: Concurrency issues

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@netlojix.com

Clarence Gardner <clarence@silcom.com> writes:
> 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

No it doesn't; note the granted=f in row 4.  What that row shows is
that Process 2 is waiting for Process 1 to commit its transaction.
It does not *have* that lock, it is *waiting* for it.

Cross-transaction waits are implemented by having each transaction first
grab ExclusiveLock on its transaction ID (rows 1 and 7 here), which it
will hold till it commits.  Then if any transaction needs to wait for
another, it tries to grab ShareLock on that other transaction's ID,
which puts it to sleep until the original ExclusiveLock is released ---
meaning the other xact has completed.

> 5) Have I missed some documentation somewhere?

I'm not sure that the use of transaction-ID locks is documented anywhere
in the SGML docs.  Feel free to contribute some text ...

            regards, tom lane

On Sun, 5 Jan 2003, Tom Lane wrote:

> No it doesn't; note the granted=f in row 4.  What that row shows is
> that Process 2 is waiting for Process 1 to commit its transaction.
> It does not *have* that lock, it is *waiting* for it.

Thanks, Tom. You answered all four questions at once. Efficient!