select "for update" with JDBC?

Question

I want to create a for update select statement in Java using JDBC, but not sure how it would be done.

If you are unfamiliar with for update you can read about it here https://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE

For example, I have the following select statements

My select statement

select email from email_accounts where already_linked = false order by random() limit 1

My update statement

UPDATE email_accounts set already_linked = true, account_link_timestamp = now() where email = ?

How would this be done in Java using JDBC while using for update?

Solution

You first add for update to your select (and your other columns you want to update), and then you update them. Also, as noted in the comments, make sure your getConnection returns a Connection without autocommit. And you need to set a Statement type for scrolling and CONCUR_UPDATABLE. Something like,

String[] colNames = { "email", "already_linked", "account_link_timestamp" };
String query = "select " + Stream.of(colNames).collect(Collectors.joining(", "))
        + "from email_accounts where already_linked = false for update";
try (Connection conn = getConnection(); // Make sure conn.setAutoCommit(false);
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stmt.executeQuery(query)) {
    while (rs.next()) {
        // Get the current values, if you need them.
        String email = rs.getString(colNames[0]);
        boolean linked = rs.getBoolean(colNames[1]);
        Timestamp time = rs.getTimestamp(colNames[2]);
        // ...
        rs.updateBoolean(colNames[1], true);
        rs.updateTimestamp(colNames[2], //
                new Timestamp(System.currentTimeMillis()));
        rs.updateRow();
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Comments

[Gord Thompson]

Is conn.setAutoCommit = false necessary for the for update locks to be effective, or does it not matter?

[Gord Thompson]

Also, doesn't the createStatement need to be called with ResultSet.CONCUR_UPDATABLE so updateRow will work?

[Elliott Frisch]

@GordThompson Good catch, thanks. Yeah. It was dinner time, and I got a bit in a hurry.

[Arya]

conn.setAutoCommit(false); right above the while loop would be ok?

[Gord Thompson]

@Arya - That should be okay, but you'll also need to remember to conn.commit() the changes, too.

[Arya]

@GordThompson something is wrong, the sql query is correct and works when I execute it using psql, but I get the following error when I run it in Java "org.postgresql.util.PSQLException: No primary key found for table email_accounts." even though I do have a primary key for the table. Here is the link to the method I am using pastebin.com/kcQvWJUx

[Mick Mnemonic]

You probably need to include the primary key column in the SELECT to make the result set updateable over JDBC, i.e. add it to colNames.

[Arya]

Do you know if it's possible use prepared statements with for update?

[sagneta]

Excellent response. Worked for me on both MSSqlServer and Oracle. Thank you again.

[Arya]

@ElliottFrisch Do you know how this would be done with Spring JDBC? no one seems to know how to do it with Spring JDBC stackoverflow.com/questions/56350833/…

[theyuv]

In the portion of your code where we are supposed to run the business logic for the update (ie: between rs.getTimestamp() and rs.updateBoolean) are we supposed to keep the logic as quick and simple as possible? I usually run into issues if I keep a connection open while executing other code. Is there a way to do the SELECT FOR UPDATE where I close the connection after the SELECT and open it before the UPDATE? Or does this not make sense?

[Prosenjit]

I am not able to use this for a group of records all having status say =0, we always need to have Primary Key in the select for update statement, else update on ResultSet is throwing java.sql.SQLException: ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc. I am looking to use rs.updateXXX() where in the select query I can lock multiple records (say 100). Dont see a way around.

[JRichardsz]

autocommit must be disabled in for it to work: stackoverflow.com/a/22849532/3957754

[Elliott Frisch]

@JRichardsz make sure your getConnection returns a Connection without autocommit