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?

Improve this question

2 Answers 2

Reset to default
1

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".

Improve this answer
1
  • 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, 2018 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 :)

Improve this answer
3
  • 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, 2018 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, 2018 at 3:11
  • But what if right after responding to the client and before the transaction is committed, the DB crashes? Then when the DB is restarted, the transaction will be rolled back incorrectly, breaking the durability.
    – Jason Lee
    Feb 10 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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