Thread: getting duplicate number is there a

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?

Yep - see the SQL COMMANDS reference section under SET TRANSACTION ...
You could use LOCK TABLE too.
See Chapter 12 - Concurrency Control for discussion.

>             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.

I'd be tempted to have a case_numbers table with (year,location,max_num) 
and lock/read/insert to that. Makes everything explicit, and means you 
don't have to mess around with counts/substrings.

--  Richard Huxton  Archonet Ltd

I actually had the same thought (a counter table, I might be able to add
fields to the location table, but we have several applications case is just
an example). I agree that is probably the safest way and it also fixes
another issue I have been having when a user wants to transfer a case to
another location.

I appreciate the ideas, I could probably safely lock the numbering table as
I would be afraid of locking the case table.

Joel Fradkin

I'd be tempted to have a case_numbers table with (year,location,max_num) 
and lock/read/insert to that. Makes everything explicit, and means you 
don't have to mess around with counts/substrings.

--
  Richard Huxton  Archonet Ltd

On Mon, May 16, 2005 at 17:36:21 -0400, Joel Fradkin <jfradkin@wazagua.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@wazagua.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.
> 
>  
> 
> 
>  
> 
>  
> 

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