Transaction Statements

## The transaction is the unit, not the statement A transaction is a group of statements the database treats as one indivisible unit of work: either every effect it produced becomes part of the database, or none of them does. The three statements that define where that unit starts and stops are `START TRANSACTION` (or `BEGIN`), `COMMIT` and `ROLLBACK`. Everything else about transactions — what other sessions can see while yours runs, how the engine undoes work — sits underneath these three; the language surface is deliberately tiny. ## Autocommit: what happens if you write none of them If you open a client, type an `UPDATE` and press enter, it takes effect. There was still a transaction — you just did not write it. In **autocommit** mode the session implicitly wraps each statement in its own transaction and commits it the moment the statement succeeds. Two statements typed in a row are therefore two separate transactions: ```sql UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- committed UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- separate transaction ``` If the second statement fails, or the process dies between the two, the first is already permanent and the money has vanished. There is no `ROLLBACK` you can issue afterwards, because there is nothing still open to roll back. Autocommit is a client/session setting, not part of the SQL standard's core model — the standard says a transaction begins implicitly at the first statement and stays open until you end it explicitly. In practice the majors and their drivers ship autocommit **on**: MySQL's `autocommit` defaults to 1, JDBC connections default to `autoCommit = true`, and most interactive clients commit per statement. Oracle's classic clients are the well-known exception: a transaction starts at your first DML and waits for you. So never assume; know what your client does. ## Opening a transaction explicitly ```sql START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; ``` Now the two updates are one unit. `START TRANSACTION` is the portable spelling. `BEGIN` is accepted as a synonym by several engines (and SQL Server spells it `BEGIN TRANSACTION`), but in standard SQL/PSM `BEGIN` opens a compound statement block, which is why `START TRANSACTION` is the safer thing to write in portable scripts. ## COMMIT `COMMIT` (optionally `COMMIT WORK`) ends the transaction and makes its effects permanent: the changes become visible to other sessions and the resources the transaction held are released. The next statement you run starts a new transaction. Two things candidates forget: `COMMIT` is itself a statement that can **fail** — deferred constraint checks and serialization failures surface there, and a failed `COMMIT` leaves you rolled back — and once it succeeds there is no `ROLLBACK` that takes it back; the only way to reverse committed work is a new transaction that writes compensating changes. ## ROLLBACK `ROLLBACK` (or `ROLLBACK WORK`) ends the transaction and discards everything it did since it began. It is the normal response to an application-level error mid-way through a unit of work. What it does **not** undo is anything outside the database's control: rows already sent to the client, a message you published to a broker, an email you sent. It also does not give back identity or sequence values the transaction consumed — those are deliberately non-transactional so concurrent sessions never wait on each other for a number, which is why gaps in generated keys are normal. ## Endings you did not write A transaction can also end without any of your statements. If the connection drops or the client exits with a transaction still open, the engine rolls it back — an unfinished transaction is never silently committed. If the server crashes, the same is true after recovery. And on some engines a DDL statement inside your transaction forces an implicit `COMMIT` of everything before it, which is the single most surprising way an explicit transaction ends early. ## What interviewers listen for They want to hear that you know a bare statement is still a transaction, that you can name the three boundary statements and what each leaves behind, and that you reach for an explicit `START TRANSACTION` the moment a piece of work spans more than one statement. Saying "I always wrap related writes in a transaction, and I decide explicitly whether the error path commits or rolls back" is the whole answer at this level.