Connections and Transactions

Introduction

What is it? Part 2 answers two questions. First, how does an application actually talk to MySQL? (Through connections, usually managed by a connection pool.) Second, how can several SQL statements behave like one single unit of work? (Through transactions.)

Why do we need it? Many real actions need more than one SQL statement. Moving 200 from one bank account to another needs two updates: subtract from one account, add to the other. If the system crashes after the first update, money disappears. A transaction makes the two updates all or nothing: either both happen, or neither does.

Real-world analogy Think of filling out a paper form in pencil. You write several answers, but nothing is final until you hand in the form. If you make a mistake, you erase everything and start over (ROLLBACK). When you are happy, you hand it in and the answers become permanent (COMMIT). A SAVEPOINT is like a folded corner on the page: "if I have to erase, only erase back to here."

Business use case An online course platform enrolls a student and charges their wallet. Both steps must succeed together. If charging fails, the enrollment must not remain, or the student would get a course for free.

Dataset Used

The same students table from Part 1 is used, plus two assumed tables for the savepoint example.

students

enrollments (assumed columns, as used in the INSERT)

accounts (assumed)

Syntax

Mental Model

 Without a transaction (auto-commit)        With a transaction

 UPDATE 1  --> saved at once                START TRANSACTION
 UPDATE 2  --> saved at once                UPDATE 1   (held, not final)
                                            UPDATE 2   (held, not final)
 A crash between 1 and 2                    COMMIT     --> both saved
 leaves half the work done                  or ROLLBACK --> both cancelled

A transaction is atomic: all or nothing.

How SQL Executes It

 START TRANSACTION
        |
        v
 Run statements (changes are held inside the transaction)
        |
        +----------------------+
        |                      |
        v                      v
     COMMIT                ROLLBACK
  changes become         changes are thrown away,
  permanent and          data returns to the state
  visible to others      before START TRANSACTION

Auto-commit Outside a transaction, MySQL normally commits each statement by itself, right after it runs. Inside a transaction, nothing is final until you write COMMIT.

1. Connections and Connection Pools

What is a connection?

A connection is one live communication channel between your application and the MySQL server.

• Every connection has a connection_id.
• A connection is stateful. Session variables and transaction state live inside that one connection only.
• If the connection is disconnected or killed, that session state is gone.

Why did the connection_id keep changing (73, 74, 75, 76)?

The tool was using a connection pool and picking connections in round-robin order. So each query could run on a different connection from the pool.

What is a connection pool?

A connection pool is a manager that keeps several database connections open and reuses them.

• Opening a new connection again and again is slow and expensive.
• Apps handle many requests at once, and one connection would make them wait.
• The pool hands out a free connection, the query runs, and the connection goes back to the pool.

Why not just one connection?

Imagine 3 users send requests at the same moment.

 ONE connection                           A POOL of 3 connections

 Request A --+                            Request A --> Connection 1 --+
 Request B --+--> [ Connection 1 ] --> DB Request B --> Connection 2 --+--> DB
 Request C --+    (B and C must wait)     Request C --> Connection 3 --+
                                          (all three run in parallel)

With one connection, only one request runs at a time and the rest queue up. During heavy traffic, the system becomes slow. With a pool, queries run in parallel and the backend serves many users efficiently.

2. COMMIT

COMMIT permanently saves all changes made in the current transaction.

Syntax

START TRANSACTION;
-- your SQL statements
COMMIT;

Example 1: Two updates inside one transaction

SQL

START TRANSACTION;

UPDATE students
SET status = 'ACTIVE'
WHERE student_id = 1;

UPDATE students
SET city = 'Delhi'
WHERE student_id = 1;

COMMIT;

Explanation Both updates are held inside the transaction. The COMMIT makes them permanent together.

State of the row

Example 2: The same updates without a transaction (auto-commit)

SQL

UPDATE students
SET status = 'ACTIVE'
WHERE student_id = 1;

UPDATE students
SET city = 'Delhi'
WHERE student_id = 1;

Explanation Outside a transaction, MySQL commits each statement as soon as it finishes. The first update is already permanent before the second one starts. If something went wrong between them, the first change would stay.

Why it works Inside a transaction, changes are not finalized until you explicitly write COMMIT. That is what lets you treat several statements as one unit.

3. ROLLBACK

ROLLBACK cancels the current transaction and puts the data back to the last committed state.

Syntax

START TRANSACTION;
-- your SQL statements
ROLLBACK;

Example 3: Undo two updates

SQL

START TRANSACTION;

UPDATE students
SET status = 'BANNED'
WHERE student_id = 1;

UPDATE students
SET city = 'Blr'
WHERE student_id = 1;

ROLLBACK;

Explanation When START TRANSACTION runs, MySQL holds all changes temporarily instead of saving them permanently.

 START TRANSACTION
   |
   |-- UPDATE status = 'BANNED'   (held, not permanent)
   |-- UPDATE city   = 'Blr'      (held, not permanent)
   |
   +-- ROLLBACK  --> both changes thrown away

At this point both changes exist only inside the active transaction. They are visible in the same session, but they are not permanently written.

When ROLLBACK runs:

• All changes made after START TRANSACTION are discarded.
• The student's status returns to its original value.
• The student's city returns to its original value.
• The database is exactly as it was before the transaction began.

State of the row

4. SAVEPOINT and ROLLBACK TO SAVEPOINT

A SAVEPOINT creates a checkpoint inside a transaction, so you can undo only part of the work with ROLLBACK TO SAVEPOINT.

Syntax

START TRANSACTION;

-- some statements
SAVEPOINT sp_name;

-- more statements
ROLLBACK TO SAVEPOINT sp_name;

COMMIT;

Example 4: Keep an enrollment, cancel a balance transfer

SQL

START TRANSACTION;

INSERT INTO enrollments (student_id, course_id, status, coupon_code, enrolled_at, refunded_at)
VALUES (4, 3, 'ACTIVE', NULL, '2026-01-10 12:20:00', NULL);

SAVEPOINT sp_before_balance_transfer;

UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

ROLLBACK TO SAVEPOINT sp_before_balance_transfer;

COMMIT;

Step-by-step

 START TRANSACTION
   |
   |-- INSERT enrollment                    [will be kept]
   |
   |-- SAVEPOINT sp_before_balance_transfer <-- the bookmark
   |
   |-- UPDATE balance - 200 (id 1)          [will be undone]
   |-- UPDATE balance + 200 (id 2)          [will be undone]
   |
   |-- ROLLBACK TO SAVEPOINT ...            goes back to the bookmark
   |
   +-- COMMIT --> only the INSERT is saved

Explanation

• After the transaction starts, a new enrollment row is inserted. This change is temporary until committed.
• The savepoint is created right before the balances change. It marks a logical checkpoint.
• Two balance updates run. They are still uncommitted.
• ROLLBACK TO SAVEPOINT undoes both balance updates. The enrollment insert stays. The transaction is still active.
• COMMIT saves whatever remains: the enrollment is stored, and the balance updates are discarded.

State of the data

Why it works A savepoint lets you cancel a risky part of a long transaction without throwing away the good work before it.

5. Internal Working of Transactions (InnoDB)

This section explains what MySQL's InnoDB engine does behind the scenes. The short version: InnoDB changes the real data safely, keeps a record of how to undo it, and keeps a record of how to redo it after a crash.

5.1 Connection plus transaction state

• Every query runs on a database connection.
• In real apps you usually use a connection pool, and a different connection may be picked each time.
• A transaction's unfinished work is tied to the same connection that started it.

What the demonstration showed

student_id = 1 was updated to Raj Vikram inside a transaction on connection 78. Another window, using different connections (79 and 80), still saw Raj.

 Time --->

 Connection 78:  START TRANSACTION;  UPDATE name = 'Raj Vikram';   (no commit yet)
                        |
 Connection 78 reads -> 'Raj Vikram'   (it sees its own change)
 Connection 79 reads -> 'Raj'          (old version)
 Connection 80 reads -> 'Raj'          (old version)
                        |
 Connection 78:  COMMIT;
                        |
 New reads on 79/80 -> 'Raj Vikram'    (open snapshots may still see the old value)

The change was not committed, so it was not visible to others. The new value lived inside that transaction's own context.

Production warning If you run START TRANSACTION on one pooled connection and later run COMMIT on a different one, that commit does nothing useful for your transaction, because the transaction state is not on the other connection. That is why people run the whole transaction as one unit (start, queries, commit or rollback) using the same connection handle.

5.2 When a transaction starts

START TRANSACTION;

InnoDB creates a transaction context. The main pieces are:

• Transaction ID: to track this unit of work.
• Read View: for MVCC (multi-version concurrency control), so other sessions can read a consistent older version of the data.

At this point:

• No table copy is made.
• No row changes are written yet.
• It is only a setup, so InnoDB can later COMMIT or ROLLBACK correctly.

(In the finer details, InnoDB may assign the ID at the first write and build the read view at the first read, but the idea is the same.)

5.3 What happens on UPDATE, INSERT, or DELETE (before COMMIT)

InnoDB does change the real records, but it does so safely.

A. Data lives on disk in pages Rows are stored in pages on disk. Index structures (B-Tree or B+Tree) help find which page holds the row.

B. The page is loaded into RAM (the Buffer Pool) InnoDB loads the needed page into the Buffer Pool and applies your change in memory, not straight to disk. A page that was changed in RAM but not yet written to disk is called a dirty page.

C. Locks are taken for writes The transaction locks the rows it updates. Another transaction that tries to update the same row must wait, and may time out. Reads can still happen, because MVCC can serve an older version.

Until you COMMIT or ROLLBACK, the lock stays.

            Your UPDATE
                 |
                 v
   +-------------------------------------+
   | 1. Take a row lock                  |
   | 2. Save the OLD value in Undo Log   |
   | 3. Change the page in RAM           |   (Buffer Pool: a "dirty page")
   | 4. Record the change in Redo Log    |
   +-------------------------------------+
                 |
        COMMIT or ROLLBACK

5.4 Undo Log

Before changing a row, InnoDB writes an entry in the Undo Log. It helps in two ways.

1. ROLLBACK support When you run ROLLBACK, InnoDB reads the undo log and reverses your changes, restoring the old values.

2. MVCC consistent reads Other connections can still read the older version, because the undo log keeps a link to the previous version of the row, and each reader's read view decides which version to show.

That is why in the demonstration the writer saw Raj Vikram, while the other connections still saw Raj until the commit.

5.5 Redo Log (crash safety)

The Redo Log is not for rollback. It is for durability: committed work must survive a crash.

When pages are changed in memory, InnoDB also writes redo information. If MySQL crashes before the dirty pages reach the disk, the redo log can replay the committed changes during recovery.

 Data page in RAM (Buffer Pool)   <-- the NEW value (dirty page)
 Undo Log                         <-- the OLD value (for ROLLBACK and old-version reads)
 Redo Log (on disk)               <-- a record of the change (for crash recovery)

5.6 What COMMIT does

When you run COMMIT, InnoDB mainly:

1. Marks the transaction as committed (the transaction ID is finalized).
2. Releases the locks.
3. Makes the changes eligible to be seen by other connections. Under MVCC, an already-open consistent read in MySQL's default REPEATABLE READ isolation may keep seeing its older snapshot until that transaction ends.

Because the redo log already recorded the change, a committed change is safe even if the dirty page has not reached the data file yet.

5.7 What ROLLBACK and SAVEPOINT do internally

ROLLBACK Uses the Undo Log to reverse all of the transaction's changes back to the last commit.

SAVEPOINT and ROLLBACK TO SAVEPOINT A savepoint is a bookmark inside the transaction. InnoDB keeps undo entries as you work. Rolling back to a savepoint means:

• Undo only the part after that savepoint.
• Keep everything before that savepoint intact.

 Undo entries:  [1 INSERT] [2 UPDATE] [3 UPDATE]
                          ^
                     SAVEPOINT here

 ROLLBACK TO SAVEPOINT  -->  undo [2] and [3], keep [1]
 ROLLBACK               -->  undo [3], [2], and [1]

6. Timeout

If Transaction A holds a lock for a long time, Transaction B's update on the same row just keeps waiting. The database has a timeout, so after a while the waiting query fails instead of waiting forever.

 Transaction A:  UPDATE row 1 ...   (holds the lock, still running)
 Transaction B:  UPDATE row 1 ...   waiting ... waiting ...
                                    |
                     after the timeout --> ERROR: lock wait timeout exceeded

In InnoDB, the wait time is controlled by innodb_lock_wait_timeout (50 seconds by default). The error code is 1205.

7. Deadlock

A deadlock happens when two transactions each hold a lock the other one needs.

• Transaction 1 locks row A, then tries to lock row B.
• Transaction 2 locks row B, then tries to lock row A.
• Both wait forever.

 Transaction 1                        Transaction 2
 -------------                        -------------
 lock row A   (ok)                    lock row B   (ok)
 want row B --- waits for T2 ------>
            <------ waits for T1 ---- want row A

 Both are stuck  =  DEADLOCK

MySQL detects the deadlock and aborts (rolls back) one of the transactions, so the other one can continue. The rolled-back one gets an error (code 1213) and should be retried.

How to reduce deadlocks

• Always lock rows in the same order in every transaction.
• Keep transactions short.
• Retry a transaction if it was chosen as the victim.

Timeout vs deadlock

Deep Dive

ACID in one minute Transactions are built to give four guarantees:

Isolation levels How much one transaction sees of another's work depends on the isolation level. InnoDB's default is REPEATABLE READ. The levels, MVCC snapshots, and row locking are covered in Isolation Levels and Locking.

Statements that commit by themselves In MySQL, DDL statements such as CREATE, ALTER, DROP, and TRUNCATE cause an implicit commit. They commit the open transaction first, and they cannot be rolled back. Do not put them in the middle of a transaction and expect ROLLBACK to undo them.

Performance Long transactions hurt. They hold locks longer, force InnoDB to keep old row versions around, and increase the chance of timeouts and deadlocks. Commit as soon as the unit of work is finished.

Database behavior Transaction commands are standard SQL, but details such as the default isolation level, savepoint limits, and implicit-commit rules differ between databases. The internals described here (undo log, redo log, buffer pool) are specific to MySQL's InnoDB engine.

Real-world Use Cases

• Money transfers: subtract from one account and add to another, all or nothing.
• Order placement: insert the order, insert the order items, and reduce stock together.
• Course enrollment with payment: enroll the student and charge them, and roll back both if payment fails.
• Bulk data fixes: run a correction inside a transaction, check the result, then COMMIT, or ROLLBACK if the numbers look wrong.
• Multi-step workflows with optional parts: use SAVEPOINT so one failed optional step does not cancel everything.
• Web backends: connection pools let many users be served at the same time.

Review Tips

Interviewers commonly ask:

• "What is a transaction, and what does atomic mean?" — A group of statements that act as one unit: all or nothing.
• "What is the difference between COMMIT and ROLLBACK?" — Commit saves permanently. Rollback cancels everything since the transaction began.
• "What is a SAVEPOINT for?" — To undo only part of a transaction.
• "What is a connection pool, and why use one?" — A set of reusable open connections. It avoids the cost of opening connections and lets requests run in parallel.
• "What happens if you start a transaction on one pooled connection and commit on another?" — The commit does not apply to the transaction.
• "What are the undo log and the redo log?" — Undo reverses changes (rollback and old-version reads). Redo replays committed changes after a crash.
• "What is MVCC?" — Readers see an older consistent version of a row while a writer has an uncommitted change.
• "What is a deadlock, and how does MySQL handle it?" — Two transactions waiting on each other. MySQL rolls one back.
• "Timeout vs deadlock?" — Timeout is a long wait that eventually gives up. Deadlock is a circular wait that can never end by itself.

Common Mistakes to Avoid

Mistake 1: Starting and ending a transaction on different pooled connections

Why it happens: Connection pools hand out connections round-robin.

Result: the COMMIT or ROLLBACK runs on a connection that has no open transaction, and your real transaction stays open.

Correct: Use one connection handle for the whole transaction: start, queries, commit or rollback.

Mistake 2: Forgetting to COMMIT or ROLLBACK

Why it happens: The code path exits early, or an error is thrown.

Result: the transaction stays open and its locks block other users.

Correct: Always finish with COMMIT or ROLLBACK, including in error handling.

Mistake 3: Expecting ROLLBACK to undo a committed change

Why it happens: It sounds like a general "undo".

Correct: ROLLBACK only reverts changes since the last commit. After COMMIT, it is too late.

Mistake 4: Expecting ROLLBACK to undo DDL

Why it happens: DDL looks like any other statement.

Result: ALTER TABLE or TRUNCATE commits the transaction first and cannot be undone.

Correct: Keep DDL out of transactions you want to be able to roll back.

Mistake 5: Thinking ROLLBACK TO SAVEPOINT ends the transaction

Why it happens: The word ROLLBACK is in it.

Correct: The transaction is still open. You must still run COMMIT (or ROLLBACK).

Mistake 6: Expecting other sessions to see uncommitted changes

Why it happens: The data changed in your session, so it feels changed everywhere.

Correct: Other connections see the old version until you commit.

Mistake 7: Locking rows in a different order in different transactions

Why it happens: Two code paths update the same two rows in opposite order.

Result: deadlocks under load.

Correct: Always touch rows in the same order.

Mistake 8: Not retrying after a deadlock or timeout

Why it happens: These errors are treated as rare bugs.

Correct: Catch the error, roll back, and retry the transaction a limited number of times.

Mistake 9: Keeping a transaction open while waiting for something slow

Why it happens: Waiting for user input or an external API inside a transaction.

Result: long-held locks, timeouts, and a bloated undo log.

Correct: Do slow work before START TRANSACTION, and keep the transaction short.

Key Takeaways

• A connection is one live channel between the app and MySQL. It has a connection_id and holds its own session state.
• A connection pool keeps several connections open and reuses them, so requests can run in parallel.
• A transaction groups statements into an atomic unit: all or nothing.
• COMMIT saves everything permanently. ROLLBACK cancels everything since the transaction started.
• Outside a transaction, MySQL auto-commits each statement.
• SAVEPOINT is a bookmark. ROLLBACK TO SAVEPOINT undoes only the work after it, and the transaction stays open.
• A transaction's unfinished work belongs to one connection. Start and end it on the same connection.
• InnoDB changes pages in RAM (dirty pages), takes row locks, and writes undo and redo information.
• The undo log supports rollback and lets other sessions read older versions (MVCC). The redo log makes committed work survive a crash.
• COMMIT marks the transaction committed, releases locks, and makes changes visible to others.
• A timeout is a wait that gives up. A deadlock is a circular wait that MySQL breaks by rolling back one transaction.
• DDL statements commit by themselves and cannot be rolled back.
• Keep transactions short, lock rows in the same order, and retry after deadlocks.