A.Beermannpiepenbrock.de
Date: Tue Dec 28 2004 - 04:56:09 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hello everybody!

My application connects to a maxdb 7.5 database via odbc-driver
isolation level = commited
I have a special table autonum where i handle automatic counters (order
number,invoice number,...)

When inserting a new order record into the orders table the application
does:

Select lastnumber from autonum where searchid = 'order_number'
my_new_ordernumber = lastnumber + 1
update autonum set lastnumber=?my_new_ordernumber where searchid =
'order_number'
Insert into orders (orderno,.......) values(?my_new_orderno,......)

I don't know how to lock the selected record of the autonum tabled
between
selecting and updating

The select statement above from user1 should lock the record, so that
other users
doing the same select have to wait (will get no new order number) until
the update
of user1 is finished ???!!!

I tried:

"Select lastnumber from autonum where searchid = 'order_number' WITH
LOCK EXCLUSIVE" with no success ??

When testing user1 select (and wait, no update)
user2 select
User2 gets the same order_number as user1.

What is the correct syntax of the select commad, that will do the wanted
behavior ??

Best regards
Albert

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]