Update/insert logic results in duplicate rows. [message #303249] |
Thu, 28 February 2008 09:16  |
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 #303267 is a reply to message #303252] |
Thu, 28 February 2008 10:02   |
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   |
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  |
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?
|
|
|