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