Transactions with MariaDB Connector/J

Overview

Java developers can use MariaDB Connector/J to perform basic DML (Data Manipulation Language) operations inside a transaction with MariaDB Enterprise.

Transactions

A database transaction is a single unit of logic. A transaction may consist of one or more database operations. Transactions are useful and sometimes essential in several types of data operations.

For example, many applications require that a set of SQL statements either complete, or fail, as a single unit. The common characteristics of transactions are atomicity, consistency, isolation, and durability, what is termed as ACID (atomic, consistent, isolated, durable) transactions.

MariaDB transactions are ACID compliant.

Auto-Commit Behavior

By default, MariaDB Connector/J enables auto-commit. When auto-commit is enabled, each SQL statement is executed in its own transaction.

Confirm that auto-commit is enabled by calling Connection.getAutoCommit():

if (conn.getAutoCommit()) {
   // do something
}

Multi-Statement Transactions

MariaDB Connector/J supports multi-statement transactions when auto-commit is disabled.

Disable auto-commit by calling Connection.setAutoCommit():

conn.setAutoCommit(false);

When auto-commit is disabled, a new transaction is automatically started when the current transaction is manually committed or rolled back. This means your application does not need to manually start each new transaction with BEGIN or START TRANSACTION.

The transaction can be manually managed by performing the following operations:

  • Setting the transaction isolation by calling Connection.setTransactionIsolation() or using SET TRANSACTION ISOLATION LEVEL.

  • Setting the transaction to read-only by calling Connection.setReadOnly() or using SET TRANSACTION READ ONLY.

  • Creating a savepoint by calling Connection.setSavepoint() or using SAVEPOINT.

  • Rolling back to a savepoint by calling Connection.releaseSavepoint() or using RELEASE SAVEPOINT.

  • Committing the transaction by calling Connection.commit() or using COMMIT.

  • Rolling back the transaction by calling Connection.rollback() or using ROLLBACK.

Code Example: Transactions

The following example program updates the test.accounts table created in the example setup:

import java.sql.*;

public class App {

    public static void main(String[] argv) {

        try (Connection conn = DriverManager.getConnection("jdbc:mariadb://192.0.2.1:3306?user=db_user&password=db_user_password")) {

            conn.setAutoCommit(false);

            try {
                try (PreparedStatement prep = conn.prepareStatement("INSERT INTO test.accounts(first_name, last_name, email, amount) VALUES (?, ?, ?, ?)")) {

                    prep.setString(1, "John");
                    prep.setString(2, "Smith");
                    prep.setString(3, "john.smith@example.com");
                    prep.setDouble(4, 900.00);
                    prep.executeQuery();
                }

                try (PreparedStatement prep = conn.prepareStatement("UPDATE test.accounts SET amount = ? WHERE email = ?")) {

                    prep.setDouble(1, 1000.00);
                    prep.setString(2, "john.smith@example.com");
                    prep.executeQuery();
                }

                conn.commit();

            } catch (SQLException e) {

                e.printStackTrace();
                conn.rollback();
            }
        } catch (Exception e) {

            e.printStackTrace();
        }
    }
}

Confirm that the test.accounts table was properly updated using MariaDB Client to execute a SELECT statement:

SELECT * FROM accounts;

Example output:

+----+------------+-----------+------------------------+---------+
| id | first_name | last_name | email                  | amount  |
+----+------------+-----------+------------------------+---------+
|  1 | John       | Smith     | john.smith@example.com | 1000.00 |
+----+------------+-----------+------------------------+---------+