Thread: Deadlock with REINDEX TABLE
Hi, I'm hoping some one can help me figure out how I ended up with a deadlock while running my reindex script last night. It basically partitions our table set and goes through each group of tables in parallel running REINDEX TABLE on each table in that group with each group having its own log. Anyways, I woke up this moring to find this in one of the logs:
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.
How did this happen? Does REINDEX TABLE not take out the locks for each of the table's indexes at the same time (there was another index on the table but I'm not sure whether or not it was reindexed or not)? Do REINDEX TABLE and UPDATE not take out their locks in the same order (i.e. lock table, lock indexes)?
erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)
Erik Jones <erik@myemma.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?
regards, tom lane
On Mar 22, 2007, at 1:01 PM, Tom Lane wrote:
Erik Jones <erik@myemma.com> writes:Mar 21 19:36:18 [info] User Info: REINDEX TABLEemma_messages_email_queue; [nativecode=ERROR: deadlock detectedDETAIL: Process 12912 waits for AccessExclusiveLock on relation138763808 of database 16384; blocked by process 15217.Process 15217 waits for RowExclusiveLock on relation 17111 ofdatabase 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'massuming it was an update due being a RowExclusive lock.This looks like a lock-upgrade deadlock to me. REINDEX TABLE takes onlyShareLock on the table itself, but needs AccessExclusiveLock on eachindex successively. What I'm guessing happened is that the conflictingtransaction did first a SELECT and then an UPDATE on the table; theSELECT would take AccessShare (which it could hold concurrently withthe reindex's ShareLock) and then the UPDATE would block because itsRowExclusiveLock request has to wait for the ShareLock to release.What's not entirely clear though is why the conflicting transaction hadany lock on the index at this point. The UPDATE wouldn't have acquiredindex locks yet. The only idea that comes to mind is that the SELECTwas 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@myemma.com>
software developer
615-296-0838
emma(r)