Chapter 32 of 35

Transactions in SQL

Imagine Rahul sends ₹1,000 to Kavya through a banking app. Inside the database this is two separate UPDATE statements: subtract 1,000 from Rahul's balance, then add 1,000 to Kavya's. Now imagine the server crashes, or the second statement fails, right after the first one. Rahul has lost ₹1,000 and Kavya never received it. The money has simply vanished.

Databases solve this problem with transactions. A transaction groups several statements into one unit of work that either completes fully or has no effect at all. In this chapter we will learn how to start, commit and roll back transactions in MySQL, use savepoints, understand autocommit and implicit commits, and see what happens when two users change the same data at the same time: isolation levels, locking reads and deadlocks.

Every example here was run on MySQL 8.4 with the InnoDB storage engine (the default). The examples that show two users at once were run as two real, simultaneous connections.


What Is a Transaction?

A transaction is a sequence of one or more SQL statements that the database treats as a single logical unit: either all of its changes are made permanent (COMMIT) or all of them are undone (ROLLBACK).

Transactions give four guarantees, known as ACID. The DBMS tutorial covers the theory in depth; here is what each one means in practice for a SQL programmer:

Property

Meaning

In the bank transfer

Atomicity

All or nothing

Both balances change, or neither does

Consistency

A transaction moves the database from one valid state to another; constraints are never left violated

No balance goes negative; total money stays the same

Isolation

Concurrent transactions do not see each other's half-finished work (to a configurable degree)

Another user never sees Rahul debited but Kavya not yet credited

Durability

Once committed, changes survive crashes and power failures

After the app says "Success", a server restart will not undo it

One important practical point: in MySQL, transactions work only with transactional storage engines, which in practice means InnoDB. The old MyISAM engine ignores COMMIT and ROLLBACK completely. InnoDB has been the default engine since MySQL 5.5, so every new table is InnoDB unless you ask otherwise.


The Demo Table

The shared company_db has no bank accounts, so for this chapter we create a small table of our own (in a copy of the database). Note the CHECK constraint: a balance can never go below zero.

CREATE TABLE accounts (
  acc_id  INT PRIMARY KEY,
  holder  VARCHAR(50) NOT NULL,
  balance DECIMAL(10,2) NOT NULL,
  CONSTRAINT chk_balance CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Rahul', 5000.00), (2, 'Kavya', 3000.00);

Each section below starts from this state: Rahul 5,000 and Kavya 3,000 (total 8,000).


Autocommit: Every Statement Is a Transaction

You have been using transactions since the first chapter without knowing it. MySQL runs in autocommit mode by default:

SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

With autocommit on, each statement is automatically wrapped in its own tiny transaction and committed the moment it succeeds. That is why a ROLLBACK after a normal UPDATE does nothing:

UPDATE accounts SET balance = balance - 100 WHERE acc_id = 1;
ROLLBACK;
SELECT * FROM accounts;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      1 | Rahul  | 4900.00 |
|      2 | Kavya  | 3000.00 |
+--------+--------+---------+
2 rows in set (0.00 sec)

The UPDATE was already committed, so there was nothing to roll back. Rahul's 100 is gone. To group statements, we must start an explicit transaction.


START TRANSACTION, COMMIT and ROLLBACK

START TRANSACTION;      -- or: BEGIN;
  statement 1;
  statement 2;
  ...
COMMIT;                 -- make all changes permanent
-- or
ROLLBACK;               -- undo all changes since START TRANSACTION

START TRANSACTION suspends autocommit until the transaction ends with COMMIT or ROLLBACK. BEGIN and BEGIN WORK are synonyms (but inside stored procedures BEGIN means "start a block", so START TRANSACTION is the clearer habit).

A Committed Transfer

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE acc_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE acc_id = 2;
SELECT * FROM accounts;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      1 | Rahul  | 4000.00 |
|      2 | Kavya  | 4000.00 |
+--------+--------+---------+
2 rows in set (0.00 sec)
COMMIT;

Inside the transaction, our own session already sees the new balances. Other sessions keep seeing 5,000 and 3,000 until we COMMIT. After COMMIT the change is permanent and visible to everyone.

A Rolled-Back Mistake

Now suppose someone runs a DELETE and forgets the WHERE clause, but inside a transaction:

START TRANSACTION;
DELETE FROM accounts;          -- oops, no WHERE
SELECT COUNT(*) FROM accounts;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
ROLLBACK;
SELECT * FROM accounts;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      1 | Rahul  | 4000.00 |
|      2 | Kavya  | 4000.00 |
+--------+--------+---------+
2 rows in set (0.00 sec)

Everything is back. This is why many experienced developers start a transaction before running risky UPDATE or DELETE statements by hand on a real database: check the affected row count, look at the data, and only then COMMIT.


A Failed Statement Does Not Roll Back the Transaction

This is the most misunderstood point about transactions in MySQL, so read it carefully. Let us try to transfer ₹6,000 from Rahul (who has only 5,000) to Kavya. We credit Kavya first, then debit Rahul:

START TRANSACTION;
UPDATE accounts SET balance = balance + 6000 WHERE acc_id = 2;
UPDATE accounts SET balance = balance - 6000 WHERE acc_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ERROR 3819 (HY000): Check constraint 'chk_balance' is violated.

The debit failed because the balance would become negative. Many beginners assume the whole transaction has now been cancelled. It has not:

SELECT * FROM accounts;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      1 | Rahul  | 5000.00 |
|      2 | Kavya  | 9000.00 |
+--------+--------+---------+
2 rows in set (0.00 sec)

MySQL undid only the failed statement (Rahul still has 5,000). The successful credit to Kavya is still there, inside a transaction that is still open. If the application now ran COMMIT, ₹6,000 would be created out of thin air (the total would become 14,000 instead of 8,000). The application must notice the error and roll back:

ROLLBACK;
SELECT * FROM accounts;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      1 | Rahul  | 5000.00 |
|      2 | Kavya  | 3000.00 |
+--------+--------+---------+
2 rows in set (0.00 sec)

Rule: after any error inside a transaction, your code must decide what to do, and usually that means ROLLBACK. Application code does this in a try/catch block; inside the database, the Stored Procedures & Functions chapter shows how an error handler can do it.

A typical application pattern (Python, for illustration):

try:
    cur.execute("START TRANSACTION")
    cur.execute("UPDATE accounts SET balance = balance - %s WHERE acc_id = %s", (amt, src))
    cur.execute("UPDATE accounts SET balance = balance + %s WHERE acc_id = %s", (amt, dst))
    conn.commit()
except Exception:
    conn.rollback()
    raise

The important exception is a deadlock: then InnoDB rolls back the whole transaction itself (we will see this below). A lock wait timeout, by default, behaves like the CHECK failure above: only the statement is undone.


Savepoints: Partial Rollback

A savepoint is a named marker inside a transaction. You can roll back to it, undoing only the work done after it, while keeping the earlier work and keeping the transaction open.

SAVEPOINT name;                 -- set a marker
ROLLBACK TO [SAVEPOINT] name;   -- undo everything after the marker
RELEASE SAVEPOINT name;         -- forget the marker (keeps the changes)

Let us trace an example:

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE acc_id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 500 WHERE acc_id = 2;
SAVEPOINT after_credit;
UPDATE accounts SET balance = 0 WHERE acc_id = 2;      -- a mistake
SELECT * FROM accounts;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      1 | Rahul  | 4500.00 |
|      2 | Kavya  |    0.00 |
+--------+--------+---------+
2 rows in set (0.00 sec)
ROLLBACK TO SAVEPOINT after_credit;
SELECT * FROM accounts;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      1 | Rahul  | 4500.00 |
|      2 | Kavya  | 3500.00 |
+--------+--------+---------+
2 rows in set (0.00 sec)

Only the mistake was undone. Rolling back further, to the first savepoint, also undoes the credit:

ROLLBACK TO after_debit;
SELECT * FROM accounts;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      1 | Rahul  | 4500.00 |
|      2 | Kavya  | 3000.00 |
+--------+--------+---------+
2 rows in set (0.00 sec)

The transaction is still open; a COMMIT now would save only the debit (which, for a real transfer, would be wrong, so here we would ROLLBACK instead). The trace in one picture:

START ── debit 500 ──●after_debit── credit 500 ──●after_credit── set Kavya = 0
                     ^                           ^
                     |                           └─ ROLLBACK TO after_credit removes "set Kavya = 0"
                     └─ ROLLBACK TO after_debit removes "credit 500" as well

Points to remember:

  • Rolling back to a savepoint deletes all savepoints set after it.

  • COMMIT or a full ROLLBACK ends the transaction and deletes all its savepoints. Using one afterwards gives ERROR 1305 (42000): SAVEPOINT after_debit does not exist.

  • Savepoints are useful for long batch jobs ("if this one item fails, skip it but keep the rest") and inside stored procedures.


Implicit Commits: Statements You Cannot Roll Back

Some statements automatically commit the current transaction before (and after) they run. The most important group is DDL: CREATE, ALTER, DROP, RENAME and TRUNCATE TABLE. Watch:

START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE acc_id = 1;   -- Rahul: 5000 -> 5100
CREATE TABLE audit_note (msg VARCHAR(50));                       -- implicit COMMIT!
ROLLBACK;
SELECT * FROM accounts;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      1 | Rahul  | 5100.00 |
|      2 | Kavya  | 3000.00 |
+--------+--------+---------+
2 rows in set (0.00 sec)

The ROLLBACK came too late: CREATE TABLE had already committed the UPDATE. Other statements that cause an implicit commit include START TRANSACTION/BEGIN itself (starting a new transaction commits the open one), LOCK TABLES, account-management statements such as CREATE USER and GRANT, and SET autocommit = 1 when it was 0.

This is why TRUNCATE TABLE cannot be rolled back while DELETE can (the ALTER, DROP & TRUNCATE chapter mentioned this). Never mix schema changes into a transaction that you might need to undo.


Turning Autocommit Off

Instead of START TRANSACTION, you can switch autocommit off for your session. Then a transaction is always open, and nothing is permanent until you COMMIT:

SET autocommit = 0;
UPDATE accounts SET balance = 4900 WHERE acc_id = 1;
ROLLBACK;
SELECT * FROM accounts;      -- Rahul still has 5000
SET autocommit = 1;

This works, but it is risky: if you forget to COMMIT and close the connection, your work is rolled back; and a forgotten open transaction can hold locks and block other users. Explicit START TRANSACTION ... COMMIT blocks are clearer and are what most applications use.


Concurrency: When Two Users Change the Same Data

So far only one user was connected. Real databases serve hundreds of connections at once, and that creates new problems. The classic ones are:

Problem

What happens

Dirty read

Reading another transaction's changes before it commits; if it then rolls back, you read data that never existed

Non-repeatable read

Reading the same row twice in one transaction and getting different values, because someone committed a change in between

Phantom read

Running the same range query twice and getting new or missing rows, because someone inserted or deleted rows in between

Lost update

Two transactions read a value, both compute a new value from it, and the later write silently overwrites the earlier one

The SQL standard defines four isolation levels that say which of the first three problems are allowed:

Isolation level

Dirty read

Non-repeatable read

Phantom read

READ UNCOMMITTED

Possible

Possible

Possible

READ COMMITTED

Prevented

Possible

Possible

REPEATABLE READ

Prevented

Prevented

Possible (by the standard)

SERIALIZABLE

Prevented

Prevented

Prevented

Higher isolation is safer but allows less concurrency. MySQL's InnoDB uses REPEATABLE READ by default (PostgreSQL, Oracle and SQL Server default to READ COMMITTED). InnoDB's REPEATABLE READ is stronger than the standard requires: plain SELECTs read from a consistent snapshot, so they do not see phantom rows either, and locking reads use "next-key" locks that block inserts into the scanned range.

You can check and change the level:

SELECT @@transaction_isolation;                               -- REPEATABLE-READ
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;       -- for this connection
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;                 -- for the next transaction only

In the demos below, Session A and Session B are two separate connections running at the same time. Time flows downwards.

Demo 1: A Dirty Read Under READ UNCOMMITTED

Time  Session A                                    Session B
----  -------------------------------------------  ---------------------------------------------
t1    START TRANSACTION;
      UPDATE accounts SET balance = balance - 1000
      WHERE acc_id = 1;           -- not committed
t2                                                 SET SESSION TRANSACTION ISOLATION LEVEL
                                                     READ UNCOMMITTED;
                                                   SELECT balance FROM accounts WHERE acc_id = 1;
                                                   --> 4000.00   (dirty!)
                                                   SET SESSION TRANSACTION ISOLATION LEVEL
                                                     READ COMMITTED;
                                                   SELECT balance FROM accounts WHERE acc_id = 1;
                                                   --> 5000.00
t3    ROLLBACK;

Under READ UNCOMMITTED, Session B saw 4,000, a value that was rolled back a moment later and never officially existed. Under READ COMMITTED it saw the last committed value, 5,000. READ UNCOMMITTED is almost never used in practice.

Demo 2: Repeatable Read vs Read Committed

Session B reads Rahul's balance twice inside one transaction; between the two reads, Session A commits a change to 4,500. We ran it once at each level:

Time  Session A                          Session B
----  ---------------------------------  ------------------------------------------------
t1                                       START TRANSACTION;
                                         SELECT balance ... WHERE acc_id = 1;  --> 5000.00
t2    UPDATE accounts SET balance = 4500
      WHERE acc_id = 1;   (autocommit)
t3                                       SELECT balance ... WHERE acc_id = 1;
                                           REPEATABLE READ: --> 5000.00
                                           READ COMMITTED:  --> 4500.00
t4                                       COMMIT;
                                         SELECT balance ... WHERE acc_id = 1;  --> 4500.00

Under REPEATABLE READ, InnoDB takes a snapshot at the transaction's first read, and every later plain SELECT in that transaction reads from the same snapshot. That is great for reports: all numbers come from one consistent moment. Under READ COMMITTED each SELECT sees the latest committed data, so the same query returned two different answers (a non-repeatable read). After COMMIT, a new snapshot is taken and both levels see 4,500.

Neither level blocked anyone here. InnoDB keeps old versions of rows (this is called MVCC, multi-version concurrency control), so readers do not wait for writers and writers do not wait for readers.

Demo 3: The Lost Update

Snapshots do not protect "read, then write" logic. Two transactions each read Rahul's balance, compute a new balance in application code, and write it back:

Time  Session A (withdraw 1000)              Session B (deposit 2000)
----  -------------------------------------  -------------------------------------
t1    START TRANSACTION;
      SELECT balance ... --> 5000.00
t2                                           START TRANSACTION;
                                             SELECT balance ... --> 5000.00
                                             UPDATE accounts SET balance = 5000 + 2000
                                             WHERE acc_id = 1;
                                             COMMIT;
t3    UPDATE accounts SET balance = 5000 - 1000
      WHERE acc_id = 1;
      COMMIT;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      1 | Rahul  | 4000.00 |
|      2 | Kavya  | 3000.00 |
+--------+--------+---------+

The correct answer is 5000 − 1000 + 2000 = 6,000, but the result is 4,000. Session B's deposit was lost, because Session A computed its new value from a stale read. There are two simple fixes:

  1. Let the database do the arithmetic: UPDATE accounts SET balance = balance - 1000 WHERE acc_id = 1. An UPDATE always works on the latest committed row and locks it, so concurrent relative updates cannot overwrite each other.

  2. Lock the row when you read it, with SELECT ... FOR UPDATE, when the logic genuinely needs to read first (for example "only withdraw if the balance is enough").

Demo 4: Locking Reads with SELECT ... FOR UPDATE

Time  Session A                                 Session B
----  ----------------------------------------  ----------------------------------------------
t1    START TRANSACTION;
      SELECT balance FROM accounts
      WHERE acc_id = 1 FOR UPDATE; --> 5000.00
      (row 1 is now locked)
t2                                              START TRANSACTION;
                                                SELECT balance FROM accounts
                                                WHERE acc_id = 1 FOR UPDATE;
                                                ... waits ...
t3    UPDATE accounts SET balance = balance - 1000
      WHERE acc_id = 1;
      COMMIT;            (lock released)
                                                --> 4000.00   1 row in set (2.00 sec)
                                                UPDATE accounts SET balance = balance + 2000
                                                WHERE acc_id = 1;
                                                COMMIT;

Final balance: 6,000, which is correct. Two details in Session B's output are worth noticing. The (2.00 sec) is the time it spent waiting for A's lock. And it read 4,000, not 5,000: a locking read always reads the latest committed version, not the transaction's snapshot. That is exactly what you want before updating.

The locking read variants in MySQL 8:

Clause

Effect

FOR UPDATE

Exclusive lock on the rows read: others cannot update, delete or lock them until you finish

FOR SHARE (old form: LOCK IN SHARE MODE)

Shared lock: others may also read-lock the rows, but nobody can modify them

... NOWAIT

Fail immediately instead of waiting if a row is locked

... SKIP LOCKED

Silently skip locked rows (useful for job queues where many workers pick tasks)

With Session A still holding the lock on Rahul's row, Session B tried both options:

SELECT * FROM accounts WHERE acc_id = 1 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
SELECT * FROM accounts FOR UPDATE SKIP LOCKED;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      2 | Kavya  | 3000.00 |
+--------+--------+---------+
1 row in set (0.00 sec)

Locks are held until the transaction ends. Under autocommit, a FOR UPDATE releases its lock immediately after the statement, so locking reads only make sense inside an explicit transaction.

Waiting Too Long: Lock Wait Timeout

A transaction does not wait forever for a lock. After innodb_lock_wait_timeout seconds (default 50) it gives up. With the timeout lowered to 2 seconds for the demo, while Session A held Rahul's row:

SET SESSION innodb_lock_wait_timeout = 2;
UPDATE accounts SET balance = balance + 1 WHERE acc_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

By default only the timed-out statement is rolled back, not the whole transaction, so the application must decide whether to retry or roll back.

Demo 5: Deadlock

A deadlock happens when two transactions each hold a lock the other one needs. Here A transfers Rahul → Kavya while B transfers Kavya → Rahul, and they lock the rows in opposite order:

Time  Session A                               Session B
----  --------------------------------------  ---------------------------------------
t1    START TRANSACTION;
      UPDATE ... - 100 WHERE acc_id = 1;
      (A locks row 1)
t2                                            START TRANSACTION;
                                              UPDATE ... - 200 WHERE acc_id = 2;
                                              (B locks row 2)
                                              UPDATE ... + 200 WHERE acc_id = 1;
                                              ... waits for A ...
t3    UPDATE ... + 100 WHERE acc_id = 2;
      ... would wait for B: a cycle!
      ERROR 1213 (40001): Deadlock found when
      trying to get lock; try restarting
      transaction
                                              Query OK, 1 row affected (1.00 sec)
                                              COMMIT;
+--------+--------+---------+
| acc_id | holder | balance |
+--------+--------+---------+
|      1 | Rahul  | 5200.00 |
|      2 | Kavya  | 2800.00 |
+--------+--------+---------+

InnoDB detects the cycle instantly, picks one transaction as the victim (here A), and rolls back that whole transaction, releasing its locks so the other can finish. The final balances show only B's transfer; A's debit of 100 was undone too. A then ran COMMIT, but there was nothing left to commit.

Deadlocks are normal in busy systems and are not a bug in MySQL. Applications should:

  • Retry a transaction that fails with error 1213 (the message literally says so).

  • Lock rows in a consistent order, for example always the lower acc_id first. If both transfers above had touched row 1 before row 2, B would simply have waited and no cycle could form.

  • Keep transactions short, so locks are held for as little time as possible.


Best Practices

  • Keep transactions short. Never wait for user input (or a slow API call) while a transaction is open; you are holding locks the whole time.

  • Always end a transaction with COMMIT or ROLLBACK, including on every error path.

  • Do arithmetic in SQL (balance = balance - 1000) rather than read-modify-write in application code, or use SELECT ... FOR UPDATE.

  • Validate with constraints. The CHECK constraint stopped the overdraft above; let the database enforce rules, then roll back on error.

  • Retry on deadlock (1213), and access rows/tables in a consistent order.

  • Do not put DDL inside transactions you may need to roll back.

  • Stick with the default REPEATABLE READ unless you have a clear reason; READ COMMITTED is a common alternative for high-write workloads.


Common Mistakes

  • Thinking ROLLBACK works without START TRANSACTION. Under autocommit, every statement is already committed.

  • Assuming an error automatically cancels the transaction. In MySQL only the failing statement is undone (except for deadlocks); you must ROLLBACK yourself.

  • Running CREATE/ALTER/DROP/TRUNCATE inside a transaction and expecting ROLLBACK to undo earlier changes.

  • Using MyISAM tables and wondering why ROLLBACK did nothing.

  • Using SELECT ... FOR UPDATE outside a transaction, where its lock is released immediately.

  • Leaving a transaction open in a SQL client and going for lunch, blocking everyone who needs those rows.


Interview Questions

What is a transaction?

A group of SQL statements executed as a single unit: either all changes are committed or all are rolled back. It guarantees the ACID properties.

What is the difference between COMMIT and ROLLBACK?

COMMIT makes all changes of the current transaction permanent and visible to others. ROLLBACK undoes all changes made since the transaction started (or since a savepoint, with ROLLBACK TO).

What is autocommit?

A mode (on by default in MySQL) where every statement runs as its own transaction and is committed automatically when it succeeds. START TRANSACTION suspends it until COMMIT or ROLLBACK.

What is a savepoint?

A named point inside a transaction. ROLLBACK TO SAVEPOINT undoes only the changes made after it, leaving earlier changes and the transaction intact.

Can you roll back a TRUNCATE or DROP in MySQL?

No. DDL statements cause an implicit commit, so they cannot be rolled back and they also commit any open transaction. DELETE can be rolled back.

If one statement in a MySQL transaction fails, is the transaction rolled back?

No, only that statement is rolled back; earlier statements remain and the transaction stays open. The application must issue ROLLBACK. The exception is a deadlock, where InnoDB rolls back the whole victim transaction.

Name the four isolation levels and MySQL's default.

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. InnoDB's default is REPEATABLE READ.

Explain dirty read, non-repeatable read and phantom read.

Dirty read: seeing uncommitted changes. Non-repeatable read: the same row read twice gives different values because of a committed update in between. Phantom read: the same range query returns new or missing rows because of a committed insert or delete in between.

What is a lost update and how do you prevent it?

Two transactions read the same value and write back results computed from it, so one write overwrites the other. Prevent it with relative updates (SET x = x - 1), SELECT ... FOR UPDATE, or optimistic locking with a version column.

What is a deadlock and how does MySQL handle it?

Two transactions wait for locks held by each other. InnoDB detects the cycle, rolls back one transaction (error 1213) and lets the other proceed. Applications should retry and lock rows in a consistent order.

What is the difference between FOR UPDATE and FOR SHARE?

FOR UPDATE takes exclusive row locks (no one else can modify or lock the rows); FOR SHARE takes shared locks (others can also read-lock but not modify). Both last until the transaction ends.


Summary

  • A transaction is all-or-nothing: START TRANSACTION ... COMMIT or ROLLBACK. Only InnoDB tables support it.

  • With autocommit (the default), each statement commits on its own.

  • A failing statement undoes only itself; your code must ROLLBACK the transaction.

  • Savepoints allow partial rollback inside a transaction.

  • DDL and some other statements commit implicitly and cannot be rolled back.

  • Isolation levels control what concurrent transactions see; InnoDB defaults to REPEATABLE READ with consistent snapshots (MVCC).

  • Use relative updates or SELECT ... FOR UPDATE to avoid lost updates; NOWAIT and SKIP LOCKED control waiting.

  • Deadlocks roll back one victim transaction (error 1213); retry it and lock rows in a consistent order.

Next, in Stored Procedures & Functions, we will store SQL logic, including a complete transfer with error handling, inside the database itself.