Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id MAA35848; Sat, 24 Jul 1999 12:30:22 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id MAA01698; Sat, 24 Jul 1999 12:29:07 -0400 (EDT) To: Oleg Bartunov cc: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org Subject: Re: [SQL] inserts/updates problem under stressing ! In-reply-to: Your message of Sat, 24 Jul 1999 13:48:29 +0400 (MSD) Date: Sat, 24 Jul 1999 12:29:06 -0400 Message-ID: <1696.932833746@sss.pgh.pa.us> From: Tom Lane Oleg Bartunov writes: > I did some benchmarks of my Web site and notice I lost some hits > which I accumulate in postgres (6.5.1) database on Linux 2.0.36 system > CREATE FUNCTION "acc_hits" (int4) RETURNS int4 AS ' > Declare > keyval Alias For $1; > cnt int4; > curtime datetime; > Begin > curtime := ''now''; > Select count into cnt from hits where msg_id = keyval; > if Not Found then > cnt := 1; > -- first_access inserted on default, last_access is NULL > Insert Into hits (msg_id,count) values (keyval, cnt); > else > cnt := cnt + 1; > Update hits set count = cnt,last_access = curtime where msg_id = keyval; > End If; > return cnt; > End; > ' LANGUAGE 'plpgsql'; 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...) 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? regards, tom lane