MySQL transactions, affected rows and row locks

What you will learn

• Group related InnoDB writes with an explicit transaction.
• Verify COMMIT, ROLLBACK and constraint-failure behavior.
• Explain row locks, affected-row checks and limits of automatic retries.

Theory: related writes form one unit

A transaction groups database work into a unit. COMMIT makes successful work durable under the configured database/storage guarantees; ROLLBACK cancels uncommitted transactional changes. This example uses InnoDB. A transaction does not automatically include external HTTP calls, files or email.

The practice operation decreases stock and records a reservation. If only one write succeeds, the data can disagree. Use one database transaction, check the conditional stock update affected exactly one row, and abort on any failure. Constraints are the final database boundary, not a replacement for an application contract or authorization.

Transactions are not a promise that every error rolls back everything. A duplicate-key error normally rejects its statement while earlier writes can remain in the open transaction. The caller must deliberately roll back the whole failed operation. Framework transaction helpers can do that when exceptions propagate.

Worked example: undo then commit

Required: MySQL 8.4 with InnoDB and a new isolated practice database. This is not MariaDB compatibility evidence. Create a dedicated practice user/database before the lab. Never run it in a company database or this website’s application database. Laragon/XAMPP are optional; check which database engine they actually provide. Save the complete file as sql_transactions.sql.

CREATE TABLE study_stock (
    id INT PRIMARY KEY,
    available INT NOT NULL CHECK (available >= 0)
) ENGINE=InnoDB;
CREATE TABLE study_reservations (
    id INT PRIMARY KEY,
    item_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    FOREIGN KEY (item_id) REFERENCES study_stock(id)
) ENGINE=InnoDB;
INSERT INTO study_stock VALUES (1, 3);
START TRANSACTION;
UPDATE study_stock SET available = available - 2 WHERE id = 1 AND available >= 2;
SELECT available FROM study_stock WHERE id = 1;
ROLLBACK;
SELECT available FROM study_stock WHERE id = 1;
START TRANSACTION;
UPDATE study_stock SET available = available - 1 WHERE id = 1 AND available >= 1;
INSERT INTO study_reservations VALUES (101, 1, 1);
COMMIT;
SELECT s.id, s.available, COUNT(r.id) AS reservation_count
FROM study_stock AS s
LEFT JOIN study_reservations AS r ON r.item_id = s.id
GROUP BY s.id, s.available;

Run and verify the result

Windows PowerShell, from the practice file's directory:

mysql --user=practice_user --password practice_learning

Ubuntu Bash, from the practice file's directory:

mysql --user=practice_user --password practice_learning

Inside the MySQL client:

SELECT DATABASE();
SOURCE sql_transactions.sql;

Check SELECT DATABASE() shows only your isolated practice database before SOURCE. Supply your own practice username/database and type the password at the prompt. Both shells launch the same MySQL client; SOURCE is a client command, not a server SQL statement. If a table already exists, stop and inspect it; the file does not drop existing tables.

Expected result values, with client layout allowed to vary:

During rolled-back update: available = 1
After ROLLBACK: available = 3
After COMMIT: id = 1, available = 2, reservation_count = 1

Actual MySQL 8.4.3 behavior was checked on Windows in uniquely owned test tables. Ubuntu execution is unverified. Table creation happens before the transaction demonstrations because many DDL statements implicitly commit.

Affected rows and row locks

The worked fixture starts with known stock, so each decrement succeeds. A real handler must inspect the UPDATE row count before inserting a reservation. If zero rows matched, abort and roll back; a conditional UPDATE alone does not stop a later unrelated INSERT. Keep all decisions within the chosen transactional/concurrency boundary.

A locking read such as SELECT ... FOR UPDATE inside an InnoDB transaction can hold the selected row against conflicting writes. A second connection can wait and time out. The test suite verifies a real held-row timeout using a one-second session-only setting; it does not change the server-wide timeout or prove every isolation anomaly. Keep transactions short and acquire locks in a consistent order.

A deadlock and a lock timeout are different failures. Decide which whole operations may be retried. Do not blindly retry external side effects; an email/API request might already have succeeded.

Guided lab

1. Predict all result values before executing the file.
2. In a new manual transaction, decrease stock by one then attempt an existing reservation ID. Observe the duplicate error and inspect the still-open transaction’s stock before ROLLBACK. Do not COMMIT the failed operation.
3. Try a request larger than available stock. Check the UPDATE affects zero rows and do not insert a reservation.
4. In two practice sessions, hold a row with FOR UPDATE and observe a conflicting write wait. Use a short session timeout, not a global change. Roll back the holder and verify unchanged final stock.
5. Record which constraints and connection boundaries were actually tested. Never claim a sequential duplicate test is a full concurrency stress test.

Independent exercise

Build a practice reservation function with a positive integer quantity contract, authorization outside the data function, one transaction, an affected-row check and a unique request ID. Return an existing result for a repeated accepted request without decrementing stock again.

Test success, insufficient stock, duplicate request, failure between writes and two separate clients. Use dummy data. Hint: a unique request constraint and a transaction must support your explicit retry policy; a browser flag is not enough.

Common mistakes and interview

Avoid autocommitting related writes separately, assuming any SQL error rolls back all earlier work, ignoring affected rows, putting DDL in an expected rollback unit, holding locks while calling remote services, and confusing a timeout with a proven deadlock.

Interview: “How do you prevent a partial reservation?” Explain constraints, transaction scope, authorization, guarded updates, failure rollback and concurrent/repeated requests. State which external effects cannot be undone by ROLLBACK.

English practice and glossary

Transaction: a unit of database work. Commit: finish its changes successfully. Rollback: cancel uncommitted transactional changes. Row lock: coordination around conflicting row access. Affected rows: rows matched/changed according to the API’s semantics. Implicit commit: a statement ending a transaction without an explicit COMMIT in your code.

Write: “The first write __. The second write failed because __. I rolled back __ and verified __.” Put actual commands/results in the journal.

Recap and next step

Make related writes one deliberate database unit. Check actual row results and roll back failures. Keep external effects and retry rules explicit. Next inspect query plans and choose indexes for a concrete read pattern rather than adding indexes blindly.