sql-servertransactionsjpa-2.0wan

What does sql server do when a JPA transaction fails across a network?


I'm using JPA to connect to an SQL server across a WAN. I've been unable to find information on what happens when I begin a JPA transaction that involves writes to the remote DB, but the WAN connection goes down before or during commit.

In each transaction, I'm transmitting a header and several hundred detail lines.

Does the far-end database know enough to discard all the changes?

Obviously, requesting a rollback on the local application isn't going to have any effect since the WAN link is down.


Solution

  • I presume:

    Then:

    It does not matter if you are using sql-server via WAN or LAN. Either the transaction is done completely, or not at all. That is the nature of transactions.

    So if the connection goes down before the commit, the server will rollback everything. There is no way to reconnect on application level, to complete the transaction.

    If the connection goes down during the commit, then dependent on the implementation and on the exact point in time, the transaction might be persisted completely or rolled back completely.

    You can be absolutely sure that everything is persisted as intended as soon as commit returns to your code.

    Beware, that "connection goes down" might happen after a timeout that might be quite long (several minutes). In that time, the transactions keep all the locks and might slow down the complete system. These timeouts might be set to longer intervals if you are communicating via slower network.