AW: SQL syntax for record locks ?????

A.Beermann@xxxxxxxxxxxxxx wrote:
> 
> 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 ??
> 

The select sounds good.
Please check (just to be sure) systemtable LOCKS
http://dev.mysql.com/doc/maxdb/en/21/7dd6076d8df04db859d77f61a171c7/fram
eset.htm

Did you use (for some reason) the commit_immediately-option (as can be
used in SQLStudio)? Then your lock will be thrown away too early.
Did you wait too long (installation-parameter LOCK_TIMEOUT compared to
the time you waited between select..with lock and calling user2)?
Did you finish your first application causing an implicit rollback for
this transaction-->causing the lock to be released before calling user2?

Which isolation level was used for user2?
If user2 uses isolation level 0, then he does not pay attention to the
lock sitting on the record. In all other cases it has to take the lock
into account.

If this all does not give THE hint, then please provide a vtrace which
has been prepared according to :

Start the trace: 
dbmcli -d <dbname> -u <dbm,dbm> util_execute diagnose vtrace default
select on 

Execute your SQL (select..with lock until select of user2)

Flush the trace: 
dbmcli -d <dbname> -u <dbm,dbm> util_execute diagnose vtrace flush or
more simply dbmcli -d <dbname> -u <dbm,dbm> trace_flush 

Create the text representation: 
dbmcli -d <dbname> -u <dbm,dbm> trace_prot akbm 

Copy the text representation to a local file: 
dbmgetf -d <dbname> -u <dbm,dbm> -k KNLTRCPRT -f <local file name> 

Stop writing of the trace: 
dbmcli -d <dbname> -u <dbm,dbm> util_execute diagnose vtrace default
select off. 

Elke
SAP Labs Berlin

> Best regards
> Albert
> 
> 
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
http://lists.mysql.com/maxdb?unsub=elke.zabach@xxxxxxx


--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx