Home » SQL & PL/SQL » SQL & PL/SQL » Insert statement from web apps resulted multiple records inserted into database problem (Oracle 10g, Solaris OS)
icon8.gif  Insert statement from web apps resulted multiple records inserted into database problem [message #328623] Fri, 20 June 2008 20:47 Go to next message
herox
Messages: 1
Registered: June 2008
Location: Malaysia
Junior Member
Dear All,

Currently I have web application (written in PHP) and connect to oracle 10g database. During testing environment (single user), when I click on "Save" button and perform insert statement, it will only insert one record which is suppossed to be. But when it comes to production environment (multi user), when one user when click on "save" button sometimes multiple records is inserted into database. I check the oracle session, sometimes it share the same session, sometimes it shares different session. Sometimes all the timestamp of insertion of the duplicated records is the same, sometimes not the same (eg 4 records inserted, but in every minutes). Do anyone experiences this problem before?

For example: (ID is running number/sequences from trigger when insert).

First Scenario, when user perform insert statement, it supposed to insert one time only which is ID : 123, but somehow another 2 records also inserted with ID 124 and 125. It shares the same oracle SID, same timestamp and userid

ID Name OracleSID TimeStamp UserID IP Address
123 TEST 1234 9:40PM JACK 192.168.0.2
124 TEST 1234 9:40PM JACK 192.168.0.2
125 TEST 1234 9:40PM JACK 192.168.0.2

Second Scenario, sometimes the duplication happen but it shares different OracleSID, different Timestamp but still from same userid/IP.
ID Name OracleSID TimeStamp UserID IP Address
123 TEST 1234 9:40PM JACK 192.168.0.2
124 TEST 1235 9:41PM JACK 192.168.0.2
125 TEST 1236 9:42PM JACK 192.168.0.2
...........
200 TEST 1788 22:22PM JACK 192.168.0.2

For extra information, our application have implement a locking mechanism to prevent user click multiple times on the button. During testing on development server, eventhough I tried to click "save" button multiple times, it will only insert one times. Sometimes the worse case is like second scenario, the records keep inserting itself for periods of time.
Re: Insert statement from web apps resulted multiple records inserted into database problem [message #328624 is a reply to message #328623] Fri, 20 June 2008 21:05 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

Congratulations!
You obviously have uncovered a SERIOUS bug in Oracle.
Oracle should not be misbehaving as it is for you.
I hope Oracle can get a fix to you Real Soon Now.

[Updated on: Fri, 20 June 2008 22:35] by Moderator

Report message to a moderator

Re: Insert statement from web apps resulted multiple records inserted into database problem [message #328897 is a reply to message #328623] Mon, 23 June 2008 04:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you implemented the Insert restriction in Javascript, it's possible that your end user is running in a browser with JS disabled.

The variance in SID is to be expectd - Web based DB apps almost never have a 1-1 connection between client Web sessions and DB sessions. There is almost always some sort of connection/session pooling going on.

Re: Insert statement from web apps resulted multiple records inserted into database problem [message #329001 is a reply to message #328623] Mon, 23 June 2008 12:39 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
It might be of value also for you to describe what you consider "unique" as regards the "duplicate" records. Then tell us why there is no unique index on this to stop duplicates. Maybe after creating a "unique" index on the right set of columns, you will see where the job is doing the wrong thing.

Its a thought anyway.

Also, anacedent, try to be useful will you. Your recent posts suggest you are pissed at Oracle today. What did they do to you recently that has you upset?

Kevin
Previous Topic: Need help with pl/sql
Next Topic: Error creating procedure
Goto Forum:
  


Current Time: Mon Dec 05 14:51:07 CST 2016

Total time taken to generate the page: 0.24977 seconds