Impact of Storage Engines on Transaction Handling

In MyBatis development, developers often assume that every data modification requires an explicit sqlSession.commit() call to persist changes to the database. While this holds true for most production scenarios, the actual persistence behavior depends heavily on the underlying storage engine, system-level transaction flags, and the lifecycle management of the SqlSession object.

Not all relational database systems enforce strict ACID compliance across every operation. When interacting with MySQL, the chosen storage engine dictates whether transactions are supported:

• MyISAM: This legacy engine does not support transactions. Every DML statement executes immediately as its own atomic unit. Issuing a commit() or rollback() in MyBatis has no effect because the storage layer ignores transactional boundaries.
• InnoDB: The default and widely used engine fully supports transactions, foreign keys, and row-level locking. Data persistence is strictly governed by transaction control mechanisms.

You can verify your current environment's automatic commit behavior by querying MySQL's system variables:

SHOW VARIABLES LIKE 'autocommit';

An output value of ON indicates that individual statements are automatically committed unless grouped within an explicit transaction block. Setting it to OFF requires developers to manually manage transaction boundaries.

Practical Scenarios Without Explicit Commits

Consider a scenario where a MyBatis mapper attempts to insert a record without invoking session.commit(). Depending on the underlying setup, the outcome varies significantly.

Case 1: Non-Transactional Engine Execution

When targeting a MyISAM table, the execution flow bypasses transaction management entirely:

public class UserPersistenceManager {
    private SqlSession session;

    public void persistUser(UserRecord user) throws IOException {
        InputStream configStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(configStream);
        session = factory.openSession();
        
        try {
            int affectedRows = session.insert("com.example.mapper.UserMapper.insertUser", user);
            System.out.println("Executed query returning: " + affectedRows);
        } finally {
            if (session != null) {
                session.close();
            }
        }
    }
}

Even without session.commit(), the data persists immediately because MyISAM treats each statement as an autonomous write operation.

Case 2: Transactional Engine Behavior

Switching to InnoDB introduces strict transactional boundaries. Define the table structure below:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    balance DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

Running the repository logic against this schema yields a misleading result. The application console reports successful execution, yet a direct database inspection shows an empty table. This occurs because InnoDB buffers the changes within an active transaction scope that never reached the COMMIT state. Since the session was closed without committing, the pending changes were discarded rather than flushed to disk.

Explicit Commit vs. Session Cleanup

To guarantee data persistence with InnoDB, you must explicitly invoke session.commit():

public class UserPersistenceManager {
    private SqlSession session;

    public void persistUser(UserRecord user) throws IOException {
        InputStream configStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(configStream);
        session = factory.openSession();
        
        try {
            session.insert("com.example.mapper.UserMapper.insertUser", user);
            session.commit(); 
        } catch (Exception e) {
            if (session != null) {
                session.rollback(); 
            }
            throw e;
        } finally {
            if (session != null) {
                session.close();
            }
        }
    }
}

Observe the auto-increment primary key behavior during debugging. If you attempt an ensert without committing, then run the commit version later, you might observe a gap in sequential IDs (e.g., jumping from 1 to 2). This happens because the first uncommitted execution allocated an ID sequence number but aborted due to missing COMMIT. The second successful execution claimed the subsequent available ID. You can manually trigger this discard behavior using session.rollback() if needed.

Automatic Rollback During Session Termination

A critical aspect of MyBatis lifecycle management is the interaction between close() and pending transactions. Calling session.close() without a prior commit() automatically triggers an implicit rollback. The underlying JDBC connection releases acquired locks and discards uncommitted work. Therefore, relying on close() alone prevents accidental partial writes, but it will also discard intended changes if commit() was omitted. For reliable data manipulation, always pair openSession() with either an explicit commit() or a structured exception-handling block that ensures rollback paths are executed before terminating the session.