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. They are a core part of the Atomicity: i.e., the A in ACID.

Typical example

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.

Concurrency

When you have a session with a database, it tries to give you the illusion that you are the only one using it. But that illusion isn’t perfect and 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.

Chained mode

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. (And yes I can undo it with a ROLLBACK).

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.

JDBC and other nasties

I’ve noticed that some applications do a lousy job 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
  • periodically, run statements to commit any uncommitted work

This is not transaction management, and it’s certainly not a formula for an application that will scale.

Don’t let your applications work this way. You can see it if you check traces of commands being sent to the database engine. Manage your transactions explicitly.

2026-11-12