Postgres Pro
Downloads
Home   >   mailing lists

Thread: Why this lock?

Why this lock?

From
Johann Spies
Date:
I have a long-running query (running now for more than 6 days already  (process 17434).  It involves three tables of which one contains XML-data.

On another, unrelated table with  30718567 records, I ran a query to create an index on a field.  This morning I cancelled this process because it did not finish after 5 days.

I then did a "vacuum analyze" on that table and rerun the query (process 9732) to create the index.  It soon stalled again and the following result shows that proces 17434 is blocking it:

locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid  |   mode    | granted | fastpath | virtualtransaction |  pid  |     mode      | granted
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------+---------+----------+--------------------+-------+---------------+---------
 virtualxid |          |          |      |       | 6/24891    |               |         |       |          | 7/27906            | 9732 | ShareLock | f       | f        | 6/24891            | 17434 | ExclusiveLock | t


Now my questions:

What would cause such a lock?
What can I do to remove the lock without stopping the long-running process which started before the query to create the index?  I suppose I just have to wait for the first process to finish...

Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: Why this lock?

From
Merlin Moncure
Date:
On Tue, Aug 25, 2015 at 8:33 AM, Johann Spies <johann.spies@gmail.com> wrote:
> I have a long-running query (running now for more than 6 days already
> (process 17434).  It involves three tables of which one contains XML-data.
>
> On another, unrelated table with  30718567 records, I ran a query to create
> an index on a field.  This morning I cancelled this process because it did
> not finish after 5 days.
>
> I then did a "vacuum analyze" on that table and rerun the query (process
> 9732) to create the index.  It soon stalled again and the following result
> shows that proces 17434 is blocking it:
>
> locktype  | database | relation | page | tuple | virtualxid | transactionid
> | classid | objid | objsubid | virtualtransaction | pid  |   mode    |
> granted | fastpath | virtualtransaction |  pid  |     mode      | granted
>
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------+---------+----------+--------------------+-------+---------------+---------
>  virtualxid |          |          |      |       | 6/24891    |
> |         |       |          | 7/27906            | 9732 | ShareLock | f
> | f        | 6/24891            | 17434 | ExclusiveLock | t
>
>
> Now my questions:
>
> What would cause such a lock?
> What can I do to remove the lock without stopping the long-running process
> which started before the query to create the index?  I suppose I just have
> to wait for the first process to finish...

creating and index requires exclusive access.  did you try the
concurrent variant?

merlin

Re: Why this lock?

From
Johann Spies
Date:


On 25 August 2015 at 15:52, Merlin Moncure <mmoncure@gmail.com> wrote:
h...

creating and index requires exclusive access.  did you try the
concurrent variant?

Yes. The one which I stopped after 5 days, was running concurrently.  There was a similar lock involved.

Regards
Johann

--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: Why this lock?

From
Tom Lane
Date:
Johann Spies <johann.spies@gmail.com> writes:
> On 25 August 2015 at 15:52, Merlin Moncure <mmoncure@gmail.com> wrote:
>> creating and index requires exclusive access.  did you try the
>> concurrent variant?

> Yes. The one which I stopped after 5 days, was running concurrently.  There
> was a similar lock involved.

That lock type is used by CREATE INDEX CONCURRENTLY when it has to wait
out another transaction.  There is no way around this, it's an inherent
part of that algorithm.

http://www.postgresql.org/docs/9.4/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

            regards, tom lane