Re: Deadlock with REINDEX TABLE

On Mar 22, 2007, at 1:01 PM, Tom Lane wrote:

> Erik Jones <erik(at)myemma(dot)com> writes:
>> Mar 21 19:36:18 [info] User Info: REINDEX TABLE
>> emma_messages_email_queue; [nativecode=ERROR: deadlock detected
>> DETAIL: Process 12912 waits for AccessExclusiveLock on relation
>> 138763808 of database 16384; blocked by process 15217.
>> Process 15217 waits for RowExclusiveLock on relation 17111 of
>> database 16384; blocked by process 12912.]
>
>> Relation 138763808 is the primary key index on the table relation
>> 17111.
>> Proc 12912 is the REINDEX and proc 15217 is the contending query, I'm
>> assuming it was an update due being a RowExclusive lock.
>
> This looks like a lock-upgrade deadlock to me. REINDEX TABLE takes
> only
> ShareLock on the table itself, but needs AccessExclusiveLock on each
> index successively. What I'm guessing happened is that the
> conflicting
> transaction did first a SELECT and then an UPDATE on the table; the
> SELECT would take AccessShare (which it could hold concurrently with
> the reindex's ShareLock) and then the UPDATE would block because its
> RowExclusiveLock request has to wait for the ShareLock to release.
>
> What's not entirely clear though is why the conflicting transaction
> had
> any lock on the index at this point. The UPDATE wouldn't have
> acquired
> index locks yet. The only idea that comes to mind is that the SELECT
> was actually a cursor that was still open at the time of the
> UPDATE ...
> does your app do things like that?

Alas, the guy who wrote most of the app code that works with the
table in question is on vacation so the only answer I can give right
away is "Maybe, but not likely...". Until I can know for sure, I'll
just make sure to only reindex that table during off hours when the
likelihood of this happening again is negligible.

erik jones <erik(at)myemma(dot)com>
software developer
615-296-0838
emma(r)