Why transactions are needed
Very often, when working with a database, a situation arises when you need to perform many different actions, but they only make sense together.
For example, we are writing banking software that should do three things:
- Withdraw money from customer's account
- Add money to the recipient's account
- Record the posting data in the “posting log”
If an error occurs during the execution of any of these actions, then the other two must also be canceled. It is impossible to write off money from the client and not add it to the recipient? Well, or add to the recipient, but not write off from the client?
So, such a logical grouping of different actions into one is called a transaction . In other words, a transaction is a group of actions that must be performed only all together . If any action failed or was executed with an error, then all other actions must be canceled.
A transaction usually has three states:
- initial state - the state of the system before executing a group of actions
- success state - state after the action group is completed
- failed state - something went wrong
In this case, there are usually three commands:
- begin/start - executed before the start of the logical group of actions
- commit - executed after the transaction action group
- rollback - starts the process of returning the system from failed state to initial state
It works like this.
First you need to open a transaction - call the begin() or start() method . Calling this method indicates the state of the system to which we will try to return if something goes wrong.
Then all actions are performed, which are combined into a logical group - a transaction.
Then the commit() method is called . Its call marks the end of a logical group of actions, and also usually starts the process of putting these actions into practice.
Recall how we wrote something in FileWriter: first, everything we wrote is stored in memory, and then when the flush () method is called , all the data from the buffer in memory is written to disk. This flush() is the transaction commit.
Well, if an error occurred during the operation of the transaction, then you need to initiate the process of returning to the starting state. This process is called rollback() , and the method of the same name is usually responsible for it.
Roughly speaking, there are 2 ways to complete a transaction:
- COMMIT - we confirm all the changes made
- ROLLBACK - roll back all changes made
Transactions in JDBC
Almost every DBMS can work with transactions. So JDBC also has support for this case. Everything is implemented very simply.
First, each call to the execute() method of the Statement object is executed in a separate transaction. To do this, Connection has an AutoCommit parameter . If it is set to true , then commit() will be called after each call to the execute() method .
Secondly, if you want to execute several commands in one transaction, then you can do it like this:
- disable AutoCommit
- calling our teams
- call the commit() method explicitly
It looks very simple:
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
int rowsCount1 = statement.executeUpdate("UPDATE employee SET salary = salary+1000");
int rowsCount2 = statement.executeUpdate("UPDATE employee SET salary = salary+1000");
int rowsCount3 = statement.executeUpdate("UPDATE employee SET salary = salary+1000");
connection.commit();If an error occurs on the server while the commit() method is running , then the SQL server will cancel all three actions.
But there are situations when the error still occurs on the client side, and we never got to the commit() method call :
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
int rowsCount1 = statement.executeUpdate("UPDATE employee SET salary = salary+1000");
int rowsCount2 = statement.executeUpdate("UPDATE employee SET salary = salary+1000");
int rowsCount3 = statement.executeUpdate("UPDATE multiple typos will result in an exception");
connection.commit();If an error occurs during the execution of one executeUpdate() , then the commit() method will not be called. To roll back all actions taken, you need to call the rollback() method . It usually looks like this:
try{
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
int rowsCount1 = statement.executeUpdate("UPDATE employee SET salary = salary+1000");
int rowsCount2 = statement.executeUpdate("UPDATE employee SET salary = salary+1000");
int rowsCount3 = statement.executeUpdate("UPDATE multiple typos will result in an exception");
connection.commit();
}
catch (Exception e) {
connection.rollback();
}Savepoints
With the advent of JDBC 3.0, it became possible to work more efficiently with transaction rollback. Now you can set save points - save points, and when you call the rollback () operation , roll back to a specific save point.
In order to save, you need to create a savepoint, this is done with the command:
Savepoint save = connection.setSavepoint();Reverting to a savepoint is done with the command:
connection.rollback(save);Let's try adding a savepoint before our problematic command:
try{
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
int rowsCount1 = statement.executeUpdate("UPDATE employee SET salary = salary+1000");
int rowsCount2 = statement.executeUpdate("UPDATE employee SET salary = salary+1000");
Savepoint save = connection.setSavepoint();
try{
int rowsCount3 = statement.executeUpdate("UPDATE multiple typos will result in an exception");
}
catch (Exception e) {
connection.rollback(save);
}
connection.commit();
}
catch (Exception e) {
connection.rollback();
}We organized nested transactions by adding a save-point before calling the problematic method, and returning to the saved state by calling the rollback(save) method .
Yes, it's very similar to save/load in games.
GO TO FULL VERSION