Locking Tables with postgres in JDBC

Question

Just a quick question about locking tables in a postgres database using JDBC. I have a table for which I want to add a new record to, however, To do this for the primary key, I use an increasing integer value.

I want to be able to retrieve the max value of this column in Java and store it as a variable to be used as a new primary key when adding a new row.

This gives me a small problem, as this is going to be modelled as a multi-user system, what happens when 2 locations request the same max value? This will of course create a problem when trying to add the same primary key.

I realise that I should be using an EXCLUSIVE lock on the table to prevent reading or writing while getting the key and adding a new row. However, I can't seem to find any way to deal with table locking in JDBC, just standard transactions.

psuedo code as such:

primaryKey = "SELECT MAX(id) FROM table1;";
primary key++;
//id retrieved again from 2nd source

"INSERT INTO table1 (primaryKey, value 1, value 2);"

Trenton Trama · Accepted Answer · 2013-03-29 19:24:44Z

5

You're absolutely right, if two locations request at around the same time, you'll run into a race condition.

The way to handle this is to create a sequence in postgres and select the nextval as the primary key.

I don't know exactly what direction you're heading and how your handle your data, but you could also set the column as a serial and not even include the column in your insert query. The column will automatically auto increment.

edited Mar 29, 2013 at 19:24

answered Mar 29, 2013 at 19:07

Trenton Trama

4,9301 gold badge25 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Locking Tables with postgres in JDBC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related