Re: getting duplicate number is there a

On Mon, May 16, 2005 at 17:36:21 -0400,
Joel Fradkin <jfradkin(at)wazagua(dot)com> 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?

Postgres also has SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. This
will prevent the current transaction from seeing the results of any
transactions that were not committed before the current transaction
started. In this mode updates can fail because of actions of concurrent
transactions, so you need to be able to retry.

Also Postgres does not do predicate locking. For some operations
serializable isn't good enough. Instead you need to lock a table to
prevent inserts. The common case is two simultaneous transactions
that insert a record into the same table and store the count of the
number of records in the table, while expecting things to look like
one transaction happened before the other. (E.g. that they return
distinct values for the counts.)

>
>
>
> 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
>
>
>
> Wazagua, Inc.
> 2520 Trailmate Dr
> Sarasota, Florida 34243
> Tel. 941-753-7111 ext 305
>
>
>
> jfradkin(at)wazagua(dot)com
> www.wazagua.com
> Powered by Wazagua
> Providing you with the latest Web-based technology & advanced tools.
> C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
> This email message is for the use of the intended recipient(s) and may
> contain confidential and privileged information. Any unauthorized review,
> use, disclosure or distribution is prohibited. If you are not the intended
> recipient, please contact the sender by reply email and delete and destroy
> all copies of the original message, including attachments.
>
>
>
>
>
>
>
>