Quiz: SQL Transactions: ACID, COMMIT and ROLLBACK, Isolation Levels and Anomalies

Total: 9 questions

1. 

What is a transaction in SQL, and what do the ACID properties mean?

A transaction is a group of commands that the database executes as a single unit: either all changes are saved with COMMIT, or all are undone with ROLLBACK, and others never see the state in between. In MySQL, transactions are supported by the InnoDB engine.

  • Atomicity — all or nothing; InnoDB keeps previous row versions in the undo log.
  • Consistency — PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL and CHECK constraints are not violated; they are checked on every command.
  • Isolation — concurrent transactions see each other only as far as the isolation level allows; MVCC for a plain SELECT and row locks for writes.
  • Durability — committed data survives a crash; the redo log is written to disk on COMMIT.

The DBMS does not guarantee consistency alone: the rule “whatever is taken from Bob must arrive at Carol” is enforced by the application.

2. 

How do you control a transaction in MySQL: autocommit, START TRANSACTION, COMMIT, ROLLBACK and SAVEPOINT?

By default MySQL works in autocommit mode (autocommit = 1, check it with SELECT @@autocommit;): every command is a separate transaction that is committed immediately.

  • START TRANSACTION or BEGIN opens a transaction; autocommit is suspended until it ends.
  • COMMIT saves the changes and makes them visible to other sessions; ROLLBACK undoes all changes of the transaction.
  • SAVEPOINT name sets a savepoint, ROLLBACK TO SAVEPOINT name undoes the changes after it without closing the transaction, RELEASE SAVEPOINT name removes the savepoint.
  • SET autocommit = 0 — every following command starts a transaction that must be closed with COMMIT or ROLLBACK.

Inside a transaction the session sees its own uncommitted changes; other sessions see the old data until COMMIT. Transactions work only with InnoDB: ROLLBACK does not bring back changes to a MyISAM table.

3. 

When does MySQL commit a transaction without COMMIT, and what happens when a command inside a transaction fails?

An implicit COMMIT is performed by DDL commands (CREATE TABLE, ALTER TABLE, DROP, TRUNCATE), by a second START TRANSACTION (MySQL has no nested transactions) and by SET autocommit = 1 while a transaction is open. After them, ROLLBACK cannot undo the work already done.

An error does not roll back the transaction. MySQL cancels only the failed command; everything before it stays, and the transaction remains open. Example: Carol is credited 100 points, the debit from Bob (30 points) fails with ERROR 3819: Check constraint 'chk_points_non_negative' is violated, and Carol already has 170 inside the transaction. A COMMIT would save points from nowhere, so after an error you run ROLLBACK, and Java code calls rollback() in catch.

MySQL rolls back the whole transaction by itself in one common case, a deadlock (1213); a constraint violation, a syntax error or a lock wait timeout (1205) cancels a single statement.

4. 

What are the concurrency anomalies: lost update, dirty read, non-repeatable read and phantom read?

  • Lost update — two transactions read views = 10, each writes 11, and the second write overwrites the first. In InnoDB it is possible even at REPEATABLE READ; the fix is an atomic UPDATE ... SET views = views + 1 or SELECT ... FOR UPDATE.
  • Dirty read — reading another transaction’s uncommitted changes, which may still be rolled back. Only at READ UNCOMMITTED.
  • Non-repeatable read — reading the same row again returns a different value: between the reads another transaction changed it and committed.
  • Phantom read — repeating a query with the same condition returns a different set of rows: another transaction inserted or deleted a matching row.

Which anomalies can happen is decided by the transaction isolation level.

5. 

What transaction isolation levels exist in SQL, which one is the MySQL default, and how do you change it?

The SQL standard defines four levels, from the weakest to the strictest:

  • READ UNCOMMITTED — every anomaly is possible, including dirty reads;
  • READ COMMITTED — no dirty reads; non-repeatable and phantom reads are possible;
  • REPEATABLE READ — no dirty or non-repeatable reads; phantoms are possible by the standard, but in InnoDB a plain SELECT does not see them;
  • SERIALIZABLE — no anomalies.

MySQL with InnoDB uses REPEATABLE READ by default; PostgreSQL, Oracle and SQL Server use READ COMMITTED. The stricter the level, the more lock waits.

Current level: SELECT @@transaction_isolation; (the old tx_isolation was removed in MySQL 8.0). To change it: SET TRANSACTION ISOLATION LEVEL ... for the next transaction, SET SESSION ... for the session, SET GLOBAL ... for new sessions. Inside an open transaction the command fails with ERROR 1568.

6. 

What are MVCC and consistent reads in InnoDB, and how does REPEATABLE READ differ from READ COMMITTED?

MVCC (multi-version concurrency control) is the mechanism that lets a plain SELECT set no locks and never wait for other transactions: it reads a snapshot (a consistent read), the row versions committed when the snapshot was created. Older versions come from the undo log.

  • READ COMMITTED creates a new snapshot for every SELECT, so fresh changes are visible.
  • REPEATABLE READ creates the snapshot at the first read of the transaction, not at START TRANSACTION, and every later SELECT reads it: changed and new rows of other transactions are not visible.

UPDATE, DELETE and locking reads do not use the snapshot; they work with the latest committed version. So in REPEATABLE READ a SELECT does not see a thread inserted by another session, but an UPDATE of the same transaction changes it (Rows matched: 3), after which the thread appears in the SELECT too: your own changes are visible in the snapshot.

7. 

What is the difference between SELECT ... FOR UPDATE and SELECT ... FOR SHARE, and when do you need them?

Both are locking reads: they lock the rows they read until the end of the transaction and return the latest committed version, not the snapshot.

  • FOR UPDATE — an exclusive (X) lock: other transactions can neither change these rows nor lock them. Use it to read a value in order to change it.
  • FOR SHARE (since MySQL 8.0, formerly LOCK IN SHARE MODE) — a shared (S) lock: everyone can read with FOR SHARE, nobody can change the rows. Use it to make sure a row does not change while you use it.

A plain SELECT is not blocked by either lock. In autocommit mode FOR UPDATE is useless: the lock is released right after the read, so run START TRANSACTION first. For a simple counter a lock is overkill: UPDATE topics SET views = views + 1 does the job in one command. To avoid waiting, use NOWAIT (error 3572 at once) or SKIP LOCKED.

8. 

How does a deadlock (ERROR 1213) differ from a lock wait timeout (ERROR 1205), and how do you make them rare?

A deadlock means two transactions wait for each other: each holds a lock the other needs. InnoDB detects it immediately and rolls back one transaction completely with ERROR 1213 (40001): Deadlock found when trying to get lock; retry it from the start. Details are in SHOW ENGINE INNODB STATUS, section LATEST DETECTED DEADLOCK.

A timeout is an ordinary wait without a cycle that lasts longer than innodb_lock_wait_timeout (50 seconds by default): ERROR 1205 (HY000): Lock wait timeout exceeded. Only the statement is rolled back (unless innodb_rollback_on_timeout is on), so run ROLLBACK, retry and find the long transaction.

  • Short transactions: no HTTP calls and no waiting for the user between START TRANSACTION and COMMIT.
  • Locks in the same order: in a points transfer, update the row with the smaller user_id first.
  • Retry the transaction 2–3 times on SQLState 40001.
9. 

How do you work with transactions in JDBC?

A JDBC connection works in autocommit mode by default. To run several commands in one transaction:

  • conn.setAutoCommit(false) before the commands;
  • conn.commit() after all of them;
  • conn.rollback() in the catch block — otherwise the open transaction stays on the connection and is committed by the next commit() together with unrelated changes;
  • conn.setAutoCommit(true) in finally — this matters when the connection goes back to a pool.

The isolation level is set with conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED), a savepoint with conn.setSavepoint("name") and conn.rollback(savepoint). On a deadlock Connector/J throws SQLTransactionRollbackException with getSQLState() = "40001", which tells the code to retry the transaction. In Spring the same calls are hidden behind @Transactional.

Page 1 of 1