X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2840ED6FEB for ; Mon, 29 Aug 2005 14:34:34 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44641-06 for ; Mon, 29 Aug 2005 17:34:32 +0000 (GMT) Received: from 217-20-115-93.internetserviceteam.com (217-20-115-93.internetserviceteam.com [217.20.115.93]) by svr1.postgresql.org (Postfix) with ESMTP id 61498D6E2C for ; Mon, 29 Aug 2005 14:34:30 -0300 (ADT) Received: from [192.168.1.122] (26.Red-83-46-168.pooles.rima-tde.net [83.46.168.26]) by 217-20-115-93.internetserviceteam.com (Postfix) with ESMTP id D5AC23963F; Mon, 29 Aug 2005 18:45:55 +0200 (CEST) Message-ID: <4313471D.7020404@mediaroot.de> Date: Mon, 29 Aug 2005 18:34:21 +0100 From: Aldor User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: olly@lfix.co.uk Cc: pgsql-admin@postgresql.org Subject: Re: Query failed: ERROR: deadlock detected References: <43132ED4.9000107@mediaroot.de> <1125336357.2589.8.camel@linda> In-Reply-To: <1125336357.2589.8.camel@linda> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 required=5 tests=[none] X-Spam-Level: X-Archive-Number: 200508/372 X-Sequence-Number: 18691 thanks for the explanation... i found the mistake in a new pl/pgsql function i wrote before this error occured;-) Oliver Elphick wrote: > On Mon, 2005-08-29 at 16:50 +0100, Aldor wrote: > >>PostgreSQL 8.0.3: Any idea what this is? >> >>--- >>Warning: pg_query(): Query failed: ERROR: deadlock detected >>DETAIL: Process 8835 waits for ShareLock on transaction 193588236; >>blocked by process 8834. >>Process 8834 waits for ShareLock on transaction 193588228; blocked by >>process 8835 >>--- >> >>I have never seen such a log entry until now. > > > You have two transactions locking each other out. > > For example: > trans A: begin; > trans B: begin; > trans A: select * from table_a for update; -- locks table_a > trans B: select * from table_b for update; -- locks table_b > trans A: select * from table_b for update; -- blocked by trans B's lock > trans B: select * from table_a for update; -- blocked by trans A's lock > > Since the two transactions are blocking each other, the locks will never > be released by the transactions that are holding them. After a preset > interval (about 1 second by default) a deadlock is detected and reported > with this error message and one of the transactions is aborted. > > See manual section 12.3.3 in the 8.0 documentation. >