duplicate key violates unique constraint when using multi-threading

Question

I am using ExecutorService with a fixed thread pool of 50, and a fixed database connection pool of 50, using HikariCP. Each worker thread processes a packet (a "report"), checks if it is valid (where each report must have a unique unit_id, time, latitude and longitude), grabs a db connection from the connection pool, and then inserts the report into the reports table. The uniqueness constraint is created with postgresql and dubbed "reports_uniqueness_index". When I have high volume, I get a ton of the following error:

org.postgresql.util.PSQLException: ERROR: duplicate key value 
  violates unique constraint "reports_uniqueness_index"

Here's what I believe the problem is. Before database insertion, I perform a check to determine whether a report already exists in the table with the same unit_id, time, latitude and longitude. If it doesn't then the report is valid and I perform the insertion. However, I think because I am using concurrency I have 50 threads each checking at the same moment if the report is valid and since none of them have been inserted yet, each thread thinks it has a valid report and when it goes to insert them at same moment, that is when postgresql raises the error.

I would like a solution that doesn't create any latency with the concurrency. I been trying to avoid using synchronized statement or a reentrant lock because the database insertions need to occur as quickly as possible. This is the insertion right here:

 private boolean save(){
        Connection conn=null;
        Statement stmt=null;
        int status=0;
        DbConnectionPool dbPool = DbConnectionPool.getInstance();
        String sql = = "INSERT INTO reports"
        sql += " (unit_id, time, time_secs, latitude, longitude, speed, created_at)";
        sql += " values (...)";
        try {
            conn = dbPool.getConnection();
            stmt = conn.createStatement();
            status = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            return false;
        } finally {
            try {
                if (stmt != null)  
                {  
                    stmt.close();
                }  

                if (conn != null)  
                {  
                    conn.close();  
                }  
            } catch(SQLException e){}
        }

        if(status > 0){
            return true;            
        }

        return false;
}

One solution I thought of was using the Class object itself as a lock object:

synchronized(Report.class) {
    status = stmt.executeUpdate(sql);
}

But this will delay insertsion for other threads. Is there a better solution?

score 4 · Accepted Answer · answered Jul 20 '14 at 01:35

By creating a unique constraint on your table, you are telling the database "each time I try to insert a row in this table, please check that there is no existing row with this combination of columns the same. Oh, and make sure you do it in an atomic fashion so that if someone else tries to insert one at the same time as me, only one of us will succeed".

After doing that, in most cases there is little point in replicating the same logic in your application. It will only make the process less efficient. You would be better off to take @horse's suggestion and catch the exception.

There are a couple of things to watch out for though. One is that if you doing this inside a transaction, then the transaction will be automatically rolled back on the error. If you did anything else prior to this in the transaction, it will have been undone. To avoid this, you need to set a SAVEPOINT prior to the insert, and then RELEASE or ROLLBACK the savepoint as appropriate.

The other issue is that the postgresql log will still contain these errors, even if they are caught and ignored by the Java code. Flooding your logs with meaningless noise helps to hide true problems that might be lurking in there, so it is not a good thing.

Possibly a better solution, which avoids both of these problems, is to create a stored procedure which inserts the record, handles any exceptions which occur, and returns an indication to your application as to whether the record was stored or not.

For example, if you had a table that looked like this:

CREATE TABLE test(a INTEGER, b TEXT, UNIQUE(A,B));

Then you could have a function like this:

CREATE FUNCTION test_insert(pa INTEGER, pb TEXT) RETURNS INTEGER AS $$
BEGIN
    INSERT INTO test(a,b) VALUES (pa, pb);
    RETURN 1;
EXCEPTION
    WHEN unique_violation THEN
       RETURN -1;
END;
$$ LANGUAGE plpgsql;

and you could use it like this:

testdb=> SELECT test_insert(1,'foo');
 test_insert
-------------
           1
(1 row)

testdb=> SELECT test_insert(1,'foo');
 test_insert
-------------
          -1
(1 row)

If an attempt is made to insert a record that violates the constraint, no exception is thrown or logged, and if executed inside a transaction, the transaction is not affected. The function returns a value which you can use to see if the record was inserted or not. It is a very basic example, but should illustrate the point.

duplicate key violates unique constraint when using multi-threading

1 Answers1