`BEGIN CONCURRENT` support

[penberg]

SQLite has a branch for BEGIN CONCURRENT, which allows more than one writer: https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md

Allowing BEGIN CONCURRENT and traditional transactions to run at the same time is a bit tricky. If we don't coordinate between them, we can have lost updates and read skew.

Lost update happens when:

• Traditional TX1 reads value A
• CONCURRENT TX2 reads value A
• TX1 updates to B
• TX2 updates to C based on A

Read skew happens when:

• CONCURRENT TX1 reads table 1
• Traditional TX2 updates tables 1 and 2
• TX1 reads table 2
• TX1 sees inconsistent snapshot (old table 1, new table 2)

There are few options to solve this problem:

1. Make all transactions go through the MV store and emulate the SQLite traditional transaction semantics in the VDBE layer. This is probably the best long-term solution, but we might want to give MVCC more mileage before doing this.
2. Notify MV store when a traditional transaction commits allowing it to create new versions of the updated records. This seems to be tricky to get right, though.
3. Disallow mixed style transactions and require the application to opt-in to MVCC via connection open flag. This seems most straight-forward.

[penberg]

Me and @psarna did some work on doing this with SQLite itself which might be helpful https://github.com/penberg/libsql/tree/mvcc. The MVCC implementation itself is based on Hekaton here: https://github.com/penberg/tihku

[hpvd]

this may be also of interest in the field of write concurrency:
Interesting Approach to Write concurrency of sqlite #2043