db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: REPLACE INTO/INSERT IF NOT EXIST
Date Tue, 19 Jun 2007 09:12:32 GMT
Hi,

>>>>>>>>>>>> Kurt Huwig wrote (2007-06-19 10:46:09):
> Hi,
> 
> I am using HA-JDBC as a clustering solution and having a problem with 
> exceptions due to duplicate keys. The application is multi-threaded, 
> multi-JVM and needs to insert records into a table if they do not exist yet. 
> It is possible, that two threads try to insert the same record into the 
> database at the same time. Up to now, I found two solutions for this:
> 
> 1. ignore the "duplicate key exception"
> 2. DELETE and then INSERT the record
> 
> Unfortunately, both are not an option for me due to the way HA-JBDC
> works: it sends every SQL-update statement to every node. If it
> succeeds on one node and fails on another node, then the failing
> node is believed to be malfunctioning and taken out of the
> cluster. 

As I anduerstand the HA-JDBC docs, an SQLException will not lead to
the assumption of a failed node. An SQLException will trigger a
mechanism which cheks wether the node is functioning (via. some
trivial SQL query). See:
http://ha-jdbc.sourceforge.net/doc.html#Failed+Database+Nodes

Thus you may safely insert a record and ignore the duplicate key
exception.

> The problem is, that it is possible that the statements are
> executed out of order on the different nodes. This means, that
> e.g. node 1 executes statement 1 successfully and statement 2 with
> the exception and node 2 does the same vice versa. HA-JDBC detects
> different behaviour and disables one node.
> 
> One nice solution would be, to have something like MySQL's "REPLACE INTO" or 
> something like "INSERT IF NOT EXISTS", I saw somewhere else. The semantics 
> would be:
> 
> REPLACE INTO:
> if primary key does not exist
> 	-> INSERT
> else
> 	-> UPDATE
> 
> INSERT IF NOT EXISTS
> if primary key does not exist
> 	-> INSERT
> else
> 	-> nothing, especially no exception
> 
> Is there any way to achieve this with the current implementation or should I 
> file a RFE? The seconds one should be easy to implement, as you just do not 
> throw the exception.
> --
> Greetings
> 
> Kurt



-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message