Many Sessions, One Process

Why this stage exists

Every transaction so far had the database to itself. Real databases serve many connections at once, and everything interesting about concurrency (two transactions touching the same row, one waiting for another, one reading while another writes) needs at least two transactions open at the same time.

The usual answer is a server with a thread per connection. That makes the interesting cases impossible to test: whether session 2's UPDATE runs before or after session 1's COMMIT depends on the operating system's scheduler, and a test that passes a thousand times can fail on the next run.

So this part keeps one thread and makes the input the scheduler. A line that starts with @2 runs on behalf of session 2:

$droid --db bank.db
Welcome to droid!
>create table a (id int primary key, v varchar(20));
Create table statement executed successfully
>@1 begin;
@1| Begin statement executed successfully
>@2 begin;
@2| Begin statement executed successfully
>@1 insert into a (id, v) values (1, 'ann');
@1| Insert statement executed successfully (1 rows)
>@1 commit;
@1| Commit statement executed successfully
>@2 tx status
@2| [TX] Status: IN_TRANSACTION pages=0
>.exit
exiting.. good bye!

Session 1 committed while session 2's transaction stayed open. The program finished each line before reading the next, so the order of the lines is exactly the order things happened. Every anomaly in the rest of this part is reproduced this way, by writing the interleaving down.

What belongs to a session

A session is what a connection would be: the state that is about who is asking. In this stage that is just its transaction: whether one is open, its id, and the pages it changed. Later stages add the locks it holds, the statement it is waiting with, its isolation level and its view of the data.

Everything else is shared, because there is one database: the file, the buffer pool, the catalog, the log, the output mode.

Session 1 always exists, and a line with no prefix belongs to it, printed without a tag. That is what keeps every earlier test passing: an input with no @ anywhere is one session, exactly as before.

Transaction ids

From now on every transaction has a number, given when it starts: at BEGIN, or, for a statement that may change rows or tables and runs outside a transaction, when it starts to run. The counter starts at 1 each time the program starts. sessions shows each session's current id:

>create table a (id int primary key, v varchar(20));
Create table statement executed successfully
>create table b (id int primary key, v varchar(20));
Create table statement executed successfully
>@1 begin;
@1| Begin statement executed successfully
>@2 begin;
@2| Begin statement executed successfully
>sessions
[SESSIONS] 1 trx=3 state=ACTIVE waits_for=0 iso=REPEATABLE_READ
[SESSIONS] 2 trx=4 state=ACTIVE waits_for=0 iso=REPEATABLE_READ

The two CREATE TABLEs were transactions 1 and 2. The ids say which transaction is older, which matters soon: a deadlock picks the youngest as its victim, and a read view sees what older transactions committed. waits_for and iso stay at those values until the stages that give them meaning.

These are not the transaction ids of the write-ahead log, which are given at commit. The two never meet.

Two transactions, one page

ROLLBACK still works by putting back page copies taken at BEGIN. With one transaction that was right. With two it is wrong the moment both change the same page: rolling back the first would put back a copy taken before the second's change, and erase it.

So in this stage the sessions that hold transactions open keep to pages of their own: different tables, no splits, no tables created while another transaction is open. The next stage replaces page copies with something that works, and its first test is exactly the case page copies get wrong.

For the same reason, no test here reads rows that another session has changed and not committed. Locks and read views decide what such a read sees.

What needs every transaction closed

CHECKPOINT writes every dirty page and empties the log; VACUUM moves pages around the file. An open transaction's pages must not reach the file before it commits, so both already refused to run inside a transaction (01304). Now they also refuse while any other session has one open: 01802.

At the end of the program, every open transaction is rolled back, lowest session number first, without printing anything, and then the program saves as before.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, sections 80 and 81.

What you should see when done

$droid --db s.db
Welcome to droid!
>create table a (id int primary key, v varchar(20));
Create table statement executed successfully
>@2 begin;
@2| Begin statement executed successfully
>@2 insert into a (id, v) values (1, 'ann');
@2| Insert statement executed successfully (1 rows)
>@2 select * from nowhere;
@2| [ERROR:00401] table nowhere does not exist
>checkpoint;
[ERROR:01802] session 2 has a transaction open
>@2 commit;
@2| Commit statement executed successfully
>@x select 1;
[ERROR:01801] a session prefix is @1 to @1000 and a space
>select * from a;
id | v
1 | ann
(1 rows)
[EXEC] rows_examined=1
>.exit
exiting.. good bye!

Common bugs in this stage

• Tagging only the first line. Every line a prefixed line prints carries the tag: the header, each row, the footer, the [EXEC] line, an error and its HINT:.
• Tagging session 1's unprefixed lines. Only a prefix brings a tag; @1 x and x run the same, one tagged and one not.
• One global transaction flag. BEGIN, COMMIT, ROLLBACK, tx status, 01301 and 01302 are about the line's own session.
• Forgetting to roll back other sessions at exit. The end of the program rolls back every open transaction, not just session 1's.
• Giving ids at COMMIT. A transaction gets its id when it starts; the log's ids are a different counter.

Language notes

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

• C: an array of session structs indexed by number, each holding its transaction state; a current pointer set from the prefix before the line runs, and a tag string the printer puts in front of every line.
• C++: a std::map<int, Session>, and an output helper that knows the current tag.
• Rust: a BTreeMap<u32, Session> keeps sessions in ascending order for sessions and for exit.
• Zig: an AutoArrayHashMap(u32, Session) sorted by key before listing, or a fixed array of 1000 optional sessions.