Received: from sunpine.krs.ru (SunPine.krs.ru [195.161.16.37]) by hub.org (8.9.3/8.9.3) with ESMTP id WAA31667; Sun, 25 Jul 1999 22:43:08 -0400 (EDT) (envelope-from vadim@krs.ru) Received: from krs.ru (dune.krs.ru [195.161.16.38]) by sunpine.krs.ru (8.8.8/8.8.8) with ESMTP id KAA00701; Mon, 26 Jul 1999 10:43:00 +0800 (KRSS) Sender: root@sunpine.krs.ru Message-ID: <379BCB34.B3A0EF3E@krs.ru> Date: Mon, 26 Jul 1999 10:43:00 +0800 From: Vadim Mikheev Organization: OJSC Rostelecom (Krasnoyarsk) X-Mailer: Mozilla 4.5 [en] (X11; I; FreeBSD 3.0-RELEASE i386) X-Accept-Language: ru, en MIME-Version: 1.0 To: Tom Lane CC: Oleg Bartunov , pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org Subject: Re: [SQL] inserts/updates problem under stressing ! References: <1696.932833746@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit (Sorry for incomplete prev message). Tom Lane wrote: > > I wonder whether this doesn't have a problem with concurrent access: > > 1. Transaction A does 'Select count into cnt', gets (say) 200. > 2. Transaction B does 'Select count into cnt', gets 200. > 3. Transaction A writes 201 into hits record. > 4. Transaction B writes 201 into hits record. > > and variants thereof. (Even if A has already written 201, I don't think > B will see it until A has committed...) You're right, Tom. > I am not too clear on MVCC yet, but I think you need "SELECT FOR UPDATE" > or possibly an explicit lock on the hits table in order to avoid this > problem. Vadim, any comments? SELECT FOR UPDATE will not help: if there was no record for particular key then nothing will be locked and two records with the same key will be inserted. Oleg, use LOCK IN SHARE ROW EXCLUSIVE MODE. Vadim