How to insert a primary key value into a table under race conditions?

Question

We have a code that inserts a new value into the table column that is a primary key. First we are doing select and if this value is not present then we do insert.

query = "SELECT AValue from ATableCache WHERE AValue=" + avalue;
ResultSet rs = stmt.executeQuery(query);
if (!rs.next()) 
{
    query = "INSERT INTO ATableCache (AValue) VALUES ('" + avalue + "')";
    stmt.executeUpdate(query);
}

At race conditions this algorithm can result in an SQL error while inserting a value that was just inserted by another thread. One of the options is synchronization but this will slow execution. Please, note that insertion happens quite seldom. Is there more efficient algorithm?

If insertion seldom occurs, why are you concerned about synchronization slowing down execution? — Andrew Jones, Oct 10 '13 at 17:20
So why not just try to insert it unconditionally? Check if the error code == key violation to determine if it worked/failed. — Marc B, Oct 10 '13 at 17:21

score 3 · Answer 1 · edited May 23 '17 at 12:10

I'd have the database generate the key and make sure that the transaction is serializable. You need to set the isolation level on your database connection properly.

It might be possible to do with PostgreSQL, but a general solution guaranteed to work for all JDBC drivers has eluded Spring, according to this answer:

Is there anyway to get the generated keys when using Spring JDBC batchUpdate?

score 2 · Answer 2 · answered Oct 10 '13 at 17:30

2

query = "INSERT INTO ATableCache (AValue) select '" + avalue +
"' where not exists (select 1 from ATableCache where AValue = '"+avalue +"')";

answered Oct 10 '13 at 17:30

Panos Theof

1,450
1
21
27

@anarinsky Just a dummy `select` for `not exists` check. – Ihor Romanchenko Oct 10 '13 at 18:04
can I see the actual query, for example avalue='RGG'? – Alex Oct 10 '13 at 22:30
INSERT INTO ATableCache (AValue) SELECT 'RGG' where not exists (select 1 from ATableCache where AValue = 'RGG' ) – Panos Theof Oct 11 '13 at 14:38

score 1 · Accepted Answer · answered Oct 10 '13 at 17:20

1

There aren't that many options:

Your avalue generation code will need to create unique keys atomically. This will require synchronization so that the value isn't shared by threads.
Let the database handle generating the key.
Catch the exception and handle it (not recommended).

answered Oct 10 '13 at 17:20

Sotirios Delimanolis

274,122
60
696
724

Why option NO. 3 is not recommended? – Alex Oct 10 '13 at 17:24
@anarinsky It depends on how you handle it, if you try to reinsert with another value, by that time, another thread might have added that value too. – Sotirios Delimanolis Oct 10 '13 at 17:24
@anarinsky If it's that kind of race condition then yes, you're good to go. – Sotirios Delimanolis Oct 10 '13 at 17:27

score 0 · Answer 4 · edited May 23 '17 at 12:28

0

I saw the tag postgresql in your post. In this case, I suggest you to set your primary key autogenerated and note put it in your insert statement.

I think this post can help if you try this approach.

edited May 23 '17 at 12:28

Community

1
1

answered Oct 10 '13 at 17:27

Maciel Escudero Bombonato

1,208
18
19

score 0 · Answer 5 · answered Oct 10 '13 at 18:07

Postgresql uses sequences for key generation or you can use the serial data type.

For sequences it is straightforward and here are the docs: http://www.postgresql.org/docs/8.1/static/sql-createsequence.html. The downside is you need to use nextval() on your inserts, but you get features like being able to pick how many values to cache.

Your other option is to set your primary key to one of the serial types. The docs and a good link on the ins and outs of serial.

Serial will create the primary key without you needing to specify it in your insert.

How to insert a primary key value into a table under race conditions?

5 Answers5