BEGIN, COMMIT and Autocommit

Why this stage exists

Until now a change reached the database file at two moments: when the pool evicted the page, and when the session ended. Neither has anything to do with what the user meant. Moving money between two rows is two UPDATEs. If the program stops between them, by a crash, a power cut or someone closing the terminal, the file may hold the first and not the second, and the money is gone. Worse, an eviction in the middle of a statement can write half of what it did.

A transaction is the unit the user means: a group of statements that reaches the file whole or not at all. This stage gives droid two promises to keep:

• When a commit prints its line, its changes are in the file. A crash one instruction later loses nothing.
• Until then, none of them are. The file only ever holds work that was committed.

You will also get a way to break the program on purpose. .crash stops it dead, with no goodbye and no save, which is what a power cut does. Every test in this part uses it, and so should you.

What changes: the transaction's life

A session is always in one of two states. A statement that changes data with no transaction open does not wait for a BEGIN: it gets a transaction of its own, which commits the moment the statement succeeds. That is autocommit, and it is what every statement you ran in the earlier parts was doing without knowing it.

A statement that fails inside a transaction fails alone: a duplicate key changes nothing, the transaction stays open with everything before it, and a COMMIT after it commits the rest. The one error that ends the transaction is 01303, which comes next.

What changes: which pages may reach the file

The transaction keeps a set of the pages it has changed. A commit writes exactly that set to the file, and the rule for the rest of the time is that nothing in the set reaches the file before the commit:

Database people call these two choices no-steal (the pool may not steal a frame from an uncommitted page by writing it out) and force (a commit forces its pages to disk before it returns). Together they make recovery trivial: the file holds every commit and nothing else, so there is nothing to undo and nothing to redo.

They also have a price, and you pay it here. If no page in the set may leave, a transaction can only change as many pages as the pool has frames. When a page must come in and the only pages left to evict are pinned or in the set, the statement is 01303: the transaction is too large. It cannot carry on, since the next page has nowhere to go, so the whole transaction is thrown away and the session is IDLE again. Throwing it away is easy precisely because of no-steal: the file still has every page as it was before the BEGIN, so forgetting the in-memory copies is enough.

No real engine accepts that limit. PostgreSQL and InnoDB let the pool write uncommitted pages (steal) and keep a log that can undo them; SQLite writes a rollback journal of the old pages first. The log comes later in this part, and it is how commits stop paying the price of force.

Output contract for this stage

The full definition is on the output contract page, sections 32 and 33.

pages=<k> counts each page once, however often it changed: an INSERT into a leaf with room adds 1, and a second INSERT into the same leaf adds nothing. The commands that change pages by hand (pager write, pager alloc, page insert and the rest) are outside transactions and work as before.

What you should see when done

A committed row, a transaction that is never committed, and a crash:

$droid --db bank.db
Welcome to droid!
>create table accounts (id int, owner varchar(20), balance int);
Create table statement executed successfully
>insert into accounts (id, owner, balance) values (1, 'ana', 100);
Insert statement executed successfully (1 rows)
>tx status
[TX] Status: IDLE
>begin;
Begin statement executed successfully
>insert into accounts (id, owner, balance) values (2, 'bo', 50);
Insert statement executed successfully (1 rows)
>update accounts set balance = 0 where id = 1;
Update statement executed successfully (1 rows)
>tx status
[TX] Status: IN_TRANSACTION pages=1
>select * from accounts;
id | owner | balance
1 | ana | 0
2 | bo | 50
(2 rows)
[EXEC] rows_examined=2
>begin;
[ERROR:01301] A transaction is already open
>.crash
$droid --db bank.db -c "select * from accounts;"
id | owner | balance
1 | ana | 100
(1 rows)
[EXEC] rows_examined=1

Inside the transaction, the SELECT saw its own changes. After the crash they were gone, and the row committed before the BEGIN was there, untouched. Run it again with commit; before .crash, and both rows come back.

To see 01303, start with --cache-pages 16, make a table with wide rows, and insert a few dozen of them inside one BEGIN. Somewhere along the way an INSERT fails and tx status says IDLE: none of the rows inserted before the error are in the table, and every INSERT after it ran on its own and committed.

Common bugs in this stage

• Printing the line before the write. The commit's line is the promise. Write the pages, make the file durable, then print.
• Trusting write to reach the disk. A write goes to the operating system's cache first. Whatever your language calls fsync, a commit needs it. The tests cannot see the difference, a power cut can.
• Syncing once per page, or per write. A sync costs milliseconds, and from here every autocommit statement is a commit. One sync per commit is the rule; more makes a session of a hundred INSERTs slow, and the tests give each session 3 seconds.
• Evicting a dirty page because it is unpinned. With 1000 frames no test notices. With 16, the uncommitted leaf goes to the file and a crash leaves it there.
• Forgetting the header and new pages. A transaction that allocates a page or frees one changes page 0 too. They are in the set like any other page, and a page allocated past the end of the file must vanish when the transaction is thrown away.
• Leaving the session IN_TRANSACTION after 01303. The transaction is gone. The next statement runs on its own, in autocommit.
• Writing every dirty page at COMMIT. A page pager write changed is not part of the transaction. Commit writes the transaction's set and nothing else.
• Saving the open transaction at .exit. The end of the session saves what was committed and the debug commands' pages. An open transaction is thrown away first.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: fflush empties your own buffer into the operating system; fsync(fileno(f)) asks the operating system to put it on the disk. .crash is _exit(3), which skips atexit handlers and stdio buffers, exactly like a crash. Plain exit would flush them.
• C++: std::ofstream::flush stops at the operating system too; you need the file descriptor and fsync. std::_Exit(3) is the crash; std::exit runs destructors that may save pages.
• Rust: File::sync_all (or sync_data) is the fsync. std::process::exit(3) does not run destructors, which is what .crash wants; a BufWriter in front of the file must be flushed before the sync, or the sync has nothing to sync.
• Zig: file.sync() is the fsync. std.process.exit(3) skips every defer, so nothing is saved behind your back.