db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kurt Huwig <k.hu...@iku-ag.de>
Subject Re: REPLACE INTO/INSERT IF NOT EXIST
Date Tue, 19 Jun 2007 09:34:38 GMT
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

Mime
View raw message