Database transaction handling in C++ systems


Concurrency (either by multithreading or asynchonicity) and database usage are present in every software system. Transactions are the mechanism given by relational databases to provide ACID properties to the execution of multiple statements.

In order to use them, most database systems expect transactions to take place in a single database connection by using explicit boundaries. This means that in order to perform X database actions as a single logical operation the system needs to: get a connection to the database, start a transaction on the connection, execute the actions over that connection and finish the connection’s ongoing transaction. The application can tell whether to finish the transaction by applying the changes or rollback all the performed changes and leave the DB in the exact same form as it was before starting the transaction. Also, the DBMS might forcefully finish abort the transaction due to conflicts generated by other transactions.

In most C++ systems I have worked on I have found that transactions are not handled in a nice way from the architectural point of view. I can basically classify the transaction management and handling in one of two options:

  • Database layer abstracted but not allowing transactions. This situation is represented by the architecture where there are multiple classes acting as repositories which interface with the database. But the abstraction handles the acquisition and release of the DB connection, thus not allowing to have a transaction that can cover multiple methods across one or more repositories. To give an example, there is a repository for the Account object with two methods: save and getById. Each of the methods acquires a connection performs the DB operation and then releases the connection. As each method acquires its own connection, there is no way to have a transaction across the two methods.
  • Database implementation polluting business logic. In this case, we have the business logic acquiring a connection and starting a transaction and passing the DB connection to other methods so that they can pass it to the DB layer. Now, you are able to use transactions in the system but the business logic needs to know the underlying layer in order to start the transaction and also all the functions that might be called need to take the DB connection object as an argument.

None of the aforementioned solutions is a good one. We would like to have the abstraction of the first scenario but with the possibility that the business logic can declare that a certain set of actions should be done within a transaction.

Solution Approach


Let’s see how we can tackle this issue in a nice way. The following UML diagram shows an architectural approach to the transaction handling program that decouples all the components as much as possible. The decoupling is important because we want to be able to mock most of the things in order to successfully test our code.

Architecture of transaction management approach

Business logic

If the business logic requires actions to be run under the context of a single transaction, then it will need to know about the transaction manager.

As the management of transactions is very specific to each implementation, what the business logic actually knows and interacts with is an object that implements the TransactionManager interface. This interface will provide an easy way of executing actions within a single transaction context.

In order to interact with the database layer, the business logic will still need to have knowledge and interact with the model’s repositories. Of course, this is done through an interface and not a concrete type.

Entity repositories

The repositories don’t change at all. They use the same interface to retrieve the connections they need to execute the queries. The difference will be in the setup, instead of using the DbConnectionManagerdirectly, they will use the ConcreteTransactionManager which also implements the DbConnectionManager interface.

This means that the repositories don’t actually care whether a transaction is happening or not.

Transaction Manager

The transaction manager is the key piece in this design. Its purpose is to provide a clear way for the business logic to perform a sequence of actions in the same transaction, while hiding the details of how that transaction is handled.

By using the transaction manager, the business logic doesn’t need to know the underlying implementation of how the persistence layer initiates or closes the transaction.

It also removes the responsibility from the business layer to keep moving the transaction state from call to call. Thus, allowing a component of the business logic that requires transactional behavior to call another who doesn’t but still get transactional results if the latter one fails.

In the diagram above, I assumed the scenario where the transaction is handled through the DB connection, which might not be the case. The way in which the transaction manager and repositories interact will depend entirely on the technicality of how the transaction mechanism needs to be implemented.

Behavior and usage

From the business logic’s point of view what we want to achieve is that the usage is somewhat like this:

void BusinessLogic::foo()
    transactionManager.performInTransaction([&]() {
        Entity a = entityRepo.getById(123);
        if (a.x > 20) {
            a.x -= 20;

Everything that is executed inside the lambda given to the transaction manager, should be done in the context of a single transaction. When the lambda returns, the transaction gets committed.

That way would be fine if transactions cannot fail, but that is not the case. The easiest way for the TransactionManager to communicate the failure of a transaction is through an exception.

In that case we should enclose the call to the transaction manager in a try-catch clause like this:

void BusinessLogic::foo()
    try {
        transactionManager.performInTransaction([&]() {
            Entity a = entityRepo.getById(123);
            if (a.x > 20) {
                a.x -= 20;
    catch (TransactionAborted&) {}

There is one more thing we need to add to make it more complete at a basic level. The business logic needs to be able to trigger a transaction rollback on its own. For that we can also use C++ exception mechanism, the lambda can throw an AbortTransaction which will make the transaction to be rolled back silently. As it was the user who asked for the rollback, the performInTransaction call should finish normally and not through an exception as was the case for the failed transaction.

Nested transactions

The purpose of this blog-post is not to write a full-fledged transaction manager, but to show an architectural solution that is decoupled, versatile and easily expandable.

For the sake of simplicity I am going to assume that if transaction nesting occurs the nested transaction is irrelevant. That is, everything will be done in the context of the outer transaction.

PoC Implementation: a transaction manager for SQLite

As a proof of concept of of this architecture, I am going to write a simple transaction manager for SQLite and write a couple of test cases to show the behavior.

The code is in the following gist: TransactionManagerArchitecturePoC.cpp.

Let’s disassemble the code into the different components.

ScopedDbConnection and ConcreteConnectionManager

In order to make things simple, the ScopedDbConnection is just a std::unique_ptr that takes a std::function<void(sqlite3*)> as its deleter. Doing this, allows us to return a ScopedDbConnection from the TransactionManager that when it gets destructed, it either actually closes the connection or does nothing if the connection belongs to an on-going transaction.

The ConcreteTransactionManager::getConnection() method, simply creates and returns a ScopedDbConnection to the DB we are using. When this scoped connection gets destructed, the underlying connection gets closed.

Transaction manager

There are two important components in the transaction manager: the protocol for transactions and its internal state to give support to the transaction protocol.

The state is composed by one internal data type (TransactionInfo) and a std::map from threads to TransactionInfo instances. A std::mutex also forms part of the transaction manager’s state and is used to synchronize access to the map.

The TransactionInfo is a structure that holds a ScopedDbConnection and a counter. This counter is used to keep track of transaction nesting.

The transaction handling protocol is the meat of the class, this is what actually handles when a transaction gets initialized and when it gets committed or aborted.

Let’s take a look at that code:

void ConcreteTransactionManager::performInTransaction(const std::function<void()>& f)
    auto threadId = std::this_thread::get_id();

    TransactionInfo& transactionInfo = setupTransaction(threadId);

    try {
    catch (AbortTransaction&) {
        if (--transactionInfo.count > 0) {
    catch (...) {
        if (--transactionInfo.count > 0) {

    if (--transactionInfo.count > 0) {

The protocol is really simple: when starting into a performInTransaction block, we setup the transaction which results in a TransactionInfo object reference. Then we execute the given function and when that function exits, we reduce the count on the transaction information object. When the count reaches 0, depending on how we reached the count, the transactions gets committed or aborted. In case the transaction finished by an exception different than AbortTransaction, the exception is re-thrown.

commitTransaction and abortTransaction are really simple functions that just execute a statement using the connection from the transaction. They also remove the transaction from the on-going transaction map.

The setupTransaction is also really simple: it checks whether there is an on-going transaction for the given threadId, if there is then it just increments its count and returns. If there’s not it initializes a new TransactionInfo, places it in the map and executes the statement to start a transaction.

The other important function in this class is getConnection. This needs to handle two cases: (a) if something is executed outside the context of a transaction, the returned connection needs to destroy itself when going out of scope; and (b) if a connection is requested within the context of a transaction, the ScopedDbConnection returned must not close the inner connection when going out of scope.

The way I decided to handle the latter is to return a new ScopedDbConnection containing the sqlite3 connection of the transaction but with an innocuous deleter. This way for the client is totally transparent whether the connection comes from a transaction context or not.

ScopedDbConnection ConcreteTransactionManager::getConnection()
    auto threadId = std::this_thread::get_id();
    std::lock_guard<std::mutex> _(_currentTransactionsMutex);
    auto it = _currentTransactions.find(threadId);
    if (it == _currentTransactions.end()) {
        return _connectionManager.getConnection();
    return ScopedDbConnection(it->second.dbConnection.get(), [](sqlite3* c) {});

The developed transaction manager has the following properties:

  • Transactions are per thread. This means that the transaction is associated to the thread who started it. Only the actions performed by that thread included in the transaction.
  • Transactions cannot be shared. This derives from the former item and means that one thread cannot give its current transaction to other threads. So, if thread A opens a transaction and launches thread B, the actions that thread B performs are not covered by the transaction initiated by thread A and there is no way to make that happen.
  • There are no nested transactions. If while in the context of a transaction a new call to executeInTransaction is made, this second call doesn’t have any practical effect. A call to abort aborts the already on-going transaction and a successful exit from the inner transaction doesn’t trigger a commit of the outer transaction.


Advantages of the given design

One of the most important advantages this design has is that it is so decoupled that mocking it is really easy to do. This helps testing the code that uses it with minimal effort and no dependencies.

It is also really easy to use and transparent that it is really non-intrusive to the code that uses it. And only the code that wants to use transaction capabilities requires knowledge of it. The rest of the code that needs to interact with it in order to provide the transaction functionalities (i.e the repos or whoever uses the DB) don’t know about transactions. This gets masked by the TransactionManager providing the ConnectionManager interface.

Also all the transactional functionality, is encapsulated by the TransactionManager. This makes it easier to test implementations in isolation without requiring other components to know any logic about transactions.

What can be improved?

The PoC is really simple and there are many edge cases that it doesn’t take into account.

When implementing a production solution, one has to be aware that the repositories can fail due to transaction problems and probably mask those as a TransactionAborted.

For my PoC, I decided to use abstract classes and virtual functions to provide polymorphism, but this can be implemented using templates.

It is also arguable that the ScopedDbConnection returned carries no guarantees that it is not going to be retained by the callee. A different approach needs to be taken to guarantee that: maybe the connection manager can have a method executeOnConnection(std::function<void(const ScopedDbConnection&)>).


Syncing external hard-drive with dropbox for backup


This little project started because Bitcasa is dropping their Personal Drive product which I used to use. This forced me to change to another cloud storage provider and I decided to use Dropbox. (During this process I found out how broken Bitcasa is/was and got really furious. But it will be a topic for another blogpost)

One of the things I liked about Bitcasa is that they provided a FUSE that I could just mount anywhere. There was no “syncing” of the files in the sense that the files only existed in the cloud provider. It would download the chunks of the requested files on demand and keep them in a cache. This allowed me to not have to worry about disk-space in my physical hard-drive.

Dropbox, on the other hand, doesn’t work like this. When you setup the daemon, you select a folder to be mirrored to the cloud. The daemon monitors any changes in the folder or cloud and keeps both copies synced. The problem with this is that it requires to have as much space in the device where the dropbox folder is as the contents stored in Dropbox. For my immediate situation, that would work but it is definitely not going to scale. I have a 256Gb disk and around 100Gb of data to store in Dropbox.

One possibility is to restrict the content to be mirrored. With this you get a partial syncing of your Dropbox account in your local folder. But after what happened to me with Bitcasa (I lost files, MANY files), I want to have a physical backup copy in an external HD to be on the safe side in any event.


After doing some research I decided to take the following approach in order to tackle the problem.

I run an instance of Dropbox solely for the purpose of syncing my external hard-drive. In this way it doesn’t interfere with the files that I actually want to have always synced in my desktop.

I run the external hd Dropbox instances manually and I haven’t automated this process. The reason behind this decision is that if I accidentally delete something from Dropbox, the backup will still have it and it won’t sync until I tell it to do so.

Running a second instance of Dropbox

Dropbox installs the folders .dropbox and .dropbox-dist under the home directory.

The first one has all the configuration for the Dropbox instance, while the latter has the binary dropboxd and the files required by it.

If you try executing dropboxd, it will complain saying that Dropbox is already running (for syncing the folder in the home directory).

The key to be able to run more than one Dropbox instance is to know how Dropbox determines the location of the .dropbox configuration folder. As it is in this folder where all the configuration for an instance is stored, where all the cached elements are kept and also where the pid file is kept what prevents multiple instances using the same config.

The location used by Dropbox for the configuration directory is $HOME/.dropbox. Thus by changing the value of the HOME environmental variable when we execute dropboxd, we can change the configuration folder and have as many instances as we want.

I mount my external hard-drive on /mnt/external-hd/, so I just execute HOME=/mnt/external-hd/ /home/santiago/.dropbox-dist/dropboxd.

The first time it will ask for the instance setup information: account, password, location of the mirrored folder, etc. After the first time, it will run silently.

One caveat is that if the mount directory of your external hard-drive changes, then you should be careful when starting the external-hd’s Dropbox service. If dropbox thinks you have deleted the data, it will sync that upstream and you will lose the data. To prevent this, before running it, create a symlink from the old location to the new and then move the location to the new one using Dropbox’s configuration setup.


Setup NAT Network for QEMU in Mac OSX


I am working on a really cool project where we have to manage virtual machines that are launched through QEMU. The project is meant to run in Linux, but as I (and all my colleagues) have a Mac to develop, we wanted to be able to run the project under Mac. Yes, I know QEMU’s performance on Mac sucks!

We couldn’t use QEMU’s user mode network stack due to its limitations. We needed to use TAP interfaces, the machines should be able to acquire the network configuration through DHCP and should be NATted.

An schema of how I wanted things to be is as follows:

VM NAT bridged network schema

Enable TAP interfaces in Mac

The first issue I stumbled upon is the fact that Mac does not have native support for TAP interfaces.

In order to have TAP interfaces in Mac we need to download and install TunTap.

Once it is installed, we are going to see a list of TAP nodes installed with the scheme /dev/tapX. TunTap determines the max number of possible TAP interfaces and sets them up.

Creating the bridge interface

Once we are able to use TAP interfaces, we need to create the bridge where we can attach them.

This is really straight-forward in Mac. For a temporal bridge we just need to issue the following command with elevated privileges:

$ sudo ifconfig bridge1 create

The next step is to configure the address for that newly created bridge. Which IP we give it, will depend on the network we want to use for our VM network. As an example, I will use the network, so I will assign to the bridge. It will act as the default gateway for all the virtual machines, that is why we need to assign that IP statically.

$ sudo ifconfig bridge1

Packet forwarding and NAT

The next step to reach our goal is to configure our Mac so that the packets that arrive from the bridge1 interface are routed correctly. We also need to NAT these packets as otherwise they won’t find their way back.

Enabling packet forwarding is really easy, we just need to execute:

$ sudo sysctl -w net.inet.ip.forwarding=1

For the NAT we need to create a pfctl configuration file where we state the rule that will do the NAT. In a file we write the following:

nat on en0 from bridge1:network to any -> (en0)

This rule tells pfctl to NAT packets that:

  1. Go through en0 (you should replace this interface for the one connected to the internet) and
  2. Have source IP from the network range associated to bridge1 (here goes the bridge name of our VM network)

The address to use for the NAT is indicated after the ->. We need to put the interface connected to the internet between parenthesis. The parenthesis are important because it forces the evaluation of the address associated with the interface each time the rule is applied. If we don’t add it, the address to use will be resolved at load time and if it changes, the used address will be incorrect.

Now we need to enable the pfctl with the given rule.

$ sudo pfctl -F all # This flushes all active rules
$ sudo pfctl -f /path/to/pfctl-nat-config -e # Enable pf with the config

Setting up the DHCP server

Before setting up the virtual machine, we need to set up the DHCP server so the VM will be able to acquire the network configuration easily.

Fortunately, Mac OS comes with a DHCP server installed by default and the only thing we need is to set it up and start it. The server is bootpd and is located under the /usr/libexec/ directory.

The DHCP server reads the configuration from the file /etc/bootpd.list and we need to edit it.

The bootpd.plist file has 3 main sections (detailed explanation in the official documentation):

  • Root dictionary keys: this properties are used to control the behavior of the daemon as a whole.
  • Subnets property: An array of the subnetworks that the DHCP server has associated.
  • NetBoot property: This is used to configure the netboot options.

We are going to be interested in the first two sections as they are the ones that are needed to have the DHCP service up and running.

Here’s the file as we need it:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
                <string>VM NAT Network (</string>

Let’s drill down to the important elements in the xml file.

The dhcp_enabled key in the root dictionary is used to state which interfaces we want to associate the DHCP with. We must here add the bridge interface name, otherwise the DHCP service won’t listen to the DHCP requests on that interface.

The other required thing we need to do is add an entry in the array associated with the Subnets key. Each entry is a dictionary that will describe a subnetwork to be used by the DHCP service. The following is a description of the main keys used above (again, for the complete list see the documentation):

  • name: A string just to give the subnetwork a human readable aspect.
  • net_address: this is the subnetwork base address. In our case
  • net_mask: the subnetwork’s mask.
  • net_range: which range in this subnetwork is managed by the DHCP server. The value of this property is an array that contains two strings: the lower and upper bound of the addresses to manage. In our case, we want the DHCP to manage all the hosts but the one assigned to the host, then our range is:
  • alocate: this boolean property tells the DHCP server whether to assign or not IP addresses from the range. We must set it to true.

The other two keys are used to push configuration to the DHCP clients. We want to push the default gateway as well as the DNS, for that we use the dhcp_router and dhcp_domain_name_server option.

Now that the configuration has been set up, we need to start the DHCP server. To do that, we just execute $ sudo /usr/libexec/bootpd -D. This will launch the server in the background with DHCP capabilities on. If we want to have it in the foreground and see how it is working, it can be launched using the -d flag.

QEMU and interface setup

The last thing to do is to launch the virtual machines and correctly set up the attached interface so that it is correctly attached to the bridge.

We are going to be using a TAP interface setup in QEMU using a virtio NIC. We cannot use the bridge setup in Mac due to the inexistent qemu-bridge-helper for the platform.

To configure the virtio device we need to use the following command line arguments: -net nic,model=virtio. Here is where we would also specify the MAC address for the interface if we want to.

The command line argument specification to setup the interface as TAP is like this: -net tap[,vlan=n][,name=name][,fd=h][,ifname=name][,script=file][,downscript=dfile][,helper=helper]

From those arguments we are interested in 2 of them in particular, script and downscript. The files given in those arguments are executed right after the TAP interface is created and right before the TAP interface is destroyed, respectively. We need to use those scripts to attach and detach the interface from the bridge.

The scripts receive one command line argument with the name of the interface involved. We need to create two scripts:

  • qemu-ifup.sh will be used as the start script and will attach the interface to the bridge:

ifconfig bridge1 addm $1
  • qemu-ifdown.sh will be used in the downscript to detach the interface from the bridge before it is destroyed:

ifconfig bridge1 deletem $1

All that’s left is start the VMs and enjoy the newly created NAT network.