X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1DD7D476B28 for ; Fri, 16 May 2003 12:08:20 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 75283-01 for ; Fri, 16 May 2003 12:08:17 -0400 (EDT) Received: from ms-smtp-01.texas.rr.com (ms-smtp-01.texas.rr.com [24.93.36.229]) by postgresql.org (Postfix) with ESMTP id 96655476B24 for ; Fri, 16 May 2003 12:08:17 -0400 (EDT) Received: from spaceship.com (cs24243223-175.austin.rr.com [24.243.223.175]) by ms-smtp-01.texas.rr.com (8.12.5/8.12.2) with ESMTP id h4GG8D8b002743; Fri, 16 May 2003 11:08:22 -0500 (CDT) Message-ID: <3EC50CEB.9080202@spaceship.com> Date: Fri, 16 May 2003 11:08:11 -0500 From: Matt Mello User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4a) Gecko/20030401 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-sql@postgresql.org Subject: Re: "deadlock detected" documentation References: <3EB82DA1.7020809@spaceship.com> <24809.1052282819@sss.pgh.pa.us> <3EC32935.5040005@spaceship.com> <7737.1053003625@sss.pgh.pa.us> <3EC3C239.7070708@spaceship.com> <10096.1053017383@sss.pgh.pa.us> <3EC3C77A.3010006@spaceship.com> <10286.1053018824@sss.pgh.pa.us> <3EC3E53C.9070800@spaceship.com> In-Reply-To: <3EC3E53C.9070800@spaceship.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/347 X-Sequence-Number: 13512 Okay, I finally found in the docs where it says that all locks acquired continue until rollback or commit (end of txn), which answers my question in previous email (bottom). So, it seems like having an UPDATE ... ORDER BY would help this. At least I could force which order rows are locked that way. As mentioned earlier, though, I can do that manually as multiple updates in a single transaction. So, if I have 2 DB connections, and each one is doing the same update but on 2 different rows in the same table, and both of those rows have foreign keys to the same rows in other tables, is there a possibility that the foreign key locks could be acquired in different orders? In other words, are there situations with foreign keys where the only way I can prevent a deadlock with update/insert is to NOT HAVE foreign keys? The two situations I can think of are: 1) Two FK fields in a single row pointing to 2 different FK tables, for which any 2 updates get locks in opposite order (seems like a deadlock). 2) Two FK fields pointing to only 1 FK table (both fields are references to the same table), for which each update gets independent locks instead of one lock for both fields, so there is a potential that #1 problem could impact this as well. Is this the right maillist for this? Thanks again! Matt Mello wrote: > If I do the following, assuming I haven't applied the patch, will it > still result in a deadlock (in other words, does this only happen with > multi-record updates, or can it happen with independent single-record > updates inside transactions)?