SQL: Implicit vs Explicit Transaction Handling - JDBC Driver I'm looking at you

SQL: Implicit vs Explicit Transaction Handling - JDBC Driver I'm looking at you

In a relational database like SQL Server, transactions are the mechanism used to ensure that entire operations either complete or are rolled back. The obvious example used to be that if you transfer funds from one place to another, that both the debit and the credit need to occur, or neither occurs.

Fair enough and straightforward enough.

Computers also try to give you the illusion that you are the only one using them. Concurrent transactions are a place where that illusion breaks. While you are working in a transaction, you are potentially affecting other users of the system. So we try to manage how long transactions are held open for. The aim is to always have transactions protect what’s needed but be as short as possible.

If you want applications to scale, you need to clearly manage the lifetime of your transactions.

In SQL Server, if I just execute an UPDATE statement, the statement either completes or doesn’t but no other transaction is involved. This is the default behavior.

But SQL Server also has an option to enable implicit transaction handling. This used to often be called “chained mode”. I can enable it like this:

SET IMPLICIT_TRANSACTIONS ON;

With this enabled, whenever I execute a statement that modifies data, SQL Server automagically starts a transaction for me. Importantly, the data is not committed until I explicitly later say COMMIT.

I really dislike this option because it breaks my desire to manage transaction lifetimes, and breaks the rules about keeping transactions as short as possible.

I’ve noticed that some applications do this instead of managing transactions. JDBC-based apps seem to be key offenders. What they do is this:

  • enable implicit transaction mode
  • do whatever work they need to do, making changes when required
  • later run statements to commit any uncommitted work
  • and yet again execute yet another commit if there is still any uncommitted work

I’m sorry, but this is not transaction management, and it’s certainly not a formula for an application that will scale.

2018-04-30