Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Fundamentals › Database

Transactions

What does a transaction group together, and what do COMMIT and ROLLBACK do?

Taking 300 out of account 1 and adding 300 to account 2 takes two UPDATE statements. If only one of them is applied, money disappears or appears. A transaction turns several statements like these into one unit that is "either all applied or none applied". In code, the statement BEGIN starts the unit, and COMMIT or ROLLBACK ends it. COMMIT makes everything done in the unit final so that other connections can see it, and ROLLBACK cancels everything done in the unit. Changes that are not yet final cannot be seen from other connections. (In MySQL, the standard way is to write START TRANSACTION instead of BEGIN, and it means the same. H2, the database this lab was checked against, does not accept START TRANSACTION, so we wrote BEGIN.) In this lab, you remove BEGIN, change COMMIT to ROLLBACK, or remove the statement that ends the transaction, run the same two UPDATEs, and see how the balance read from another connection changes.

Checked against real execution · Ran all 6 combinations on H2 2.4.240 and checked them, and checked the rules against the MySQL documentation

Concepts in this lab

Transaction
A way to group several data changes into a single unit so that either all of them are applied (commit) or all of them are canceled (rollback). In a bank transfer, it prevents an intermediate state where only one of the withdrawal and the deposit is applied. It is the most basic means a database has for keeping data consistent.
Commit and rollback
A commit makes all the data changes in a transaction final in the database, and a rollback cancels all of those changes and returns to the state before the transaction started. A transaction ends in one of the two, and a change that has already been committed cannot be undone by a rollback. In Spring, when a method with @Transactional ends normally, Spring commits, and when it ends with an exception, Spring looks at the rollback rules and rolls back. Under the default rule, it rolls back when a RuntimeException or an Error is thrown out of the method, and it does not roll back for a checked exception. Without a transaction, each save statement is committed immediately by auto-commit, which is the default setting of a JDBC connection, so even if an exception occurs later, the earlier save cannot be rolled back.
Autocommit
Autocommit is the mode in which, when you have not started a transaction yourself, the database commits each SQL statement as soon as it runs. MySQL starts a new connection with autocommit on, so one statement is one transaction, and its result is final right away and is visible from other connections too. A statement made final this way cannot be undone with ROLLBACK. When you start a transaction, autocommit is off until you end it with COMMIT or ROLLBACK, and when it ends, autocommit returns to its previous state. If a session with autocommit off ends without committing its last transaction, MySQL (InnoDB) rolls that transaction back.
BEGIN, COMMIT, and ROLLBACK statements
BEGIN, COMMIT, and ROLLBACK are the SQL statements that start and end a transaction. BEGIN is the statement that starts a transaction, and in MySQL it means the same as START TRANSACTION (START TRANSACTION is recommended because it is standard SQL syntax). COMMIT makes permanent all the changes made so far inside the transaction, and ROLLBACK cancels all of them. By default autocommit is on, so without a statement that starts a transaction, COMMIT and ROLLBACK have no transaction to commit or cancel. Inside a MySQL stored program (a procedure, a trigger, and so on), BEGIN is read as the start of a BEGIN ... END block, so you must use START TRANSACTION, and the syntax of the statement that starts a transaction differs a little from database to database.

Try these

Running with the default settings

  1. CREATE TABLE and INSERT: The database creates the account table and inserts account 1 (balance 1000) and account 2 (balance 0). The SQL on the screen runs on one connection to the database (this connection). It is not in the code, but to check the result we also open one more connection to the same database (the other connection), and each time this connection runs a statement, we read the account balances. No transaction has started yet, so these two statements become final as soon as they run, and even when read from the other connection, account 1 is 1000 and account 2 is 0.
  2. BEGIN: The database starts treating the statements sent from this connection as one transaction. It pauses the mode in which every statement became final right away (autocommit) until this transaction ends, so from now on nothing becomes final until it meets COMMIT or ROLLBACK.
  3. UPDATE account SET balance = balance - 300 WHERE id = 1: The database changes the balance of account 1 from 1000 to 700. This is work inside the transaction, so it is not final yet. Read from this connection it is 700, but read from the other connection it is still 1000. Counting only the final values, the total of the two accounts is still 1000.
  4. UPDATE account SET balance = balance + 300 WHERE id = 2: The database changes the balance of account 2 from 0 to 300. This is also inside the transaction, so it is not final. This connection sees 700 and 300, but the other connection still sees 1000 and 0. The two changes are piled up in one transaction.
  5. COMMIT: The database makes the two changes piled up in the transaction final together. Now even when read from the other connection, account 1 is 700 and account 2 is 300. Making them final happens once for the whole transaction, so in this lab the values the other connection read were only 1000 and 0, or 700 and 300, and never a value with only one side changed. The transaction has ended, so this connection goes back to making every statement final right away.
  6. SELECT * FROM account ORDER BY id: This connection reads the accounts. The result is (1, 700), (2, 300). These are the same as the final values.
  7. After everything has ended, we read the accounts on a new connection. Account 1 is 700 and account 2 is 300, and the total is 1000. The whole transaction was applied.

Result of this run

Code

CREATE TABLE account (id INT PRIMARY KEY, balance INT);
INSERT INTO account VALUES (1, 1000), (2, 0);

-- Start: treat the statements from here on as one transaction
BEGIN;
UPDATE account SET balance = balance - 300 WHERE id = 1;
UPDATE account SET balance = balance + 300 WHERE id = 2;
-- End: make the whole transaction final
COMMIT;

SELECT * FROM account ORDER BY id;

Assumptions of this model

It was run and checked against H2 2.4.240 (in-memory database, JDBC), and it was not checked against execution on MySQL. It ran all 6 combinations, and it ran the whole SQL on the screen, including the comment lines, exactly as written, one statement at a time on one connection (this connection). It created a new in-memory database with a name (DB_CLOSE_DELAY=-1) for each combination so that the two connections look at the same database, and it left the isolation level at the H2 default, READ COMMITTED. What it checked is the three items of the result table, that is, the value that another connection, opened separately, read with SELECT balance FROM account WHERE id = 1 right after the first UPDATE, and the values of the two accounts read on a new connection after everything had ended (for the combination that was not ended, after this connection was closed). This checking query is not in the code on the screen, and there was no case where the query of the other connection waited or failed (it ran with a time limit of 3 seconds). As the facts the step sentences rely on, it also checked by execution the values of the two accounts that this connection and the other connection read at each statement, that autocommit is turned off after BEGIN and turned on again after COMMIT or ROLLBACK (getAutoCommit of JDBC), and the value that the last SELECT showed this connection. That closing the connection without ending the transaction cancels the changes that are not final is behavior observed when a JDBC connection was closed in H2, and we did not run other ways for a connection to drop (a network break, a server shutdown). The details that change with the isolation level (which values another connection can see, and whether it waits) were not covered, and are only the result seen with the H2 default. For the statement that starts a transaction, we wrote BEGIN because H2 2.4.240 does not accept START TRANSACTION (a syntax error). BEGIN ran on H2 2.4.240, but we could not find it on the Commands page of the H2 documentation. COMMIT, ROLLBACK, and SET AUTOCOMMIT are on that page. In section 15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements of the MySQL 8.4 documentation, it is written that BEGIN and BEGIN WORK are other names for START TRANSACTION (and that START TRANSACTION is standard SQL and is recommended), that inside a stored program BEGIN is read as the start of a BEGIN ... END block, so you must use START TRANSACTION, that during START TRANSACTION autocommit is off and returns to its previous state after COMMIT or ROLLBACK, that autocommit is on by default so each statement is final and cannot be undone with ROLLBACK, and that COMMIT makes changes permanent and ROLLBACK cancels changes. In section 17.7.2.2 autocommit, Commit, and Rollback (InnoDB), it is written that a new connection starts with autocommit on, and that if a session with autocommit off ends without committing its last transaction, MySQL rolls that transaction back. We opened the two sections above directly and checked them, but we did not check by execution that MySQL gives the same values for the same SQL. The code on the screen is the whole thing, including CREATE TABLE and INSERT, and the query of the other connection and the closing of the connection are not shown. This lab covers only the start and the end of one transaction, and does not cover isolation levels, locks, nested transactions, or Spring's @Transactional.

Previous lab: Indexes · Next lab: Row locks

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these