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?

`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. — Mark Rotteveel, May 29 '19 at 07:21
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. — Arya, May 29 '19 at 14:54
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. — Mark Rotteveel, May 29 '19 at 14:56
@MarkRotteveel This given answer is for doing `select for update` using plain JDBC. https://stackoverflow.com/a/46995306/492015 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 — Arya, May 29 '19 at 15:01
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. — Mark Rotteveel, May 29 '19 at 15:45
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 — Arya, May 29 '19 at 16:19
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. — Mark Rotteveel, May 29 '19 at 16:49
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 — Arya, May 29 '19 at 17:27
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194129/discussion-between-arya-and-mark-rotteveel). — Arya, May 29 '19 at 17:53
That is basically just the answer of the question you linked. In any case, why can't you use `UPDATE` for this directly? — Mark Rotteveel, May 30 '19 at 09:11
@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. — Arya, May 30 '19 at 16:14

score 0 · Accepted Answer · answered May 29 '19 at 18:17

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;
}

I tried it without turning off AutoCommit and it looks like it's working fine — Arya, May 29 '19 at 18:38
You don't need to setAutoCommit in the catch block, the finally block always gets called. — findusl, Oct 19 '22 at 14:58

Spring jdbc 'select for update'

1 Answers1

Linked