concurrent index builds unneeded lock?

From: Theo Schlossnagle <jesus(at)omniti(dot)com>
To: PostgreSQL-development Development <pgsql-hackers(at)postgresql(dot)org>
Cc: Theo Schlossnagle <jesus(at)omniti(dot)com>
Subject: concurrent index builds unneeded lock?
Date: 2009-07-11 04:02:27
Message-ID: DC895D03-8812-4DA5-816E-E13E1F6D764A@omniti.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

We just ran into a case where we were performing two concurrent index
builds on two different tables in two different schemas in the same
database (no relational constraints between them).

One of the index builds locked on the other.

The first index build started...
The second index build started...
The first one locked on the second one....
The second one finished...
The first one was allows to continue and finish.

quux=# select * from pg_locks where pid IN (25264, 20108);
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction |
pid | mode | granted
------------+----------+----------+------+-------+------------
+---------------+---------+-------+----------+--------------------
+-------+--------------------------+---------
relation | 16385 | 25852 | | |
| | | | | 9/3041 |
20108 | RowExclusiveLock | t
relation | 16385 | 25861 | | |
| | | | | 1/15735 |
25264 | RowExclusiveLock | t
relation | 16385 | 16421 | | |
| | | | | 9/3041 |
20108 | ShareUpdateExclusiveLock | t
virtualxid | | | | | 9/3041
| | | | | 9/3041 |
20108 | ExclusiveLock | t
virtualxid | | | | | 1/15735
| | | | | 1/15735 |
25264 | ExclusiveLock | t
virtualxid | | | | | 9/3041
| | | | | 1/15735 |
25264 | ShareLock | f
relation | 16385 | 16528 | | |
| | | | | 1/15735 |
25264 | ShareUpdateExclusiveLock | t
(7 rows)

Reading the comments in the concurrent index build code, it seems that
in prep for phase 3 of the index build it looks for any open txns that
could feasibly see deleted tuples prior to the snap.

I would think it would be txns that would be reading that table, but
I'm thinking it is a bit to aggressive. Am I reading the code wrong
there? I'm thinking it should be more selective about vxids it
chooses to block on. I'd expect it to block on vxids touching the
same table only.

Thoughts?

--
Theo Schlossnagle
http://omniti.com/is/theo-schlossnagle
p: +1.443.325.1357 x201 f: +1.410.872.4911

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-07-11 04:12:31 Re: concurrent index builds unneeded lock?
Previous Message D'Arcy J.M. Cain 2009-07-11 02:37:00 Re: Maintenance Policy?