Using WHERE NOT EXISTS with dummy table not working on Apache Derby Database

Question

So I'm using Java with Apache Derby, and I'm trying to insert a record, but only if a record with the same key does not already exist, because all the values I want to exist in my code rather than the database I use derbys dummy table (analogous to DUAL for db2) this is the query I'm using (md5 is the primary key)

PreparedStatement stmt = conn.prepareStatement("INSERT INTO artwork (md5,image) " +
               "SELECT ?,?" +
               "FROM SYSIBM.SYSDUMMY1 " +
               "WHERE NOT EXISTS ( SELECT 1 FROM artwork WHERE md5=?)");

    stmt.setString(1, key);
    stmt.setBinaryStream(2, new ByteArrayInputStream(imageData), imageData.length);
    stmt.setString(3, key);

and it seemed to be working, however when I multi-thread the code so that two threads could be trying to insert the same artwork I get the get errors about entering duplicate value into index.

If I synchronize the method so that only one thread can call the method at the same time then I get no such errors but this defeats the purpose of adding the WHERE NOT EXISTS value in the first place.

So is my query not doing what I think it is doing, or am I misunderstanding a general concept here ?

Answer 1

INFO: This solution does not work. See the comments for details. I left it here so who ever looks for a solution dosen't have to try this as well.

I haven't had the prodblem myself yet, but I would guess that you could get around it if you optimize the subquery out of it. Like this (I haven't tested it, maybe it needs improvement):

PreparedStatement stmt = conn.prepareStatement("INSERT INTO artwork (md5,image) " +
           "SELECT ?,?" +
           "FROM SYSIBM.SYSDUMMY1 left join artwork exi on ? = md5" +
           "WHERE exi.md5 IS NULL");

stmt.setString(1, key);
stmt.setBinaryStream(2, new ByteArrayInputStream(imageData), imageData.length);
stmt.setString(3, key);