Ho, Am Dienstag, 19. Juni 2007 schrieb Bernt M. Johnsen: > 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.
This documentation is somehow misleading. It will deactivate the node
immediately, if it fails a "VALUES (1)" (for Derby dialect). But this is only
to determine, if the statement is wrong or the database. After the statement
has been executed on all databases, this happens:
net.sf.hajdbc.sql.SQLObject.java:443
// If any databases failed, while others succeeded, deactivate
them
if (!exceptionMap.isEmpty())
{
this.handleExceptions(exceptionMap);
}
and handleExceptions() deactivates the failed node.
> > 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.
--
Kurt
GnuPG 1024D/99DD9468 64B1 0C5B 82BC E16E 8940 EB6D 4C32 F908 99DD 9468
pgpvN9TEGySFC.pgp
Description: PGP signature
