Thread: Deadlocks in 7.4.x ...

Client is running 7.4.6 right now (we're looking at moving to 7.4.8 when 
we can get a suitable ops window for the upgrade), and is experiencing 
semi-regular (10-16 times a day) deadlocks, with the following 
information:

A little background:
  1) We do not do explicit row or table locking.  2) We use serialized transactions quite often.  3) All deadlocks are
fightingfor the same relation: 4335271, which is     the tsearch2 fulltext index on the articles table:
articles_idxft1_idx. 4) I have a trigger on the articles table which is executed before     insert or update.  It
updatesthe fulltext index column if any     changes are made to the relevant article columns.  This is the only
placewhere changes are made to the fulltext index column.
 

DETAIL:  Process 8122 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 11846.
Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042.
Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 8122.
----------------------------------
DETAIL:  Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 11846.
Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042.

I haven't had much experience (okay, I've yet to use it) with tsearch2, 
but according to 
http://www.postgresql.org/docs/8.0/interactive/limitations.html, GiST does 
have concurrency issues "The current implementation of GiST within 
PostgreSQL has some major limitations: GiST access is not concurrent"

Could this be what we are experiencing?

This is a wee bit vague, but I'm not sure what direction(s) to look at for 
Deadlocks themselves, so pointers/ideas would be much appreciated ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

> I haven't had much experience (okay, I've yet to use it) with tsearch2, 
> but according to 
> http://www.postgresql.org/docs/8.0/interactive/limitations.html, GiST 
> does have concurrency issues "The current implementation of GiST within 
> PostgreSQL has some major limitations: GiST access is not concurrent"
> 
> Could this be what we are experiencing?

All GiST indexes allow only 1 writer at a time.  Dunno if that's causing 
your headaches.  The other thing that causes deadlocks is using foreign 
keys...

Chris

"Marc G. Fournier" <scrappy@postgresql.org> writes:
> ...
>    3) All deadlocks are fighting for the same relation: 4335271, which is
>       the tsearch2 fulltext index on the articles table:
>       articles_idxft1_idx.

> DETAIL:  Process 8122 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 11846.
> Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042.
> Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 8122.
> ----------------------------------
> DETAIL:  Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process
11846.
> Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042.

Gist indexes require AccessExclusiveLock for any update, so the blocks
on 4335271 just indicate an index update attempt.  The "ShareLock on
transaction" lines suggest a block on a row that is updated or selected
FOR UPDATE.  It's hard to say more without more info.  Do you have any
related foreign keys, for instance?
        regards, tom lane