Thread: deadlock while re-indexing table

deadlock while re-indexing table

From
Dave Cramer
Date:
reindex table user_profile;
ERROR:  deadlock detected
DETAIL:  Process 32450 waits for AccessExclusiveLock on relation
194689112 of database 163880909; blocked by process 31236.
Process 31236 waits for AccessShareLock on relation 194689110 of
database 163880909; blocked by process 32450.
jnj=# select * from pg_class  where oid = 194689112;
       relname      | relnamespace | reltype | relowner | relam |
relfilenode | reltablespace | relpages |  reltuples  | reltoastrelid |
reltoastidxid | relhasindex | relisshared | relkind | relnatts |
relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids |
relhaspkey | relhasrules | relhassubclass | relfrozenxid | relacl |
reloptions
-------------------+--------------+---------+----------+-------
+-------------+---------------+----------+-------------+---------------
+---------------+-------------+-------------+---------+----------
+-----------+-------------+----------+----------+---------+------------
+------------+-------------+----------------+--------------+--------
+------------
  user_profile_pkey |         2200 |       0 |    16384 |   403 |
293905914 |             0 |     6004 | 2.18844e+06 |             0
|             0 | f           | f           | i       |        1
|         0 |           0 |        0 |        0 |       0 | f
| f          | f           | f              |            0 |        |
(1 row)

jnj=# select * from pg_class  where oid = 194689110;
         relname        | relnamespace | reltype | relowner | relam |
relfilenode | reltablespace | relpages |  reltuples  | reltoastrelid |
reltoastidxid | relhasindex | relisshared | relkind | relnatts |
relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids |
relhaspkey | relhasrules | relhassubclass | relfrozenxid | relacl |
reloptions
-----------------------+--------------+---------+----------+-------
+-------------+---------------+----------+-------------+---------------
+---------------+-------------+-------------+---------+----------
+-----------+-------------+----------+----------+---------+------------
+------------+-------------+----------------+--------------+--------
+------------
  user_profile_name_key |         2200 |       0 |    16384 |   403
|   293905879 |             0 |     8746 | 2.18843e+06 |             0
|             0 | f           | f           | i       |        1
|         0 |           0 |        0 |        0 |       0 | f
| f          | f           | f              |            0 |        |

This is 100% repeatable ....

Dave

Re: deadlock while re-indexing table

From
Alvaro Herrera
Date:
Dave Cramer wrote:
> reindex table user_profile;
> ERROR:  deadlock detected
> DETAIL:  Process 32450 waits for AccessExclusiveLock on relation
> 194689112 of database 163880909; blocked by process 31236.
> Process 31236 waits for AccessShareLock on relation 194689110 of
> database 163880909; blocked by process 32450.

I don't find this very surprising ... I would suggest using "reindex
index" for each index instead.  I'm not sure if REINDEX TABLE is
supposed to be deadlock-free.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: deadlock while re-indexing table

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Dave Cramer wrote:
>> reindex table user_profile;
>> ERROR:  deadlock detected
>> DETAIL:  Process 32450 waits for AccessExclusiveLock on relation
>> 194689112 of database 163880909; blocked by process 31236.
>> Process 31236 waits for AccessShareLock on relation 194689110 of
>> database 163880909; blocked by process 32450.

> I don't find this very surprising ... I would suggest using "reindex
> index" for each index instead.  I'm not sure if REINDEX TABLE is
> supposed to be deadlock-free.

It's not guaranteed to be so, but I'd think simple cases would be
okay.  What's that other process doing?

            regards, tom lane

Re: deadlock while re-indexing table

From
Alvaro Herrera
Date:
Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:

> > I don't find this very surprising ... I would suggest using "reindex
> > index" for each index instead.  I'm not sure if REINDEX TABLE is
> > supposed to be deadlock-free.
>
> It's not guaranteed to be so, but I'd think simple cases would be
> okay.

Can we rework REINDEX TABLE so that it processes each index on its own
transaction?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: deadlock while re-indexing table

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Can we rework REINDEX TABLE so that it processes each index on its own
> transaction?

It still wouldn't be guaranteed deadlock-free.  There might be fewer
cases, but whether it would help Dave's particular case is just
speculation when we don't know what that case is.

            regards, tom lane

Re: deadlock while re-indexing table

From
Gregory Stark
Date:
"Dave Cramer" <pg@fastcrypt.com> writes:

> reindex table user_profile;
> ERROR:  deadlock detected
> DETAIL:  Process 32450 waits for AccessExclusiveLock on relation  194689112 of
> database 163880909; blocked by process 31236.
> Process 31236 waits for AccessShareLock on relation 194689110 of  database
> 163880909; blocked by process 32450.

And what was process 31236 doing?

Are you running REINDEX TABLE on the same table from two different sessions?
Or are there other transactions running which call LOCK TABLE on this table?

Also, have you done other queries in this same transaction? Or other DDL in
other transactions, especially in combination with DML earlier.

Generally what frequently causes this is upgrading locks. So for example if
you do normal DML which takes a share lock, then in the same transaction try
to do DDL against the same table which requires an exclusive lock, then you'll
be at risk of deadlocks when other transactions try to do the same thing.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Re: deadlock while re-indexing table

From
Dave Cramer
Date:
On 12-Feb-08, at 10:37 AM, Tom Lane wrote:

> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Dave Cramer wrote:
>>> reindex table user_profile;
>>> ERROR:  deadlock detected
>>> DETAIL:  Process 32450 waits for AccessExclusiveLock on relation
>>> 194689112 of database 163880909; blocked by process 31236.
>>> Process 31236 waits for AccessShareLock on relation 194689110 of
>>> database 163880909; blocked by process 32450.
>
>> I don't find this very surprising ... I would suggest using "reindex
>> index" for each index instead.  I'm not sure if REINDEX TABLE is
>> supposed to be deadlock-free.
>
> It's not guaranteed to be so, but I'd think simple cases would be
> okay.  What's that other process doing?
>
The other process is inserting into the user_profile table.

Dave
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq


Re: deadlock while re-indexing table

From
Gregory Stark
Date:
"Dave Cramer" <pg@fastcrypt.com> writes:

> On 12-Feb-08, at 10:37 AM, Tom Lane wrote:
>
>> Alvaro Herrera <alvherre@commandprompt.com> writes:
>>> Dave Cramer wrote:
>>>> reindex table user_profile;
>>>> ERROR:  deadlock detected
>>>> DETAIL:  Process 32450 waits for AccessExclusiveLock on relation
>>>> 194689112 of database 163880909; blocked by process 31236.
>>>> Process 31236 waits for AccessShareLock on relation 194689110 of
>>>> database 163880909; blocked by process 32450.
>>
>>> I don't find this very surprising ... I would suggest using "reindex
>>> index" for each index instead.  I'm not sure if REINDEX TABLE is
>>> supposed to be deadlock-free.
>>
>> It's not guaranteed to be so, but I'd think simple cases would be
>> okay.  What's that other process doing?
>>
> The other process is inserting into the user_profile table.

Hm. This shouldn't be enough to cause a deadlock. Both inserts and reindex use
the same method to get the list of indexes which returns them in the same
order.

Did either transaction do anything else in the same transaction previously?

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

Re: deadlock while re-indexing table

From
Dave Cramer
Date:
On 12-Feb-08, at 1:02 PM, Gregory Stark wrote:

> "Dave Cramer" <pg@fastcrypt.com> writes:
>
>> On 12-Feb-08, at 10:37 AM, Tom Lane wrote:
>>
>>> Alvaro Herrera <alvherre@commandprompt.com> writes:
>>>> Dave Cramer wrote:
>>>>> reindex table user_profile;
>>>>> ERROR:  deadlock detected
>>>>> DETAIL:  Process 32450 waits for AccessExclusiveLock on relation
>>>>> 194689112 of database 163880909; blocked by process 31236.
>>>>> Process 31236 waits for AccessShareLock on relation 194689110 of
>>>>> database 163880909; blocked by process 32450.
>>>
>>>> I don't find this very surprising ... I would suggest using
>>>> "reindex
>>>> index" for each index instead.  I'm not sure if REINDEX TABLE is
>>>> supposed to be deadlock-free.
>>>
>>> It's not guaranteed to be so, but I'd think simple cases would be
>>> okay.  What's that other process doing?
>>>
>> The other process is inserting into the user_profile table.
>
> Hm. This shouldn't be enough to cause a deadlock. Both inserts and
> reindex use
> the same method to get the list of indexes which returns them in the
> same
> order.
>
> Did either transaction do anything else in the same transaction
> previously?
>
It would appear that the insert is running before the reindex starts.
Other than that I can't tell yet what was done in other transactions.

Dave
> --
>  Gregory Stark
>  EnterpriseDB          http://www.enterprisedb.com
>  Ask me about EnterpriseDB's PostGIS support!


Re: deadlock while re-indexing table

From
Tom Lane
Date:
Dave Cramer <pg@fastcrypt.com> writes:
>>> The other process is inserting into the user_profile table.
>>
>> Did either transaction do anything else in the same transaction
>> previously?
>>
> It would appear that the insert is running before the reindex starts.

That's not possible --- if it had been, the insert would already have
RowExclusiveLock on the table, which would have blocked the reindex
from acquiring ShareLock on the table.  The reindex must already have
that, since it's trying to acquire AccessExclusiveLock on one of the
indexes, so there can't be any active inserts on the table.

I suspect the other process must be doing a series of selects in one
transaction that use indexes of the table in some random order, but
that's just an educated guess at this point.

BTW, what PG version is this?

            regards, tom lane

Re: deadlock while re-indexing table

From
Dave Cramer
Date:
On 12-Feb-08, at 5:05 PM, Tom Lane wrote:

> Dave Cramer <pg@fastcrypt.com> writes:
>>>> The other process is inserting into the user_profile table.
>>>
>>> Did either transaction do anything else in the same transaction
>>> previously?
>>>
>> It would appear that the insert is running before the reindex starts.
>
> That's not possible --- if it had been, the insert would already have
> RowExclusiveLock on the table, which would have blocked the reindex
> from acquiring ShareLock on the table.  The reindex must already have
> that, since it's trying to acquire AccessExclusiveLock on one of the
> indexes, so there can't be any active inserts on the table.
>
> I suspect the other process must be doing a series of selects in one
> transaction that use indexes of the table in some random order, but
> that's just an educated guess at this point.
>
> BTW, what PG version is this?
>
8.2.5


Dave
>