From: Ezequiel Tolnay <mail@etolnay.com.ar>
Subject: Re: getting duplicate number is there a
Date: Tue, 17 May 2005 15:02:17 +1000
Organization: Hub.Org Networking Services
Lines: 31
Message-ID: <d6btqq$24v7$1@news.hub.org>
References: <000e01c55a5f$4d912d00$797ba8c0@jfradkin>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.7.6) Gecko/20050319
In-Reply-To: <000e01c55a5f$4d912d00$797ba8c0@jfradkin>
To: pgsql-sql@postgresql.org

You can select "for update", so you ensure that the rows are locked for 
your current transaction's use exclusively. If the rows in question had 
been modified by another ongoing transaction, then the select will get 
blocked until the other transaction is finished.

Cheers,

Ezequiel Tolnay
etolnay@gbtech.com.au

Joel Fradkin wrote:
> I was using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in MSSQL.
> 
> Is there something similar in postgres to ensure its not in the middle 
> of being updated?
> 
>             sql = "SELECT COUNT(*) FROM tblcase WHERE LocationID = " & 
> intLocationID & _
> 
>                   " and substr(casenum,length(casenum)-1,2) = '" & 
> right(year(date),2) & "' AND clientnum = '" & _
> 
>                   chrClientNum & "'"
> 
> I will add a select just before doing the insert to see if this helps, 
> its not happening a bunch, but 5 6 times a day is still an issue for me.
> 
> I use the count as a segment of my case number so each time a new case 
> is entered the count goes up for that location for that year.
> 
> Joel Fradkin