Ability to rollback changes when insert data using a server API

Question

I currently have the following situation:

I have build a client Java application, which used to communicate with the MySQL database directly. When I was inserting a lot of data, I used to do this with AutoCommit turned off. This was easy to do, because I had direct access to the Connection. I had chosen to do this, because it enabled me to rollback the changes when something went wrong during the synchronization.

Now, the application is evolving and I thought I would be better to build a server API which communicates with the MySQL database. So, at the moment, I am inserting data by doing Http requests. Each request opens and closes a new Connection.

Now, I would like to be able to rollback the changes when one of the requests goes wrong. I assume I can not work with AutoCommit, because this is based on a Connection, and that one is different for each request.

Can anyone tell me how this is done usually?

I have thought of the following:

• First call an URL which sets the AutoCommit to false, and then do all the requests and check if one fails. But this would go wrong if another client is also inserting data at the same time.
• Sending the data to the server with one request, but this would force me to alter my design drastically.

Note: I know some code is usually required when asking a question, but I can not see how this would improve my question. However, if it is needed, feel free to request it.

Answer 1

Every web request should run in it's own transaction.

Send all the data that belongs to a logical transaction in one request and call commit at the end of the request processing.

Spaning transactions accros multiple web requests is a bad idea. Think about crashing clients which never commit. This will leave you with open transactions that never close.