Spring jdbc 'select for update'

Question

I have the following method that I use with Spring JDBC

public String getState() {
    String stateLink = template.queryForObject(
            "select state_url from state_scrape_queue where in_use = false ORDER BY scrape_timestamp NULLS FIRST LIMIT 1",
            (result, rowNum) -> {
                return result.getString("state_url");
            });
    return stateLink;
}

I can't find an example of how to do a for update with Spring JDBC. I want in_use to be set to true using for update.

I need to use select for update since this application will be used in a multi-threaded fashion. I don't want more than one thread to get the same row and the way to prevent that is by using select for update

I was able to do this with plain JDBC, here is the question I asked how to do it with plain JDBC

select "for update" with JDBC?

Anyone know how this would be done?

Relieve

FOR UPDATE doesn't update anything, it only locks selected rows as if it was updated. So you can't set something to true using FOR UPDATE, you would need to execute a separate UPDATE statement. In any case, things work the same using spring-jdbc, as it would executing statements directly. As it stands, it is unclear what you're really asking. – Morrismorrison

For update has its purpose that's why it exists. It's very useful in multithreaded applications. It prevents another thread to get the same row because using select for update locks the row and in this example will update in_use to true, so another thread will not get the same row. – Relieve

A for update clause in a select will not update a row, it will only lock the row so concurrent transactions can't update and - depending on the lock model - can't read that row. Your assumption that it will change in_use to true is wrong. – Morrismorrison

@MarkRotteveel This given answer is for doing select for update using plain JDBC. https://mcmap.net/q/1616988/-select-quot-for-update-quot-with-jdbc I have used it for a very long time and it works perfectly. Two threads never got the same row. I just need to do the same thing with Spring JDBC – Relieve

And it will work no different using JDBCTemplate. The primary difference is probably your transaction boundary. Are any of the methods in the chain calling this method @Transactional, if not, the transaction has been committed and the locks released at the time queryForObject returns. – Morrismorrison

I think auto-commit would need to be off for this to work? And since Spring JDBC is using connection pooing, then it would be a hassle to implement this. I may just use plain JDBC for select for update methods – Relieve

Connection pooling has nothing to do with it, it has to do with the lifetime of the transaction. Also note that the plain JDBC example you link actually explicitly updates through the result set, which is not what your code is doing: that just selects data. – Morrismorrison

I need to modify my code to make it work. It looks like I need to override a method inside getState to set autoCommit to false, then call the select for update to lock the row, then do the update and then commit – Relieve

Let us continue this discussion in chat. – Relieve

@MarkRotteveel what do you think of the answer? – Relieve

That is basically just the answer of the question you linked. In any case, why can't you use UPDATE for this directly? – Morrismorrison

@MarkRotteveel pretty much, the only difference is that it gets the connection from the connection pool. Because In the time between selecting and updating another thread can get the same row. – Relieve

Answer 1

This is what I came up with, feel free to recommend improvements

public String getState() throws SQLException {
    String state = null;

    Connection conn = DataSourceUtils.getConnection(template.getDataSource());
    try {
        conn.setAutoCommit(false);

        String[] colNames = { "id", "state_url", "in_use" };
        String query = "select " + Stream.of(colNames).collect(Collectors.joining(", "))
                + " from state_scrape_queue where in_use = false ORDER BY scrape_timestamp NULLS FIRST LIMIT 1 FOR UPDATE";
        System.out.println(query);
        try (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.
                state = rs.getString(colNames[1]);

                rs.updateBoolean(colNames[2], true);
                rs.updateRow();
                conn.commit();
            }
        }
    } catch (SQLException e) {
        conn.setAutoCommit(true);
        e.printStackTrace();
    } finally {
        conn.setAutoCommit(true);
    }

    return state;
}

Relieve

I tried it without turning off AutoCommit and it looks like it's working fine – Relieve

You don't need to setAutoCommit in the catch block, the finally block always gets called. – Dramshop