Re: How can I temporarily Lock a table?

A copy of this was sent to "Neil Robinson" <vtr_at_firestorm996.freeserve.co.uk> (if that email address didn't require changing) On Sat, 8 May 1999 12:21:16 +0100, you wrote:

>I am running an Access 97 front-end over Oracle 7.3 on Unix. Our Order
>Numbers are stored in a single-record table, which is incremented each time
>an order number is allocated. This is fine, but because of the code
>involved in incrementing the OrderNo I have a situation where the following
>scenario occurs....
>

first -- i would recommend you get rid of the table to increment order numbers with and use a sequence. you can just "select orderno_seq.nextval from dual" to get a unique, increasing number for orders.

second, if you *must* leave it in a table, do it like this:

update single_record_table set orderno = orderno+1; select orderno from single_record_table; commit;

the update will lock the row preventing others from doing the update. you will wait until no one else is doing the update/select and then do the update locking the row on your behalf. you can safely read out the new value and commit, allowing others to get a value.

>User A requests new OrderNo
>User B requests new OrderNo
>User A receives latest OrderNo
>User B receives latest OrderNo (same as User A)
>
>I cannot find a way of incrementing the orderno and grabbing the new orderno
>on the same command, so I am looking at temporarily locking the table so
>that User B cannot increment/grab an order no until User A has done so and
>unlocked the table. The best way for me to do this would be to run an SQL
>command direct from with Access VBA.
>
>If anyone could let me know the commands for locking & unlocking tables, and
>also of any potential pitfalls or problems with it, it would be appreciated.
>
>Thanks

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat May 08 1999 - 10:43:19 CDT