Why does Spring data jdbc run a select statement when inserting?

Question

I have a deadlock that baffles me. It happens in my spring application upon inserting into a Table without an active transaction. I use the save method on the SimpleJdbcRepository. This question is not about solving the Deadlock but about why it happens.

The best I can guess so far, it for some reason 'save' starts a transaction, runs at least one select statement and somehow acquires locks and runs into a deadlock. But that would actually require at least 2 select statements. It shouldn't do any upon inserting. Also I cannot reproduce it locally, the jdbc logs only show the insert statement.

Does someone have any clues why Spring data jdbc runs these extra select statements?

Bonus question, how do I get rid of them?

Here is the stacktrace as part of the deadlock exception. Best I can tell the save function triggers the select that triggers the deadlock:

org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 315008 waits for ShareLock on transaction 11699360; blocked by process 315268.
 Process 315268 waits for ShareLock on transaction 11699361; blocked by process 315008.
  Hint: See server log for query details.
  Where: while locking tuple (5,18) in relation "journey"
 SQL statement "SELECT 1 FROM ONLY "my_schema"."user" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
    at org.springframework.jdbc.core.JdbcTemplate.lambda$update$3(JdbcTemplate.java:992)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:991)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:356)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:340)
    at org.springframework.data.jdbc.core.convert.IdGeneratingInsertStrategy.execute(IdGeneratingInsertStrategy.java:67)
    at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.insert(DefaultDataAccessStrategy.java:117)
    at org.springframework.data.jdbc.core.JdbcAggregateChangeExecutionContext.executeInsertRoot(JdbcAggregateChangeExecutionContext.java:77)
    at org.springframework.data.jdbc.core.AggregateChangeExecutor.execute(AggregateChangeExecutor.java:66)
    at org.springframework.data.jdbc.core.AggregateChangeExecutor.lambda$execute$0(AggregateChangeExecutor.java:52)
    at java.base/java.util.ArrayList.forEach(Unknown Source)
    at org.springframework.data.relational.core.conversion.DefaultAggregateChange.forEachAction(DefaultAggregateChange.java:127)
    at org.springframework.data.jdbc.core.AggregateChangeExecutor.execute(AggregateChangeExecutor.java:52)
    at org.springframework.data.jdbc.core.JdbcAggregateTemplate.store(JdbcAggregateTemplate.java:360)
    at org.springframework.data.jdbc.core.JdbcAggregateTemplate.save(JdbcAggregateTemplate.java:161)
    at org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.save(SimpleJdbcRepository.java:78)

score 1 · Answer 1 · answered Feb 09 '23 at 16:27

Not a complete answer but too much for a comment:

if you have a TransactionManager the save method will run in a transaction.
Spring Data JDBC isn't issuing a select statement. You can tell from at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:991) that it actually executes an update/insert. So the query you are seeing is probably created by Postgres itself.

Why does Spring data jdbc run a select statement when inserting?

1 Answers1