| Unique Constraint Error due to multiple insert by Java [message #451890] |
Sun, 18 April 2010 05:04  |
tomicmanka
Messages: 1
Registered: April 2010
Location: London
|
Junior Member |
|
|
Hi All,
I have two different java process trying to insert in the same time in the same table for the same trade. The structure of the table Report is
create table report
( TRADE_ID NUMBER,
VERSION NUMBER,
MESSAGE_TIME TIMESTAMP)
There is a unique key on (TRADE_ID and VERSION)
So if a new trade_id is inserted, the version is set to 1 and the second becomes 2 and so on. The version is calculated as last version of the trade_id ie. version + 1. It was woking fine till a new Java process was build that fired inserts through ten different java instances at the same time resulting in unique key error. So in detail what is hapenning is if three records of trade_id's comes in at the same time it should allocate versions in a first come first serve basis and there should be three versions of trade id 1,2 and 3. Now due to the multiple instances they all seems to get fired at once and all ending up with version one and thus resulting in unique key constrain error while trying to insert into the table.
If you have any idea, or if you have come across such issues and have found a solution then kindly do let me know.
Any help will be greatly appretiated.
Thanks,
Tomic
|
|
|
|
|
|
|
|
| Re: Unique Constraint Error due to multiple insert by Java [message #451905 is a reply to message #451890] |
Sun, 18 April 2010 13:27  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
This is an excellent example of how people build systems to be single user systems. This is to say, that techniques were employed, without considering how they would behave when two or more users tried to do work at the same time.
Do not feel too badly. This is a common error among application developers. Your solution will depend upon your requirements. Because you are using the traditional MAX()+1 METHOD of generating keys we can assume two things:
1) you require a monotonically increasing sequence
2) you are not willing to tolerate gaps in the sequence
If these are not your requirements, they you should not be using MAX()+1 to assign values. You should instead use the simple SEQUENCE NUMBER method.
If these are your requirements, then you should seriously consider changing your requirements. To correctly generate using MAX()+1 requires code that will dramatically hamper the scalability of your system when it comes to inserting into this table.
If you insist on using this method because you firmly believe you must employ a GAPLESS SEQUENCE, then you will have to add additional code to your system and/or appliactions to account for what you are doing.
1) you must get all applications that will be assigning the version number to agree on the same method of acquiring the number
2) you must use some kind of serialization to ensure that no two requests for a number can generate the same number
There may be more than one method to do this but this is the one I know best.
create or replace function get_version_number (trade_id_p in number) return number is
return_v number;
begin
lock table report in exclusive mode;
select nvl(max(version),0)+1
into return_v
from report
where trade_id = trade_id_p
;
return (return_v);
end;
/
Notice the LOCK TABLE IN EXCLUSIVE MODE use to acquire exclusive access to the REPORT table. This esures that no one can generate the same number twice in competing transactions. It also ensures that no two transactions can be active at the same time thus relegating your system to being pretty much a single threaded system. Such is the nature of a GAPLESS SEQUENCE, and the reason why you should reconsider the use of MAX()+1.
If you do not understand the reasoning behind the exclusive table lock then you do not understand why your problem happened.
Since your version number is partitioned by trade_id, you may opt for an alternative implementation if you so desire.
create or replace function get_version_number (trade_id_p in number) return number is
return_v number;
begin
select nvl(max(version),0)+1
into return_v
from report
where trade_id = trade_id_p
for update
;
return (return_v);
end;
/
This method is superior in that at least different trades can operate at the same time since it locks rows one at a time rather than the entire table. But it still causes waits for the same trade which is the problem you have as indicated by your UNIQUE CONSTRAINT violations.
Lastly as you can see, this only works if your applications all agree to use the function as the mechanism for getting the next number. If you get a rogue app, you can still get failures.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
