Thread: Hash Indexes Causing Deadlock Notices

Hi,

I have been experimenting with the Apache Benchmarking Tool ( ab ) to
give my demo web site a bit of a hiding. The database backend is
Postgresql 7.1 ( dev from 25/09/00) .

I noticed that a large number of log notice messages announcing a
deadlock being detected together with  transactions being aborted (and
suggesting that I read the lock manual entry).   I am a bit confused
about the deadlock as I use select...for update to lock rows and process
all tables in the same order ( which SHOULD prevent any deadlocks...)

The tables impacted in the benchmark had HASH indexes. ( only = scans
were being performed, it seemed a good idea at the time ..). Recreating
these indexes as BTREE made all the messages go away. ( and produced a
better request/s result ! )

I am not sure if this issue with HASH indexes occurs in the production
sources :-). I will attempt to replicate if anybody is interested.

Mark

mark <markir@i4free.co.nz> writes:
> I noticed that a large number of log notice messages announcing a
> deadlock being detected together with  transactions being aborted

> The tables impacted in the benchmark had HASH indexes. ( only = scans
> were being performed, it seemed a good idea at the time ..). Recreating
> these indexes as BTREE made all the messages go away. ( and produced a
> better request/s result ! )

IIRC, hash indexes use per-page locking that is not guaranteed deadlock
free.  It's also bad for concurrency even when you don't get a deadlock.

The btree index type is by far the best choice we offer for
high-concurrency applications.  Perhaps at some point someone will
be motivated to improve the other index types, but right now they
are definitely poor stepchildren ...

            regards, tom lane