Autocommit, Transactions, SAVEPOINT, Atomicity, and Application Error Boundaries

Learning outcomes

A dispatcher action often changes more than one row: reserve a part, assign a technician, mark a work order as open, and record an idempotency key. If the third step fails while the first two are already committed, the database can represent a business state that never actually happened. Transactions exist to make a group of changes an atomic unit—but only when every participating table/statement follows the transactional contract and the application controls transaction boundaries correctly.

MariaDB defaults to autocommit, so a naïve sequence of three DML statements is normally three transactions. START TRANSACTION creates an explicit boundary, SAVEPOINT allows partial rollback inside it, and COMMIT/ROLLBACK end it. DDL is a separate danger: many CREATE/ALTER/DROP and administrative statements implicitly commit. “I will rollback if anything goes wrong” is false when schema change has already crossed an implicit-commit boundary.

1. Autocommit means every successful statement can be its own transaction

With @@autocommit=1, a standalone DML statement commits when it completes successfully. That is convenient for independent writes but dangerous when the business operation spans multiple statements. Connector libraries often expose their own autocommit property, and defaults differ across languages and pools, so application code should set or verify the intended mode instead of relying on folklore.

USE servicehub_tx_lab;SELECT @@autocommit AS autocommit,       @@in_transaction AS in_transaction,       CONNECTION_ID() AS connection_id;START TRANSACTION;SELECT @@in_transaction AS in_transaction_after_start;ROLLBACK;SELECT @@in_transaction AS in_transaction_after_rollback;

in_transaction is a session read-only indicator: it is useful evidence that the current connection is inside a transaction. It does not tell you whether your application has accidentally checked out a different pooled connection between statements, so connector-level tests should log a connection/session identity when diagnosing transaction leakage.

2. Build one ServiceHub unit of work

Suppose request dispatch-1003-v1 must consume one filter cartridge and assign work order 1003 to technician 101. Both changes must commit together, and the request key must make a duplicate application retry detectable. The transaction can encode that rule directly.

START TRANSACTION;INSERT INTO request_dedup(request_key, operation_name, work_order_id)VALUES ('dispatch-1003-v1','dispatch',1003);UPDATE parts_stockSET qty_on_hand = qty_on_hand - 1WHERE part_id=1 AND qty_on_hand >= 1;UPDATE work_ordersSET technician_id=101,    status='open',    version_no=version_no+1WHERE work_order_id=1003 AND status='queued';SELECT ROW_COUNT() AS work_order_rows_changed;COMMIT;

This is intentionally simplified: production code should verify that the stock UPDATE changed exactly one row and that the work-order transition changed exactly one row before COMMIT. If either count is zero, the application should ROLLBACK and return a domain error such as “out of stock” or “already dispatched.” The unique request key prevents a retry from silently performing the same business operation twice.

3. Deliberately fail in the middle and prove rollback

Reset the lab, then repeat the operation with a failing statement before COMMIT. A foreign-key or duplicate-key error is useful because it is deterministic and does not require damaging the server. After the error, issue ROLLBACK explicitly and verify that stock, work order and idempotency row all returned to their baseline state.

START TRANSACTION;INSERT INTO request_dedup(request_key, operation_name, work_order_id)VALUES ('dispatch-1003-fail','dispatch',1003);UPDATE parts_stock SET qty_on_hand=qty_on_hand-1 WHERE part_id=1;-- Deliberate foreign-key failure: customer 999 does not exist.UPDATE work_orders SET customer_id=999 WHERE work_order_id=1003;ROLLBACK;SELECT qty_on_hand FROM parts_stock WHERE part_id=1;SELECT * FROM request_dedup WHERE request_key='dispatch-1003-fail';SELECT customer_id,status,technician_id FROM work_orders WHERE work_order_id=1003;

Do not assume every SQL error automatically rolls back the entire transaction. Error behavior is statement- and condition-specific; applications should know whether the connector keeps the transaction open and should normally issue an explicit ROLLBACK on an aborted unit of work before reusing the connection. Deadlock and lock-wait timeout behavior is treated separately in Lesson 5.

4. SAVEPOINT creates a partial rollback marker, not a nested transaction

A SAVEPOINT names a position inside the current transaction. ROLLBACK TO SAVEPOINT undoes changes after that marker while keeping the outer transaction active. This is useful when an optional sub-operation can fail without discarding the whole business unit. It is not an independent nested transaction with its own durable COMMIT.

START TRANSACTION;UPDATE work_ordersSET priority=2, version_no=version_no+1WHERE work_order_id=1001;SAVEPOINT before_optional_stock;UPDATE parts_stock SET qty_on_hand=qty_on_hand-50 WHERE part_id=2;-- Application validation decides this optional step is invalid.ROLLBACK TO SAVEPOINT before_optional_stock;UPDATE work_ordersSET status='open'WHERE work_order_id=1001;COMMIT;SELECT work_order_id,status,priority,version_no FROM work_orders WHERE work_order_id=1001;SELECT part_id,qty_on_hand FROM parts_stock WHERE part_id=2;

Rolling back to a savepoint does not release metadata locks acquired by the transaction. That matters when long-running workflows touch tables and later DDL waits behind them. Savepoints reduce data-change scope; they do not reset every concurrency side effect of the outer transaction.

5. DDL can commit behind your rollback plan

MariaDB documents many DDL and administrative statements as implicit-commit boundaries. A sequence such as INSERT → ALTER TABLE → ROLLBACK is therefore not one rollback-safe unit: the ALTER can commit the preceding data change and run as its own transaction boundary. This is why application migrations and transactional business DML should be separated operationally.

START TRANSACTION;UPDATE work_orders SET priority=9 WHERE work_order_id=1001;-- DDL causes an implicit commit boundary.ALTER TABLE work_orders ADD COLUMN lab_marker INT NULL;ROLLBACK;SELECT priority, lab_marker FROM work_orders WHERE work_order_id=1001;ALTER TABLE work_orders DROP COLUMN lab_marker;UPDATE work_orders SET priority=1 WHERE work_order_id=1001;

The exact DDL algorithm and locking behavior is version-sensitive, but the transaction lesson is stable: do not depend on ROLLBACK to undo ordinary schema DDL. Temporary-table DDL is a special case: some temporary-table CREATE/ALTER/DROP operations do not cause the same implicit commit, yet the temporary object operation itself is not rollbackable. Treat that as an exception requiring explicit testing, not as a general “transactional DDL” feature.

6. Application error boundaries and idempotency

A database transaction can make database changes atomic, but it cannot roll back an HTTP response already sent, a message already published to an external broker, or a payment already captured by another service. A robust application boundary distinguishes database work from external side effects and uses patterns such as idempotency keys, outbox tables, or compensating actions when atomic cross-system commit is unavailable.

For the ServiceHub lab, the request_dedup row belongs in the same InnoDB transaction as the business changes. If the transaction rolls back, the dedup row rolls back too. If it commits, a retry with the same request key receives a unique-key conflict that the application can interpret as “already handled” rather than applying inventory/work changes again.

SELECT request_key, operation_name, work_order_id, created_atFROM request_dedupWHERE request_key='dispatch-1003-v1';

7. Connection-pool hygiene is part of transaction correctness

A pooled connection is a long-lived server session reused by many application requests. Session variables, isolation level, autocommit mode, temporary tables and an accidentally open transaction can therefore leak from one request into the next if the pool or application does not reset state. The database may be perfectly transactional while the service is still wrong because request B inherits request A’s session contract.

A robust pool checkout/check-in policy should make the expected autocommit and isolation level explicit, reject or rollback connections that return to the pool with an active transaction, and avoid leaving transaction-scoped settings ambiguous. When debugging, capture both the application request ID and CONNECTION_ID(); this lets logs prove whether two statements that were supposed to share one transaction actually ran on the same MariaDB session.

SELECT CONNECTION_ID() AS connection_id,       @@autocommit AS autocommit,       @@in_transaction AS in_transaction,       @@transaction_isolation AS isolation_level;

Do not implement “nested transactions” by blindly issuing START TRANSACTION from a library function that may already be inside one. Define ownership: either the outer application layer owns commit/rollback and inner components use savepoints, or each component receives a connection whose transaction state is known. This design decision belongs in integration tests because many transaction bugs come from connection lifecycle rather than SQL syntax.

8. Lab checklist, knowledge check and production judgment

1. Reset the lab and record @@autocommit/@@in_transaction.
2. Run the successful dispatch as one transaction and verify all three tables after COMMIT.
3. Reset, run the deliberate FK failure, issue ROLLBACK, and prove no partial state remains.
4. Run the SAVEPOINT example and prove the optional stock change is undone while the outer work-order change commits.
5. Run the implicit-commit DDL drill only in the disposable lab and explain why ROLLBACK cannot restore the pre-DDL transaction.
6. Repeat the same request key and design the application response to the unique-key conflict.

9. Summary and bridge

A transaction is an application correctness boundary, not simply a BEGIN/COMMIT wrapper. Make autocommit explicit, keep all participating writes transactional, check affected-row invariants, use SAVEPOINT for optional substeps, rollback on failure, and keep DDL out of rollback assumptions because MariaDB commonly commits around it. Idempotency must be designed alongside the transaction when retries are possible.

Next, the question becomes what concurrent transactions can see. You will use two sessions to distinguish consistent snapshot reads from current/locking reads and compare READ COMMITTED, REPEATABLE READ and SERIALIZABLE behavior instead of memorizing anomaly names in isolation.