Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8EB27476003 for ; Fri, 18 Oct 2002 09:57:55 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id F2B21475E8A for ; Fri, 18 Oct 2002 09:57:54 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g9IDvwhR017218; Fri, 18 Oct 2002 09:57:58 -0400 (EDT) To: Achilleus Mantzios Cc: Christoph Haller , lud_nowhere_man@yahoo.com, pgsql-sql@postgresql.org Subject: Re: Locking that will delayed a SELECT In-reply-to: References: Comments: In-reply-to Achilleus Mantzios message dated "Fri, 18 Oct 2002 11:32:51 +0300" Date: Fri, 18 Oct 2002 09:57:58 -0400 Message-ID: <17217.1034949478@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/210 X-Sequence-Number: 9927 Achilleus Mantzios writes: >> The problem is solved >> >> a) Using SERIALIZABLE XACTION ISOLATION LEVEL >> b) in T2 using "select for update" instead of select. That way T2's >> queries will wait untill T1's statements commit or rollback. ISTM that SERIALIZABLE mode will not solve this problem, since by definition you want T2 to see results committed after T2 has started. A simple answer is to have T1 grab an ACCESS EXCLUSIVE lock on some table to block T2's progress. If that locks out third-party transactions that you'd rather would go through, you can probably use a lesser form of lock --- but then both T1 and T2 will have to cooperate since each will need to explicitly take a lock. I gave a presentation at the O'Reilly conference this year that covered some of these issues. Looks like you can still get the slides from http://conferences.oreillynet.com/cs/os2002/view/e_sess/2681 regards, tom lane