Re: Concurrency issues

Clarence Gardner <clarence(at)silcom(dot)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