| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: C/S Concurrency issue
A copy of this was sent to "D.W. Hoellein" <hoellein_at_kersur.net>
(if that email address didn't require changing)
On Tue, 12 May 1998 01:06:15 -0400, you wrote:
>Our C/S system requires a unique id for each top-level object inserted
>into a table. We're using ODBC and are trying to stay fairly generic in
>our code, so we are maintaining the current id ourselves and
>incrementing as we insert new records.
>
>Currently we SELECT the current id value, then UPDATE it to increment it
>for the next time. We have the problem of Client #1 obtaining the
>current value of N, but before it can UPDATE it to N+1, Client #2 also
>obtains the id of N. How can we insure that only one client will get a
>given value, without any other client getting it before the UPDATE
>occurs?
>
>Thanks in advance!
>
>Don Hoellein
>Mantra Software Corp.
Well, the answer to your question is to put the id's into their own table by name and reverse the order of your operations.
UPDATE T set COUNTER = COUNTER+1 where NAME = 'value'; SELECT MAX(COUNTER) into :my_var FROM T where NAME = 'value';
the update will serialize access to T for purposes of getting the counter.
But... Since databases have put lots of efforts into providing non-blocking solutions (Oracle Sequence Plus a Trigger, Informix Serial Type, Sybase/MS Identity types) you might consider working them into your solution to avoid the concurrency issue alltogether and just remove the bottleneck from your system.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue May 12 1998 - 06:21:49 CDT
 |
 |