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 - Aborting this 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 (referring to v6.5) that hash index were bad, is this still the case and should I switch to btree? I have tried to wrap the update 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'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.
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
<html><head></head><body>Hi Tom,<br>
<br>
<br>
<blockquote type="cite" cite="mid:4501.987180015@sss.pgh.pa.us"><pre wrap="">"Brian J. France" <a class="moz-txt-link-rfc2396E" href="mailto:postgresql@firehawksystems.com"><postgresql@firehawksystems.com></a> writes:<br></pre>
<blockquote type="cite"><pre wrap=""> I am getting a few of these errors in my web logs and didn't know what I could do to stop it.<br></pre></blockquote>
<blockquote type="cite"><pre wrap="">NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause.<br></pre></blockquote>
<blockquote type="cite"><pre wrap="">Error in query "UPDATE <table> SET <field> = <value> WHERE <field2> = <value2>" : ERROR: WaitOnLock: error on wakeup - Aborting this transaction<br></pre></blockquote>
<blockquote type="cite"><pre wrap="">Is this due to the hash index on field2 or due to the has index in general?<br></pre></blockquote>
<pre wrap=""><!----><br>Don't use hash indexes for concurrent applications. I don't really know<br>of any reason for preferring a hash index over a btree index in any case.<br><br> regards, tom lane<br></pre>
</blockquote>
Could you expand a little on the subject of Hash Vs BTree indexes? And in
particular "Don't use hash indexes for concurrent applications".<br>
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).
<br>
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.
<br>
If using BTree instead of Hash indexes does not affect performance and solves my deadlock problem, please let me know!<br>
<br>
Thanks.<br>
<br>
--Maurice<br>
</body></html>