Hi,

We have a master table, say with two columns name (varchar) and id
(number) and a stored procedure insert_name_details(name, other
details).

SP will first check if name is present in master table or not. If
present, it will get the id otherwise it will insert a row in master
table with a new sequence number and use that id in other tables as
foreign key. So its basically an atomic "get or create-if-not found"
operation.

There are two ways of handling concurrency here.
Option 1
-------------
select id from master_table;
if (not_found)
{
lock master_table;
(again) select id from master_table;
if (still_not_found)
{
insert record in master table;
}

}

Option 2
------------
select id from master_table;
if (not_found)
{
try
{
insert record in master table;
}
catch (PrimaryKeyVIolationException)
{
select id from master_table;
}

}

Both these options are for stored procedure implementation although
code is written is Java style.

Which method is preferred? Locking table or catching exceptions? I
know that using exceptions should not be used to drive logical flow,
but it makes life simpler for a programmer vs. locking table. Also
locking tables means holding resources from other threads, but table
will be locked for a brief amount of time i.e. till master row is
inserted.

Any comments, suggestions or alternatives?

Thanks,
Sameer

On Dec 19, 1:01 am, samee... (AT) gmail (DOT) com wrote:
I'd opt for option 2. Option 1 is going to lock the table every time
there's an insert. Option 2 is only going to cause delays when two
processes try to insert the same name at the same time.

sameergn (AT) gmail (DOT) com wrote:
Neither of these is a good idea.

First of all this is Oracle not SQL Server so the data type is not
VARCHAR. A strong clue that you are not dealing with Oracle as someone
who understands the way it works.

Second what is the form of your primary key? If it is a surrogate key
it should be generated by a sequence object making a collision impossible.

Third, assuming a natural key and a possible collision which is more
likely? A duplicate or a non-duplicate? Assuming some competence in
system design a duplicate is highly unlikely so you should just to the
insert and trap the rare exception.

Again, as at OTN where you posted the exact same question, it appears
you think Oracle is SQL Server ... it is not.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Dec 19, 8:45 am, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
Daniel,

This code is for Oracle 10g database. I meant varchar2, but wrote
varchar just to keep it generic, pseudo-code like, to indicate a
character columns. Anyway, that was just an example and not real
columns anyway.

I have seen "select ... for update" construct when you want to
atomically get and update a row, but was not sure what is the
recommended approach for inserting a new master row in a multi-
threaded environment.

The key will be generated by a sequence object.

Thanks,
Sameer

sameergn (AT) gmail (DOT) com wrote:
SELECT FOR UPDATE makes perfect sense if you are going to update but
that appears to not be what you are doing. If you are inserting then
just perform the insert like this:

BEGIN
INSERT INTO
(....,....)
VALUES
(...,...);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
... do something else ...
END;

This is, again, assuming a good design and collisions are rare.

What you did not address is why you think a collision is possible.
In most cases that possibility is a red flag to it being a bad design.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Dec 19, 9:30 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
We are trying to log HTTP requests from tomcat to a database table.
This would include URL, referrer, user agent (browser), HTTP method
(GET/POST) etc. It would require lot of space if we log all the
character data as it is, so we decided to normalize it, but all
possible values of URL, referrer, UA are not known in advance. So a
master table for them will be built on the fly as and when new data
arrives. It may then be possible for two threads to create an entry in
master table at the same time.

With this approach, contention would arise only during initial phase,
when master table is being built. After some time most of the values
would go into master table and their ID will always be found while
inserting access log record.

( I am aware of the AccessLogValve mechanism provided by Tomcat but we
are logging some application specific data in each row and making
these inserts using a separate thread to minimize impact on service
processing thread)

<sameergn (AT) gmail (DOT) com> schreef in bericht
news:0c6a4c21-b8fa-4b91-9625-c8e1426c2c69 (AT) s12g2000prg (DOT) googlegroups.com...
In this case I think you should follow Daniels design and just ignore the
exceptions (but keep the exceptions clause in your code, just put null; in
it) : if the value is already there, it's no problem. Only problem might be
that in time you will only get exceptions, and no new records.

Shakespeare

sameergn (AT) gmail (DOT) com wrote:
It seems you are indicating collisions will be rare or perhaps never
happen so just use an optimistic methodology and trap for the rare
occasion when an issue arises as I have suggested.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 20.12.2007 15:05, DA Morgan wrote:
I believe the situation is different: while there might be few
collisions for URLs there are likely only few collisions for HTTP
methods (this table could even be prefilled as the data set is known
beforehand) and more but still fewer collisions for browser.

Having said that it seems different strategies for different columns are
in order.

URL: option 3: direct insert without select, catch unique violation
error and return selected id

Browser: allow multiple entries, option 1 but without the locking.

HTTP method: prefill, for the rest use the same approach as for browser

etc.

Kind regards

robert

On 20.12.2007 22:05, Robert Klemme wrote:
PS: A nice example for the importance of knowing the data.

robert