dolt sql-server Concurrency

March 12, 2021

16 min read

Update 2021-08-02: Dolt now supports SQL transactions with COMMIT, ROLLBACK, SAVEPOINT and the other MySQL transaction primitives, so it's now safe to run the SQL server out of the box with multiple readers and writers. The techniques described in this blog post are still valid, but only recommended for advanced users with niche requirements.

In building a version controlled SQL database we see countless applications. I'm excited to see our users collaborating on data thanks to bounties. It was always our thesis that this was the best way to collaborate on data projects. However, the more users that discover Dolt, the more we are asked about using Dolt as an application store. Some people are looking for a MySQL compliant database that supports easy checkpointing for data backups. At the moment, that isn't where dolt shines. Others want to use Dolt to back applications where clients connect to a database, make changes in isolation, and then can merge those changes and resolve conflicts. In this two part blog I'm going to cover using Dolt as a server with multiple concurrent clients, and how to merge and resolve conflicts programmatically.

Background

Those familiar with the inner workings of Git will be familiar with their "three trees" model. Git uses three file trees, HEAD, index/staging, and the working directory in its version control system. The working directory contains the current state of the files, index/staging is the state of the files that will be in the next commit, and HEAD is the latest commit. A commit includes the state of the files, and some metadata including the committer, and the parent commits.

In its normal operation, Dolt does functionally the same thing as Git. However, this can be harder to see because it isn't operating on files. Dolt stores data in a Merkel DAG and the state of the Dolt database can be referenced by a hash. Each of Git's trees has an equivalent hash in Dolt. HEAD is a commit with a hash that points to the state of the database at the time of the commit, staging is a hash that points to the state of the database that will be used in the next commit, and the working hash points to the current state of the database.

In a standard Git workflow you would modify your files in the filesystem. Then use git add to add files to the staging tree. Finally you commit your changes which updates HEAD. In Dolt you would modify your database tables using Dolt commands such as dolt sql which updates the working hash. dolt add adds tables to the staged state which updates the staged hash. When you run dolt commit you get a commit which contains the hash of whatever was staged.

This model makes sense when you are working with files or data on your machine. You have a single user modifying the data locally, and then using commands like push, pull, and merge to interact with other users and their changes. However, that is not how a MySQL compliant database works. When you run an INSERT statement against a MySQL database you expect other users to see that data reflected in their queries almost immediately. The concepts of branches or commits don't exist. There are several ways you might want Dolt's SQL server to function that I think are worth talking about.

Single User Server

One of the most compelling reasons to run Dolt in this mode is to integrate Dolt with existing tooling. Whether it be to use a syntax highlighting GUI that makes it easier to write queries against the database, or to integrate with some visualization software, this is a use case we have come across very frequently. It was the first server use case we tackled and it is the default mode when running dolt sql-server. Only a single connection is allowed at a time, and all changes are reflected in the working set.

Multiple Read Only Clients

If you wanted to provide read only data to multiple clients, Dolt is a very good choice. I worked on a software system previously that requested IP to geo data from a read only service. That service was updated monthly, and there was more than one instance where the ability to rollback would have had huge benefit. Then, during debugging issues with the new data, the ability to diff the data to see what happened would have saved many man-hours of work. Dolt running as a read only server would have been a great replacement for that service. To do this you would need to add the following parameters to your config.yaml file:

behavior:
  read_only: true

listener:
  max_connections: MAX_CONNECTION_COUNT

Multiple Clients Modifying the Same Data

This is how MySQL works, and it is the use case that we are the furthest away from supporting properly. You could certainly run dolt sql-server with multiple concurrent connections enabled, and autocommit turned on. That would work similar to how MySQL without transactions works, however your application may not work as you expect. Clients can stomp each other's changes without notice, and you may end up altering data in ways that you don't intend.

Multiple Clients Working with Independent Copies of the Data

In this mode every server connection is working independently. Each connection has their own HEAD commit and they have their own working state. They can make modifications, and create commits. These actions only affect the local session, and you have to take explicit actions to make your modifications available to other clients. Other clients are not affected by your changes, unless they want them and can take explicit actions to get them. This mode is analogous to users cloning their own copies of the data, working on it locally. Each connection can take explicit action to make their changes available by pushing them to a remote, and they can get other peoples changes by explicitly fetching from a remote and merging with their local branch. The benefits of doing this via dolt sql-server is that clients don't need to clone the entire repository, they don't have to install the Dolt cli and can access the server programmatically in their own applications. This is the mode I will be focusing on in the remainder of this blog.

Running dolt sql-server so Each Session is Independent

dolt sql-server provides many command line switches for configuring its behavior, but there are even more options available if you provide a yaml config file. The config.yaml file below provides 2 key changes from the default sql-server behavior.

behavior:
  read_only: false
  autocommit: false

user:
  name: root
  password: ""

listener:
  host: 0.0.0.0
  port: 3306
  max_connections: 100
  read_timeout_millis: 28800000
  write_timeout_millis: 28800000

The first is autocommit: false. When autocommit is disabled, changes to the repositories HEAD, staging, and working states are not modified. Instead your changes only affect the current session. The second is max_connections: 100 which will allow 100 client sessions to run concurrently.

Managing Session State

A client session tracks the hash of the latest commit in the session, and the state of the database in the session. This data is tracked in the sql session variables @@mydb_head for the hash of the head commit and @@mydb_working for the hash of the current database state (In these cases mydb is the name of your database). When a client connects to the server, the session variable @@mydb_head is set to the latest commit from master, and @@mydb_working to the hash of the database state from that commit.

# Open mysql client application and connect
~>mysql --host 127.0.0.1 --user root --database test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.9-Vitess

# look at the existing branches
MySQL [test]> SELECT * FROM dolt_branches;
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| name   | hash                             | latest_committer | latest_committer_email | latest_commit_date      | latest_commit_message      |
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| master | 3a066h4eisbppu4a5egjccdag25fofqb | Brian Hendriks   | brian@dolthub.com      | 2021-03-11 00:29:09.852 | Initialize data repository |
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
1 row in set (0.007 sec)

# inspect the state of my session noting that @@test_head hash matches the hash of the master branch
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| 3a066h4eisbppu4a5egjccdag25fofqb | ksnsrcorn2urfnmrp19bbktmpgn520dk |
+----------------------------------+----------------------------------+
1 row in set (0.001 sec)

The snippet above shows a new client connection being made to a running Dolt sql-server. By querying the dolt_branches system table you are able to see that the current hash of the branch master matches @@test_head. Any changes we make to the database will result in changes to @@test_working, but will not affect @@test_head.

# create a table
MySQL [test]> CREATE TABLE t (
    -> pk int,
    -> col varchar(32),
    -> PRIMARY KEY(pk)
    -> );
Empty set (0.002 sec)

# note that @@test_working changes and @@test_head does not
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| 3a066h4eisbppu4a5egjccdag25fofqb | olrcbmcj9vb6bpjiujqea1jbsnu9hv8l |
+----------------------------------+----------------------------------+
1 row in set (0.000 sec)

# make more working set changes by adding 2 rows
MySQL [test]> INSERT INTO t VALUES (1,'a'),(2,'b');
Query OK, 2 rows affected (0.002 sec)

# again notice that @@test_working has changed and @@test_head does not
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| 3a066h4eisbppu4a5egjccdag25fofqb | 8k7ckk3l5bk9n6i8u0ovojousn88m3sq |
+----------------------------------+----------------------------------+
1 row in set (0.000 sec)

# make one final edit
MySQL [test]> INSERT INTO t VALUES (3,'c'),(4,'d');
Query OK, 2 rows affected (0.001 sec)

# inspect state once more
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| 3a066h4eisbppu4a5egjccdag25fofqb | 05bfl0q4fvrk7vbtunri4sqphc3brl8g |
+----------------------------------+----------------------------------+
1 row in set (0.001 sec)

Above you can see the effect of write queries on @@test_working. One thing to note is that altering @@mydb_working (or in my case @@test_working) directly can be problematic if you don't know what you are doing. As you make edits to the database there is no guarantee that these edits will be persisted to disk until you commit your data. If I set @@test_head to 8k7ckk3l5bk9n6i8u0ovojousn88m3sq I get an error which will result in any edits made to this session being lost.

# select all the rows from table t to see they reflect all the writes done previously
MySQL [test]> SELECT * FROM t;
+------+------+
| pk   | col  |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.008 sec)

# set @@test_working to be the state hash that existed prior to adding the rows with pks 3 and 4
MySQL [test]> SET @@test_working='8k7ckk3l5bk9n6i8u0ovojousn88m3sq';
Query OK, 1 row affected (0.000 sec)

# attempt to inspect state and note the failure as I've set my working database state to be a state that was never
# persisted to disk
MySQL [test]> SELECT @@test_head, @@test_working;
ERROR 1105 (HY000): there is no dolt root value at that hash

In order to make sure my edits are persisted, I need to make a commit.

The COMMIT() function

COMMIT() is a Dolt specific function that creates a "dangling commit" and returns it's hash. A dangling commit is a commit that is not being referenced by any branches. Calling Commit() alone is enough to persist your working changes. If we go back to our example above, and call COMMIT() at the point where our working hash is 8k7ckk3l5bk9n6i8u0ovojousn88m3sq our data will be persisted, and then we could set our @@test_working back to that value.

# Redo a all the work done above before the error.
# create a table
MySQL [test]> CREATE TABLE t (
    -> pk int,
    -> col varchar(32),
    -> PRIMARY KEY(pk) );
Empty set (0.002 sec)

# note that @@test_working changes and @@test_head does not
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| 3a066h4eisbppu4a5egjccdag25fofqb | olrcbmcj9vb6bpjiujqea1jbsnu9hv8l |
+----------------------------------+----------------------------------+
1 row in set (0.000 sec)

# make more working set changes by adding 2 rows
MySQL [test]> INSERT INTO t VALUES (1,'a'),(2,'b');
Query OK, 2 rows affected (0.002 sec)

# again notice that @@test_working has changed and @@test_head does not
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| 3a066h4eisbppu4a5egjccdag25fofqb | 8k7ckk3l5bk9n6i8u0ovojousn88m3sq |
+----------------------------------+----------------------------------+
1 row in set (0.000 sec)

# call COMMIT() so our working data is persisted to disk and it becomes possible to restore @@test_working to
# 8k7ckk3l5bk9n6i8u0ovojousn88m3sq.
MySQL [test]> SELECT COMMIT('-m','dummy message');
+----------------------------------+
| COMMIT("-m","dummy message")     |
+----------------------------------+
| qus1cmdn1d13a4bm3k7bd4a8os58j49b |
+----------------------------------+
1 row in set (0.012 sec)

# make one final edit
MySQL [test]> INSERT INTO t VALUES (3,'c'),(4,'d');
Query OK, 2 rows affected (0.001 sec)

# inspect state once more
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| 3a066h4eisbppu4a5egjccdag25fofqb | 05bfl0q4fvrk7vbtunri4sqphc3brl8g |
+----------------------------------+----------------------------------+
1 row in set (0.001 sec)

# select all the rows from table t to see they reflect all the writes done previously
MySQL [test]> SELECT * FROM t;
+------+------+
| pk   | col  |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.008 sec)

# set @@test_working to be the state hash that existed prior to adding the rows with pks 3 and 4
MySQL [test]> SET @@test_working='8k7ckk3l5bk9n6i8u0ovojousn88m3sq';
Query OK, 1 row affected (0.000 sec)

# this time the call works and we can see we have a valid state which is equal to what we expect
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| 3a066h4eisbppu4a5egjccdag25fofqb | 8k7ckk3l5bk9n6i8u0ovojousn88m3sq |
+----------------------------------+----------------------------------+
1 row in set (0.000 sec)

# retrieving all the rows now shows our edits prior to adding the rows with pks 3 and 4 as we intended.
MySQL [test]> SELECT * FROM t;
+------+------+
| pk   | col  |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.001 sec)

As a result we undo the changes done after 8k7ckk3l5bk9n6i8u0ovojousn88m3sq. COMMIT() creates a commit object which stores metadata such as the committer, the creation timestamp, and the parent commit hashes along with a reference to the current state. It is the hash of that object that is returned. We've discarded the returned hash here to highlight how modifications interact with persistence. Next we'll use the returned value from COMMIT() to update @@mydb_head. Doing so is analogous to using dolt commit on the command line to update HEAD.

# inspect the commit log
MySQL [test]> SELECT * FROM dolt_log;
+----------------------------------+----------------+-------------------+-------------------------+----------------------------+
| commit_hash                      | committer      | email             | date                    | message                    |
+----------------------------------+----------------+-------------------+-------------------------+----------------------------+
| 3a066h4eisbppu4a5egjccdag25fofqb | Brian Hendriks | brian@dolthub.com | 2021-03-11 00:29:09.852 | Initialize data repository |
+----------------------------------+----------------+-------------------+-------------------------+----------------------------+
1 row in set (0.001 sec)

# inspect the branches table
MySQL [test]> SELECT * FROM dolt_branches;
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| name   | hash                             | latest_committer | latest_committer_email | latest_commit_date      | latest_commit_message      |
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| master | 3a066h4eisbppu4a5egjccdag25fofqb | Brian Hendriks   | brian@dolthub.com      | 2021-03-11 00:29:09.852 | Initialize data repository |
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
1 row in set (0.001 sec)

# inspect our current state.  Notice @@test_head matches the latest commit log commit, and matches the hash of master
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| 3a066h4eisbppu4a5egjccdag25fofqb | 8k7ckk3l5bk9n6i8u0ovojousn88m3sq |
+----------------------------------+----------------------------------+
1 row in set (0.001 sec)

## create a commit with all our changes and set the value of @@test_head to be the hash of that commit
MySQL [test]> SET @@test_head = COMMIT("-m", "commit message");
Query OK, 1 row affected (0.014 sec)

## inspect state and see @@test_head has been updated, and @@test_working is unaffected this time because the newly created
## commit references the current database state. If the commit referenced a different state @@test_working would be modified.
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| kbtqdtbp9e1foh0nna2qaajqi034m19p | 8k7ckk3l5bk9n6i8u0ovojousn88m3sq |
+----------------------------------+----------------------------------+
1 row in set (0.001 sec)

# inspect dolt_branches and see that it is unaffected
MySQL [test]> SELECT * FROM dolt_branches;
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| name   | hash                             | latest_committer | latest_committer_email | latest_commit_date      | latest_commit_message      |
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| master | 3a066h4eisbppu4a5egjccdag25fofqb | Brian Hendriks   | brian@dolthub.com      | 2021-03-11 00:29:09.852 | Initialize data repository |
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
1 row in set (0.001 sec)

# inspect the commit log and see that there is a new commit with our commit message.
MySQL [test]> SELECT * FROM dolt_log;
+----------------------------------+----------------+-------------------+-------------------------+----------------------------+
| commit_hash                      | committer      | email             | date                    | message                    |
+----------------------------------+----------------+-------------------+-------------------------+----------------------------+
| kbtqdtbp9e1foh0nna2qaajqi034m19p | Brian Hendriks | brian@dolthub.com | 2021-03-11 02:24:41.243 | commit message             |
| 3a066h4eisbppu4a5egjccdag25fofqb | Brian Hendriks | brian@dolthub.com | 2021-03-11 00:29:09.852 | Initialize data repository |
+----------------------------------+----------------+-------------------+-------------------------+----------------------------+
2 rows in set (0.008 sec)

Above you'll see that updating @@test_head resulted in the commit we created being visible in dolt_log, but no changes being visible in the dolt_branches table. At this point our changes are only visible within our session.

Modifying dolt_branches

dolt_branches is a special system table that functions fundamentally differently from most things in Dolt. dolt_branches provides read / write access to data that lives outside of the prolly tree. It is not a versioned structure. When you are working with Dolt on the command line, every clone of the data has their own local version of this data. My clone can have different branches and state from your clone. But in server mode, there is a single version of this data that is shared by all clients regardless of the value of their HEAD or working hashes. Making changes to this table is how you can make your changes available to others, how you persist data across sessions, and how others can make their changes available to you.

Below we'll create a feature branch that we can use across sessions. Even though this branch is just for us, we use a lock to when reading to and writing from dolt_branches. REPLACE INTO is equivalent to a force push here. It will insert the row id it doesn't exist, and overwrite any existing value if it does already exist. This branch is not intended to be shared by multiple users, so we don't need to worry about messing up another session's work.

# inspect the existing branches
MySQL [test]> SELECT * FROM DOLT_BRANCHES;
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| name   | hash                             | latest_committer | latest_committer_email | latest_commit_date      | latest_commit_message      |
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| master | 3a066h4eisbppu4a5egjccdag25fofqb | Brian Hendriks   | brian@dolthub.com      | 2021-03-11 00:29:09.852 | Initialize data repository |
+--------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
1 row in set (0.001 sec)

# retrieve exclusive access to the lock named "branches"
MySQL [test]> SELECT GET_LOCK('branches',-1);
+------------------------+
| get_lock("branches", -1) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.007 sec)

# REPLACE INTO will create a new branch, but had a branch with this name already existed it would overwite it
MySQL [test]> REPLACE INTO dolt_branches (name, hash) VALUES ('brian/feature_branch',@@test_head);
Query OK, 1 row affected (0.009 sec)

# release our lock
MySQL [test]> SELECT RELEASE_LOCK('branches');
+--------------------------------------+
| RELEASE_LOCK("branches") |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.001 sec)

# inspect dolt_branches to see our updated branch list containing our new branch
MySQL [test]> SELECT * FROM DOLT_BRANCHES;
+----------------------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| name                 | hash                             | latest_committer | latest_committer_email | latest_commit_date      | latest_commit_message      |
+----------------------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| brian/feature_branch | kbtqdtbp9e1foh0nna2qaajqi034m19p | Brian Hendriks   | brian@dolthub.com      | 2021-03-11 02:24:41.243 | commit message             |
| master               | 3a066h4eisbppu4a5egjccdag25fofqb | Brian Hendriks   | brian@dolthub.com      | 2021-03-11 00:29:09.852 | Initialize data repository |
+----------------------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+

Restoring Your Session and HASHOF()

So far we've connected, made modifications, committed our work, and then created a branch that contains our work on it. Now if we end our session we can come back later and restore our work.

# Open mysql client application and connect
~>mysql --host 127.0.0.1 --user root --database test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-Vitess

# inspect state of my session
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| 3a066h4eisbppu4a5egjccdag25fofqb | ksnsrcorn2urfnmrp19bbktmpgn520dk |
+----------------------------------+----------------------------------+
1 row in set (0.001 sec)

# look at the dolt_branches table to check my @@test_head hash and verify that it is pointing to the tip of master
MySQL [test]> SELECT * FROM dolt_branches;
+----------------------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| name                 | hash                             | latest_committer | latest_committer_email | latest_commit_date      | latest_commit_message      |
+----------------------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| brian/feature_branch | kbtqdtbp9e1foh0nna2qaajqi034m19p | Brian Hendriks   | brian@dolthub.com      | 2021-03-11 02:24:41.243 | commit message             |
| master               | 3a066h4eisbppu4a5egjccdag25fofqb | Brian Hendriks   | brian@dolthub.com      | 2021-03-11 00:29:09.852 | Initialize data repository |
+----------------------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
2 rows in set (0.001 sec)

# set @@test_head to point to the commit referenced by the branch brian/feature_branch
MySQL [test]> SET @@test_head=HASHOF('brian/feature_branch');
Query OK, 1 row affected (0.001 sec)

# note that our head commit has changed and our working state is back where it had been previously
MySQL [test]> SELECT @@test_head, @@test_working;
+----------------------------------+----------------------------------+
| @@test_head                      | @@test_working                   |
+----------------------------------+----------------------------------+
| kbtqdtbp9e1foh0nna2qaajqi034m19p | 8k7ckk3l5bk9n6i8u0ovojousn88m3sq |
+----------------------------------+----------------------------------+
1 row in set (0.001 sec)

When we reconnect, our new session starts with the latest from master. In order to continue our work we need to set @@test_head to the commit being pointed at by our branch. We could query the dolt_branches table to find the hash for that branch, but the HASHOF() function makes this much easier. HASHOF() takes a string reference to a commit, and returns the hash for that commit. The reference string parameter can be a branch name, or it can be 'HEAD' with an optional ancestry spec such as 'HEAD^2'. Above we restore our session by setting @@test_head like so: SET @@test_head=HASHOF('brian/feature_branch');. Something to note is that setting @@test_head has the side effect of changing your working state as you can see by the changing value of @@test_working. Any work that lives only in your working set, and has not been persisted will be lost.

Merges and Conflict Resolution in Part Two

So far our changes have been made in isolation. In part two of this blog I'll be digging into collaborating with others that are working against the same server. We'll cover merges and programmatic conflict resolution. In the meantime visit DoltHub and clone one of our open source datasets and give it a try, or come talk with us about the project on Discord.

Blog