Many Sessions, One Process

Read an @N prefix on any line and run the rest as session N, with its own transaction and its own transaction id; tag every line printed for a prefixed line with @N| ; list the sessions; roll back every open session at the end of the program; and refuse CHECKPOINT and VACUUM while another session has a transaction open.

Not run yet · 8 tests

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:

shell

$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.

Drawing the diagram…

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:

droid session

>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.

The algorithm in words

Reading a line

Find the session before anything else reads the line.

1.If the line does not start with @, it is session 1's, and what it prints is not tagged.
2.Otherwise read the digits after @. With no digits, a number outside 1 to 1000, or no space after them, print 01801 and read the next line.
3.Find session N, creating it if this is the first line that names it. The rest of the line, trimmed, is what session N runs.
4.Run it as before, against session N's transaction state, and put @N| in front of every line it prints.

Starting and ending transactions

One counter for the whole program, one transaction per session.

1.Keep one counter of transaction ids, starting at 0 when the program starts.
2.BEGIN in a session with no open transaction takes the next id. So does an autocommit INSERT, UPDATE, DELETE, CREATE, DROP, TRUNCATE, ALTER, CREATE INDEX, DROP INDEX or ANALYZE, once it has been parsed.
3.COMMIT and ROLLBACK end only the transaction of the session that sent them.
4.At .exit, end of input or the end of -c, roll back every open transaction from the lowest session number up, printing nothing, then save as before.
5.Before CHECKPOINT or VACUUM: if the line's own session has a transaction open it is 01304; if any other session has one open it is 01802.

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.

Input	Output
`@2 <line>`	every line `<line>` prints, each starting `@2
`<line>` with no prefix	what session 1 prints for it, untagged
`@0 ...`, `@x ...`, `@`, `@1001 ...`, `@2select ...`	`[ERROR:01801] ...`
`sessions`	`[SESSIONS] <n> trx=<id> state=<IDLE or ACTIVE> waits_for=0 iso=REPEATABLE_READ`, one line per session, ascending; `trx=0` when IDLE
`checkpoint;` or `vacuum;` while another session has a transaction open	`[ERROR:01802] ...`
`.exit` with transactions open	nothing more than `exiting.. good bye!`
`@02 <line>`	runs as session 2, tagged `@2
a lexer or parser error after a prefix	its column counts from the start of the line after the prefix
`@2 <meta command>`	does what it always does, its output tagged; `@2 .exit` ends the program
`sessions <anything>`	`[ERROR:00104] ...`

What you should see when done

shell

$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.

Loading starter code…

No run yet

8 tests wait for your first run

Run tests builds your code, drives the binary through every case for this stage, and shows expected and received output side by side here. Failing cases come first.