Обсуждение: Deadlock detected

Hello,

  I am getting a few of these errors in my web logs and didn't know what I could do to stop it.

NOTICE:  Deadlock detected -- See the lock(l) manual page for a possible cause.

Error in query "UPDATE <table> SET <field> = <value> WHERE <field2> = <value2>" : ERROR:  WaitOnLock: error on wakeup -
Abortingthis transaction 

Is this due to the hash index on field2 or due to the has index in general?  I read in the mailing archive back in '98
(referringto v6.5) that hash index were bad, is this still the case and should I switch to btree?  I have tried to wrap
theupdate statement with BEGIN; <statement>; END; and get the same results. 

System:
  Linux: 2.2.18
  PostgreSQL: 7.0.3
  gcc: 2.95.2
  libc-2.1.3

Thanks,

Brian

BTW, on a different table I get this warning ever time I vacuum the database. It doesn't seem to cause a problem, but
didn'tknow how to get rid of it.   

NOTICE:  Index <indexname>: NUMBER OF INDEX' TUPLES (214) IS NOT THE SAME AS HEAP' (215).
    Recreate the index.

Brian J. France wrote:

 >
 > BTW, on a different table I get this warning ever time I vacuum the
database. It doesn't seem to cause a problem, but didn't know how to get
rid of it.
 >
 > NOTICE:  Index <indexname>: NUMBER OF INDEX' TUPLES (214) IS NOT THE
SAME AS HEAP' (215).
 >     Recreate the index.

I had the same problem for months and just figured it out: I had a null
entry in one of the index's columns. Once I removed the null entry that
notice went away.

---Maurice

"Brian J. France" <postgresql@firehawksystems.com> writes:
>   I am getting a few of these errors in my web logs and didn't know what I could do to stop it.

> NOTICE:  Deadlock detected -- See the lock(l) manual page for a possible cause.

> Error in query "UPDATE <table> SET <field> = <value> WHERE <field2> = <value2>" : ERROR:  WaitOnLock: error on wakeup
-Aborting this transaction 

> Is this due to the hash index on field2 or due to the has index in general?

Don't use hash indexes for concurrent applications.  I don't really know
of any reason for preferring a hash index over a btree index in any case.

            regards, tom lane

Hi Tom,


"Brian J. France" <postgresql@firehawksystems.com> writes:
    I am getting a few of these errors in my web logs and didn't know what I could do to stop it.
    NOTICE:  Deadlock detected -- See the lock(l) manual page for a possible cause.
      Error in query "UPDATE <table> SET <field> = <value> WHERE <field2> = <value2>" :
ERROR: WaitOnLock: error on wakeup - Aborting this transaction 
        Is this due to the hash index on field2 or due to the has index in general?
          Don't use hash indexes for concurrent applications.  I don't really knowof any reason for preferring a hash
indexover a btree index in any case.            regards, tom lane 

Could you expand a little on the subject of Hash Vs BTree indexes? And in
particular "Don't use hash indexes for concurrent applications".

I posted a question about deadlocks a week or two ago and I was advised to upgrade to 7.0.3 (from 7.0.2).
I did, but I still get a few deadlocks (i.e. all the backends eventually remain locked in requests).

I am using a lot of Hash indexes because they usually provide faster access
to a specific record than binary trees (O Vs OlogN). Most of my requests
simply lookup a record based on some specific account or transaction ID.

If using BTree instead of Hash indexes does not affect performance and solves my deadlock problem,  please let me
know!

Thanks.

--Maurice