Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 611939FB693 for ; Mon, 12 Feb 2007 15:22:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 71886-06 for ; Mon, 12 Feb 2007 15:22:14 -0400 (AST) X-Greylist: delayed 00:22:47.13117 by SQLgrey-1.7.4 Received: from spoetnik.xs4all.nl (spoetnik.xs4all.nl [213.84.24.58]) by postgresql.org (Postfix) with ESMTP id 0804A9FB698 for ; Mon, 12 Feb 2007 15:22:08 -0400 (AST) Received: by spoetnik.xs4all.nl (Postfix, from userid 1000) id 83D901CC66; Mon, 12 Feb 2007 19:59:19 +0100 (CET) Date: Mon, 12 Feb 2007 19:59:19 +0100 From: Reinoud van Leeuwen To: pgsql-sql@postgresql.org Subject: Re: [ADMIN] Deadlock on transaction Message-ID: <20070212185919.GG677@spoetnik.xs4all.nl> Mail-Followup-To: pgsql-sql@postgresql.org References: <55c095e90702120943v2909044eo91445b0eda3958c7@mail.gmail.com> <1171302488.3565.15.camel@state.g2switchworks.com> <55c095e90702121008u6d3d27c0m16e52d63f97c3e7d@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <55c095e90702121008u6d3d27c0m16e52d63f97c3e7d@mail.gmail.com> User-Agent: Mutt/1.4.2.1i X-message-flag: Warning! The sender of this mail thinks you should use a more secure email client. X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50 X-Spam-Level: X-Archive-Number: 200702/150 X-Sequence-Number: 27484 On Mon, Feb 12, 2007 at 03:08:27PM -0300, Ezequias Rodrigues da Rocha wrote: > I mean really deadlock. Other transactions can't access the database until > the main transaction is complete. What you describe sounds like a blocking lock: one transaction uses a resource and another has to wait until it is not locked anymore A deadlock is a special case: two transactions both need resources A and B. transaction 1 locks A, and the next moment transaction 2 locks B. Now transaction 1 waits for 2 to release B, but at the same time 2 waits for 1 to release A. This is called a deadlock, or circulair lock. Postgresql automatically detects such a situation and rolls one of them back. The client recieves a 'restartable database error' and that is exactly true: ususally another try will work. One method of fixing deadlocks is to make sure all your code accesses the database in the same order. Alphabetically is easiest to remember... -- __________________________________________________ "Nothing is as subjective as reality" Reinoud van Leeuwen reinoud.v@n.leeuwen.net http://www.xs4all.nl/~reinoud __________________________________________________