Oracle and exclusive-lock

[Chris Kelleher]

We have ported a Progress application to the Oracle
dataserver.

When creating a record a trigger fires to get a unique number. This is done
by a find last .... exclusive-lock of the table. But for Oracle this
exclusive-lock is translated to a share-lock. So if two users at the same
time create a record, a 5032 error message occurs(unique constraint
violated). Putting a validate or release after the find is not a solution.

What kinds of solutions do you have to solve this?

-peter
---------------------------------------------------------
Get your free Progress v9 stuff at http://www.v9stuff.com
---------------------------------------------------------
Certified Progress Solution Designer http://www.walvis.nl
Home page: http://home.kabelfoon.nl/~prvdam/progress.htm
Official BDK Partner Standard disclaimers apply

 

[Chris Kelleher]

Hi Peter,
We are dealing with the same problem here. Our unique constraints occurred when
we would create a record and not assign all the fields in unique indexes. When
2 users tried to do this at once, they'd get the unique constraints.

Our fix has been to go through all our code, make sure all component fields of
unique indexes are assigned to real values (not the null value) and then to add
either a RELEASE or VALIDATE to force Oracle to write the record to the db
before the record scope ends.

It's a pain for our gui application but it was crippling for our web
application. (the unique constraints) it would cause looping error msgs in the
logs, they'd get huge, suck up the server resources and crash the server. big
problem for us.

hope that helps. kt

Peter van Dam <p.van.dam@walvis.nl> on 10/05/99 10:22:49 AM

To: servers@peg.com
cc: (bcc: Katie Eveler/GELCO)

Subject: Oracle and exclusive-lock

We have ported a Progress application to the Oracle
dataserver.

When creating a record a trigger fires to get a unique number. This is done
by a find last .... exclusive-lock of the table. But for Oracle this
exclusive-lock is translated to a share-lock. So if two users at the same
time create a record, a 5032 error message occurs(unique constraint
violated). Putting a validate or release after the find is not a solution.

What kinds of solutions do you have to solve this?

-peter
---------------------------------------------------------
Get your free Progress v9 stuff at http://www.v9stuff.com
---------------------------------------------------------
Certified Progress Solution Designer http://www.walvis.nl
Home page: http://home.kabelfoon.nl/~prvdam/progress.htm
Official BDK Partner Standard disclaimers apply

 

[Chris Kelleher]

>When creating a record a trigger fires to get a unique number. This is done
>by a find last .... exclusive-lock of the table. But for Oracle this
>exclusive-lock is translated to a share-lock. So if two users at the same
>time create a record, a 5032 error message occurs(unique constraint
>violated). Putting a validate or release after the find is not a solution.
>
>What kinds of solutions do you have to solve this?

Are you finding the last record, getting its ID# and incrementing or are
you using a sequence-like record? Oracle supports sequences, similar
to Progress, although there are enough differences to make you careful.
You could convert that way, although it has other implications (can your
numbering tolerate gaps, for instance?).

Mike

----------
Michael J. Lonski Allegro Consultants, LTD.
Office: 804-649-1011 Email: mlonski@allegroconsultants.com
Fax : 804-649-7823 WWW : http://www.allegroconsultants.com
* * * Founders of VA PUG and PUG Central * * *

 

[Chris Kelleher]

kt wrote:

> Hi Peter,
> We are dealing with the same problem here. Our unique
> constraints occurred when
> we would create a record and not assign all the fields in
> unique indexes. When
> 2 users tried to do this at once, they'd get the unique constraints.
>
> Our fix has been to go through all our code, make sure all
> component fields of
> unique indexes are assigned to real values (not the null
> value) and then to add
> either a RELEASE or VALIDATE to force Oracle to write the
> record to the db
> before the record scope ends.
>
> It's a pain for our gui application but it was crippling for our web
> application. (the unique constraints) it would cause looping
> error msgs in the
> logs, they'd get huge, suck up the server resources and crash
> the server. big
> problem for us.

We have tried this and we only have one unique index field but
we cannot seem to get validate in the create trigger to work
as you describe. We still get the unique constraints problem
if two users add a record at the same time. Am I missing
something?

-peter
---------------------------------------------------------
Get your free Progress v9 stuff at http://www.v9stuff.com
---------------------------------------------------------
Certified Progress Solution Designer http://www.walvis.nl
Home page: http://home.kabelfoon.nl/~prvdam/progress.htm
Official BDK Partner Standard disclaimers apply

 

[Chris Kelleher]

> >When creating a record a trigger fires to get a unique
> number. This is done
> >by a find last .... exclusive-lock of the table. But for Oracle this
> >exclusive-lock is translated to a share-lock. So if two
> users at the same
> >time create a record, a 5032 error message occurs(unique constraint
> >violated). Putting a validate or release after the find is
> not a solution.
> >
> >What kinds of solutions do you have to solve this?
>
> Are you finding the last record, getting its ID# and
> incrementing or are
> you using a sequence-like record? Oracle supports sequences, similar
> to Progress, although there are enough differences to make
> you careful.
> You could convert that way, although it has other
> implications (can your
> numbering tolerate gaps, for instance?).

Yes we can tolerate gaps but we would like the application to
run both on Progress, Oracle and AS/400. Is there an easy
solution for that?

-peter
---------------------------------------------------------
Get your free Progress v9 stuff at http://www.v9stuff.com
---------------------------------------------------------
Certified Progress Solution Designer http://www.walvis.nl
Home page: http://home.kabelfoon.nl/~prvdam/progress.htm
Official BDK Partner Standard disclaimers apply

 

[Chris Kelleher]

We are immersed in similar issues at this very moment. Most of our trx
numbers (transactions) are generated by a separate library call. This
library does a find last and puts the last trx into a no-undo variable.
Then a Do block is started similar to the following:

Do:
w-trx = w-trx + 1.
assign {&table}.trx = w-trx no-error.
&if defined(s-use-ds) > 0 &then
validate {&table} no-error.
&endif
if error-status:error then
undo,retry.
leave.
end.

You could like replace the preprocessor &IF test (s-use-ds is our own value
to say compile with dataserver specific code) with:
if dbtype({&dbname}) <> "progress" then

I know you said that putting a validate after the find was not possible but
is there a spcecifi reason for this? For Oracle dataserver, the assign does
NOT create an error, only a write to the database will give you the error.

Kim Hutchinson
Xponent Technical Support