Search code examples
databasepostgresqltransactionsspring-jdbcspring-transactions

What happens if the response to the commit from the database is not received?

If I have method with @Transaction(with default isolation level) and after all it send commit to DB, DB set transaction's visible for other and send response back to me, but network is lagging and I receive timeout. What happens? Transaction will be rollbacked?

Solution

  • If the COMMIT statement times out, the client cannot be certain whether the transaction was committed or not. It could have been committed successfully, but the network broke when sending the response to the client, or the COMMIT statement could have been canceled by the timeout.

    If that worries you, you could get the transaction ID while the transaction is in progress:

    SELECT pg_current_xact_id();

    Then, after the timeout, you can run

    SELECT pg_xact_status(123456);

    to see if that transaction was committed or not (replace 123456 with the result from the first query).

    In most cases, COMMIT is a very fast an unproblematic operation, and it shouldn't give you trouble.