Add transactions (#69)
Transactions are implemented with the START TRANSACTION, COMMIT and ROLLBACK
syntax.
vsql transactions are implemented using Multiversion Concurrency Control (MVCC).
In a nutshell, MVCC maintains multiple copies of rows that are visible to
respective transactions. MVCC is one method to ensure readers and writers do not
block readers or writers. So, unlike SQLite3, there can be multiple concurrent
writing transactions.
By its nature MVCC leaves around copies of rows that have either been deleted or
rolled back. These row versions need to exist until at least the transaction is
finished (COMMIT or ROLLBACK). However, in either case, the rows are not
revisited so they will remain invisible to all other future transactions.
This makes the COMMIT basically zero cost but it requires another process to
clean up expired rows. Since this isn't easily possible or ideal for a
single-process database, vsql performs cleanup on COMMIT and ROLLBACK for any
pages that were modified during the transaction.
The cost of a COMMIT or ROLLBACK should be approximately equal to each other.
However, the cost of an individual COMMIT or ROLLBACK is proportional to the
amount of pages modified and not the number of changes directly.
For this first implementation only READ COMMITTED isolation is used (which is
the default in most other databases). Transaction ID wraparound will be a
problem in the near future to fix. The remaining three isolation levels will be
implemented in the future as well.
