2

I know the transaction will be rolled back if connection breaks before commiting. But what if the commit operation reached the sql server and before it could respond to the client, the network breaks?

The transaction is committed on the sql server, how can the client then know if the transaction was committed or not?

0

The transaction is committed on the sql server, how can the client then know if the transaction was committed or not?

It can't. The application gets a network error because the connection has dropped. From that, it has no way of knowing whether the requested operation was successful.

It might be possible for the application to reconnect and then query the "current state of play" of the Transaction to determine whether it was successful. Realistically, though, in a Local Area Network scenario, the chances of this happening are so vanishingly small that it's barely worth worrying about.

However, if you're writing an app that will run on a hand-held device, collecting data somewhere in the middle of the Amazon rainforest over a particularly flaky cell-phone connection, this is just the sort of thing that you might need to do, effectively building a "State Machine" for the progress of each Transaction and only accepting that everything has completed when it reaches the "end state".

  • Unless you use a feature like Oracle Transaction Guard you end up with a commit on the database and an error (somewhat more or lesss clearly recognizeable as connection/transient) on the client. There is no standard way (besides transaction guard or Open XA transaction recovery) to query the state to verify. – eckes Aug 22 '18 at 19:05
2

The simple answer is, that a transaction is committed after responding to the client. And it's the client that's sending the commit, the server doesn't decide on its own, when a transaction is finished. (I'm having a hard time finding reliable sources for this, I must admit, but you have my word. It makes sense)

I tested something similar after it wasn't clear to me when a statement is written to the slow-query-log (the manual isn't clear about this). For this I throtteled my network bandwidth in Linux with netem. The result was, that the statement duration in the slow-query-log includes the time until the result is transmitted to the client.
Feel free to do a similar test :)

  • The explicit commit call can still succeed on the server and fail to be acknowledged to the client. Especially if the commit is slow and the network flaky. By not using auto commit the risk is a bit reduced (since the request and execution time does not count into that window) but it is not zilch. Having said that, it depends on driver, mode, dB product and settings. (See Oracle Replay Driver and Transaction Guard) – eckes Aug 22 '18 at 19:07
  • With Galera or Group Commint, a COMMIT can raise an error. So the client must wait for the reply from it. Still, if there is no reply (due to network outage), the situation is ambiguous. – Rick James Aug 30 '18 at 3:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.