Database access and transactions

In this article, I’d like to explore access to database and transaction demarcation. I want to showcase several examples, building from bottom to top.

Experience of a Junior developer

My main motivation for this article is following. In 2004, I started my career as a Java developer. I learned Java on the go and I participated on various projects over time. Roughly said, here is, how I was told to access a database on my first few projects:

The project was build using EJB2 beans and Hibernate framework. In order to access the database, I always used the magic method getDatabaseSession and that was it. I only needed to understand, how SQL queries work. However, I never had to deal with transactions explicitly. Despite this, I created production rated code.

Now, such approach works most of the time. However, if something went wrong and the code did not work correctly, I was lost. I was unable to find the root cause and I was overwhelmed with the complexity of layers upon layers. I was also unable to solve tasks, which required to go beyond simple request-response model, e.g. long running queries, reports, batch jobs. In those cases, a senior developer had to do the job.

To be honest, I never ever learned, how EJBs work exactly. However, I became interested in the underlying concepts and how they translate to enterprise patterns. I was namely interested in JDBC and proprietary database features.

Things became clear on subsequent projects, which were using Spring transactions. We used AOP and we wrapped all our services with transactional interceptor. In many cases, we handled transactions programmaticaly using TransactionTemplate. Here is why Spring helped me with my understanding:

  1. Documentation is much clearer. This piece of documentation was a big eye-opener. You should really read, get hands on and try to understand as much as possible from this article.
  2. Source code is clearer and easier to understand.
  3. You can run and debug a Spring application with full transactional setup easily. There is no need for a heavyweight application server.

And my last lesson was a PHP project, which was processing real financial transactions. I was shocked to discover an ad-hoc and very optional usage of transactions. Those few database transactions that I found in the code were ad-hoc snippets like this one:

        $this->update(['order' => $order1], 'id = ' . $id1);
        $this->update(['order' => $order2], 'id = ' . $id2);
        return $this->_db->commit();

I checked, how PHP world approaches problem of transaction demarcation. Well, there certainly is no such thing as Enterprise PHP. If you check documentation of a modern PHP framework like Laravel, you will only find a very short page on this topic. The most high level pattern is the DB::transaction(callback) method.

Ok, so what did I learn? Here is the list:

  1. How to start a transaction
  2. Importance of code composability
  3. Threadlocal pattern used by Spring transactions

There is one important prerequisite, which we will assume as true, while describing examples in this article. All services described in examples are short ones. By this we mean duration of less than a second and ideally only few milliseconds on average. This excludes services, that include following:

  • Long running report queries
  • IO access to remote resources (e.g. remote HTTP endpoints)
  • Long running computations (mathematical, graphical)

There will be a separate article dealing with transactions in long running services.

How to start a transaction

Intuitively, you might think, that every database server supports transactions by accepting 3 commands:


The truth is, that ANSI-92 does not specify a common way on how to start a transaction.

MySQL’s InnoDB engine by default runs in so called “autocommit” mode. When you open a connection, than every DML is persisted immediatelly. There are two ways on how to start a transaction. To be honest, it is really confusing.

On the other hand, DB2 does not support autocommit at all. You always have to explicitly call COMMIT. And like many other, I have also been fooled by DB2 client tools, that I’ve been using, because these tools sneak in COMMIT every time you execute a DML statement.

Rules change again, when you access your DB using a driver/adapter. E.g. in MySQL, you can call “BEGIN TRANSACTION” as many times, as you want. In Java JDBC driver, you can call setAutoCommit(true) as many times as you want. However, the PHP MySQL PDO driver keeps an internal flag and refuses to execute beginTransaction twice, without a commit or rollback in between.

  // run this in
  require "util/public_db_info.php";
  $connect = new PDO($dsn, $user_name, $pass_word);
  $connect->beginTransaction(); // fails with "A transaction is already active"
  $connect = null;

Well, what can we count on than? To me, PHP MySQL driver behaves the most correct way. I learned to pretend, that all databases and drivers behave the same as PHP MySQL PDO driver. This forces you to write an application code, which never starts a transaction, when you already started one. Otherwise, semantic of such operations becomes unclear. Do you want to join an already running transaction? Or did you want to start a brand new transaction?

To summarize, an ideal database/framework should (according to me) not allow to request for a transaction start, if a transaction has already been started and not finished yet in current stacktrace.

Finishing a transaction

A running transaction can either be committed or rolled back. Same as above, an ideal database/framework should not allow to request for commit or rollback, if no transaction has yet been started in current stacktrace. Again, only PHPs PDO driver behaves this way:

  // run this in
  require "util/public_db_info.php";
  $connect = new PDO($dsn, $user_name, $pass_word);
  $connect->commit(); // fails with: There is no active transaction
  $connect = null;

Search for a safe pattern

Let’s talk about the same PHP snippet again:

        $this->update(['order' => $order1], 'id = ' . $id1);
        $this->update(['order' => $order2], 'id = ' . $id2);
        return $this->_db->commit();

It suffers from multiple problems:

  1. No error handling
  2. Not composable
  3. Boilerplate

Error handling

First of all, there is no error handling. If update of order2 fails, than an exception is thrown, but not handled immediately. There is an active transaction associated with database connection _db. We have to rely on the fact, that Apache/PHP will close this connection physically at the end of this request thread and the transaction will be rolled back. In other languages and environments, connections might be borrowed from and returned to a connection pool. A connection pool might not be smart enough to check for any ongoing transactions.

So it can happen, that a new thread will use a connection, which already has been used in another thread and left in an inconsistent state.

In following example, we explore one such flawed connection pool:

This datasource will never commit or rollback on it’s own. It just takes care of connection pooling and ignores the fact, that transactions might be still running when a connection is being returned to pool. You should never encounter such datasource in normal environments. But it is always to be safe than sorry.

Now, let’s explore three services, which works with this connection pool. They will deal with a simple table, containing one single auto incremented unique integer column. The table is prefilled with value 1.

First, we have a method, which is very similar to the problematic PHP snippet.

We use Java7 autoclosable pattern to make sure, that connections, statements and result sets are properly closed. However, transactions are started and committed “by hand”. If you run sk.ygor.dbtransactions.MainApplication#main from the same repository, than you can test this method by invoking URL http://localhost:8080/insertWithForgottenCommit. Let’s add multiple users this way.

Next, we have a method for reading list of users. The method does not deal with transactions at all, it just executes a simple query.

You can invoke this method from http://localhost:8080/readRowsWithNoTransaction. It will display a growing list of users.

Finally, we present a method, which deals with transactions in a more involved way.

It comes in two flavors. http://localhost:8080/insertWithProperHandling will start transaction, insert user and commit. http://localhost:8080/insertWithProperHandlingRollback will start transaction, attempt to insert duplicate user and rollback.

This demonstrates, that we can have a transaction running across multiple user requests for an indefinite duration. However, this could be prevented, if we used the pattern shown in third method all the time.

This is in fact what Laravel’s DB::transaction or Spring’s Transactiontemplate.executeIntransaction is doing. It starts a transaction, executes provided callback (Runnable), commits and returns the value of the callback. If the callback throws any kind of exception, than the transaction is rolled back and the exception is propagated upwards.

Validating the pattern

Let’s think for a while about the relationship between throwing any kind of exception and rolling back a transaction. Is this a one-to-one partnership ?

  1. Don’t we wish to propagate the exception without rolling back transaction?
  2. Don’t we wish to rollback a transaction even in cases, when no exception is thrown?

I will dismiss the first case right away. Using exceptions to control flow of program is an anti-pattern. I learned, that I never ever needed them when constructing business logic. Business logic is a well analyzed set of scenarios and use cases with well defined set of responses and all this can be modeled with proper interfaces and return types. Have you ever received following request from a customer “If database becomes deadlocked during order processing, than the order needs to be flagged with a blue star”? I don’t think so. In light of this, I cannot imagine any useful scenario for propagating an exception outside of executeIntransaction pattern without rolling back the transaction.

Let’s think about second case. We are inside the callback, we have executed some SQL queries and we have found out that all changes need to be rolled back. How did we find out? If some of user input is invalid, than we should have validated the input before we performed unwanted data changes. Were we running a long running batch and we received a signal to interrupt the job? In such case, we should split the job into short running batches, execute each batch in a separate transaction and only intercept interrupt signals in between batches and outside of executeIntransaction callback. I have never encountered and cannot think of a situation, when business logic defines an expected situation, which can only be handled by rolling back changes done in current transaction.

Spring supports this case though, using <code>TransactionStatus.setRollbackOnly(), similar to setRollbackOnly by EJB.. I am not a fan of this approach. You need to organize your services well. Is it too hard to organize your code in a proper way? What if some business logic can only be fulfilled after DB changes have been executed. But what can we learn from those changes? Value of an autoincremented ID column? That’s hardly a value you want to validate.

So, I am dismissing this case as well. Business logic must never explicitly deal with database rollbacks. Business logic must only deal with transaction blocks as a whole. A transaction block defined by business logic is never expected to fail and rollback. It can only fail and rollback on following:

  1. An infrastructure issue, e.g. DB server is down, network cable to DB server was pulled out.
  2. Programming error. A simple case like null pointer exception. Or a more tricky case, like DB deadlock. This is a bug and needs to be fixed.

I hope, that you now also accept the one-to-one relationship between thrown exceptions and database rollback in a business application. This means, that from now on, we want to start/commmit/rollback transaction always by using only executeIntransaction pattern.

Composable code

We are now wrapping random parts of our code in executeInTransaction blocks.

What would happen if executeInTransaction is called already in another executeInTransaction? This could start two transactions and as we already explored, this would fail e.g. with PHP MySQL PDO. With JDBC, second, third and later request to start a transaction would do nothing, however, the first commit, in the innermost invocation of executeInTransaction would prematurely commit before outer block have the chance to execute all queries. This means, that our code does not compose well.

An obvious solution is to make the pattern smarter. We use an internal counter that keeps track of have many levels of nested calls have been made. In a single threaded PHP, we can use a static (global) variable. In Java, we can use a ThreadLocal variable. Here is a prototype of such solution:

First ThreadLocal keeps the counter. We also need a second ThreadLocal, because during invocation in one thread, we have to use the same connection.

For simplicity reasons, our pattern only supports update operations:

And here is the full solution:

Notice, that only at level 0, we grab new connection from datasource, we start a transaction and commit or rollback. On all other levels (1,2, …) we reuse connection from ThreadLocal, we do not start transaction and we do not commit or rollback. With this solution, we have come pretty close to the magic behind Spring’s TransactionTemplate. While our example deals with a single datasource only, Spring works fine with multiple datasources. Feel free to study the implementation itself.

Boilerplate code

Once you have such pattern available, you will be tempted to use it everywhere. Every time you create a function, which executes multiple DML statements, you will want to wrap contents of the function into the pattern. When you compose two such functions, the composing code needs to be wrapped in the pattern too. Soon, you will find, that your code is full of boilerplate. Can we improve on this ?

Unclear transaction boundaries

As you design your application, you should always have a clear overview on all transaction boundaries in whole application. This will be hard with the boilerplate solution. You need to analyze call hierarchies and identify “outer-most” executeInTransaction calls. I argue, that it is much better to only use executeInTransaction in places, where transactions really start/end. By definition, these are now places, which are not composable anymore. Naturally, these are top-level places from call hierarchy point of view. A good name for them is “entry-points”. In a web application, these are typically your web controller methods, which serve a single HTTP request.

Frameworks like Spring additionally enable you to “wrap” your whole controller method in a transaction, without explicitly using executeInTransaction. This is usually done using annotations. The result is the same.

Please, check a simple demo, which shows usage of all patterns described so far.

In a typical web application, this approach covers majority of web controller methods. Remaining cases are potentially long running methods, caused either by need to perform network IO, data intensive processing (DB batches) or computation intensive processing. You must not wrap these methods into a single transactional block. Instead, you must handle this by inserting transactional blocks outside of long running operations. This will be further described in my post about distributed financial transactions and batch data processing.

Side effect free solution

I’ve always had the hardest time explaining and persuading my coworkers, that they only need to use the pattern in top entry points. But they either don’t mind boilerplate, or they don’t consider this boilerplate. Or they don’t often find themselves analyzing code, which has already been written. They think, that leaving out the executeInTransaction in some DAO method introduces the risk, that this method will be called without an enclosing executeInTransaction and thus create a service, which can leave data in an inconsistent state. I tell them, that “you need to know, what you are doing”, but that does not seem to remove their fears.

I understand where that fear is coming from. In a pure DAO method, nothing indicates, that a transaction must already have been started:

The root cause for this is the way on how the code accesses the database. The access is usually injected by dependency injection as a singleton object, which represents DB access. In PHP, this can be a pure PDO object or an framework provider adapter, e.g. Zend’s Zend_Db_Adapter_Mysqli. In Spring, you usually inject a java.sql.DataSouce and wrap it into JdbcDaoSupport or to Hibernate.

They don’t behave like singletons though at all, because they have a state attached to them. One such state is the presence of a started transaction. I also always kept wondering, how did different DAOs in my Spring projects ensure, that they will to work on the same connection. As we already mentioned, Spring does all the magic. I recommend to check documentation and source code of Spring’s org.springframework.jdbc.datasource.DataSourceUtils#doGetConnection. Default behavior of java.sql.Datasource is overriden by relying on state store in various ThreadLocal.

Can we achieve same result, but without global state and ThreadLocal hacks? How does a pure solution look like?

Since we want to track state in a pure fashion, we need to introduce an additional parameter to all our functions. This parameter will simply represent a connection on which a transaction already has been started. DAO classes will no longer take database as a singleton dependency. Instead, every DAO method will contain an explicit parameter. For starters, the JdbcTemplate will work just fine:

This parameter is than created by the outer-most orchestrator:

This is the purest and most correct solution to me. We have lost some advantages though. Our web controller and services now have a very strong dependency on classes, that provide database access. In fact, every method in every class, which accepts the JdbcTemplate parameter can execute SQL queries. Can we isolate this capability back to DAO module only?

The solution could be to transform JdbcTemplate into a “token” object only. It would still carry the template, but the there would not have a public accessor and it would be seemingly unusable. If the language allows a fine grained access levels (private, protected, package-friendly), we could require to wrap to token in a class and get access to DB operations only after that.

I am not aware of any framework, that would promote such a solution. I am currently exploring this approach in my free-time hobby projects.


Hopefully, this article shed some light on the problematic of database transaction demarcation from applications. If you are a PHP programmer, I recommend to start wrapping your requests in transactional blocks, using any pattern available by framework of your choice. If you are a Java programmer, you should now know, how frameworks like Spring provide support for transactions and you should understand, how this support relies on thread-per-request model. This approach might not translate correctly, once you start using various reactive capabilities to your application.

Leave a Reply

Your email address will not be published. Required fields are marked *