Home » SQL & PL/SQL » SQL & PL/SQL » Update/insert logic results in duplicate rows. (Oracle, 10g)
Update/insert logic results in duplicate rows. [message #303249] Thu, 28 February 2008 09:16 Go to next message
cornell
Messages: 6
Registered: February 2008
Location: Pennsylvania, USA
Junior Member
In a Java servlet, we are doing (in psuedocode):

  rowsAffected = update... where trans_id = p_trans_id
  if (rowsAffected == 0) {
    insert ... (trans_id ... ) values (p_trans_id...)
  }

And we are getting, very infrequently, duplicate rows, that is multiple instances of the same trans_id.

This is the same practice as used throughout our organization, and we seem to be the only ones with the problem.

We've not been able to determine what sequence of events causes this to occur.

We've searched the Java and Stored Procedure code and found that there are no other locations where inserts or updates to this table are made.

We've changed to doing a select count(*) for trans_id and if count is 0 insert, else update. And we're still getting duplicates.

This may or may not relate to my previous topic "nextval and sysdate out of order"

(Our next attempt will be to replace the update/insert with a merge)

One could ask why the table was not defined to require uniqueness for trans_id, I don't know and the designer's no longer available. Or ask, why not add such a constraint? I've suggested that, and the... suggestion has not been heeded.

Any insight would be appreciated..
Thanks in advance
Cornell
Re: Update/insert logic results in duplicate rows. [message #303252 is a reply to message #303249] Thu, 28 February 2008 09:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
How much elapsed time can occur between the INSERT & subsequent COMMIT?
The inserted values are not really "in the DB" for others to see until after the next COMMIT.
Re: Update/insert logic results in duplicate rows. [message #303263 is a reply to message #303249] Thu, 28 February 2008 09:54 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
Looks like a situation where MERGE would be better than separate update and insert statements.

And if you do not want duplicate rows , why do not you have a unique key constraint on these columns ?

Srini
Re: Update/insert logic results in duplicate rows. [message #303267 is a reply to message #303252] Thu, 28 February 2008 10:02 Go to previous messageGo to next message
cornell
Messages: 6
Registered: February 2008
Location: Pennsylvania, USA
Junior Member
I'm told that auto commit is set (by the company's infrastructure database classes) which, if true, would make commit near instantaneous.

The statements are near the end of the servlet, and commit IS done there, so the time between insert and commit would be how long it takes Java to execute a hand full of statements (which may include update/inserts into a different table). So the timing on that would be a fraction of a second.

So the commit occurs in less than a second, if not immediately.
Re: Update/insert logic results in duplicate rows. [message #303270 is a reply to message #303263] Thu, 28 February 2008 10:05 Go to previous messageGo to next message
cornell
Messages: 6
Registered: February 2008
Location: Pennsylvania, USA
Junior Member
Yes, the next step is to try merge (which is a lot neater way to save data than update/insert, if one doesn't actually care if one is updating or inserting).

And, as I said, I didn't set it up, and my superiors have not heeded the suggestion to add the constraint.

I didn't mention before that this is a child table, it has it's own unique key. The trans_id is the key for the parent table, and, while logically must be unique, nothing was done at the child table definition level to ensure it.
Re: Update/insert logic results in duplicate rows. [message #303272 is a reply to message #303249] Thu, 28 February 2008 10:19 Go to previous message
cornell
Messages: 6
Registered: February 2008
Location: Pennsylvania, USA
Junior Member
We're running Real Application Clusters...

I've posted another thread "nextval and sysdate out of order" asking about sequencing of nextval values. Apparently, with Real Application Clusters, one cannot guarantee that they are in time sequence.

The DBA says that, with Real Application Clusters, there are multiple nodes and depending upon how well/quickly they communicate sequence numbers may not be in order, unless the ORDER option is used.

Could this multiple node situation allow the duplicate rows? What if, one node inserts, insert is committed and the servlet ends. Then the user hits the refresh, so the same servlet is invoked again, with the same data. Or the user isn't sure they've hit the submit button, so they hit it again, starting a second thread. Either way, a couple of seconds later we go through the same code and the update, which happens to be on a second node, doesn't see the row recently inserted by the first node and hence does an insert of it's own.

Could that happen?
Previous Topic: nextval and sysdate out of order
Next Topic: Distinct grouping
Goto Forum:
  


Current Time: Fri Dec 09 23:03:38 CST 2016

Total time taken to generate the page: 0.11852 seconds