Re: Pessimistic Locking via ODBC

Art Zemon <art@nwnREMOVE_ME.org> wrote: :Scott Nichol wrote: :> :> Since you are already doing this within the context of a transaction, :> here's what I suggest. Do your UPDATE *before* your SELECT. Doing the :> UPDATE within a transaction will lock the record, and keep it locked until :> you commit or rollback. As Boistuaud points out, however, this ends up :> serializing clients, as each successor must wait for the current holder of :> the lock to finish. : :Scott, : :I'm not sure how to actually *do* that. I need the SELECT to obtain the :current value. How can I increment it unless I have it? update Serial_Number set value=value + 1 where sn_key = 1 Then select value from Serial_Number where sn_key = 1 But the bigger question is why don't you use a column defined as serial for this purpose. If you can't use a serial type column directly in a table we use a single table with nothing but a serial type column in it and insert into that to get the next value. You can delete all rows from this table at any time as the next serial value are not dependent on any data in the table itself. I think that's a much better design than using one table that contains a value that you update with locking as you do. Using ODBC you can get the last inserted value in a serial column via: select dbinfo(something I dont't remember right now) from sometable where condition to return only one row You will find the appropriate parameter to dbinfo in the Informix manuals. The best manuals are at http://www.informix.com/answers as they can be searched. This select is of course best done on some table that contains only one row in the first place. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org