5

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?

migrated from stackoverflow.com Jul 27 '14 at 16:58

This question came from our site for professional and enthusiast programmers.

  • 1
    I think the best way to do it is to simply catch the exception and then process with the next row. Nothing you can implement in the code can be more efficient in checking for duplicates than the database. – a_horse_with_no_name Jul 19 '14 at 20:40
  • @a_horse_with_no_name so you think catching the exception is faster than using synchronization for the actual query? I know that synchronizing the query will definitely slow down the workers as they have to wait until the lock is released to do their insertion. But I am not sure if running sql, getting constraint exception from postgres and then catching it in code will be faster or slow. – JohnMerlino Jul 19 '14 at 20:51
  • I think this is material for dba.SE. – Erwin Brandstetter Jul 19 '14 at 21:23
  • @ErwinBrandstetter you want me to delete the question here and ask it there? It is ok to ask a question there even if it references java code? – JohnMerlino Jul 19 '14 at 22:06
  • Ok I flagged it for moderator attention to migrate to dba. – JohnMerlino Jul 19 '14 at 22:18
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.