Database/sql Tx - detecting Commit or Rollback

sql

transaction-management

error-handling

database-operations

byAlex Kataev·Dec 2, 2024

Quickly determine if a Commit or Rollback has occurred within Go's database/sql by harnessing a custom struct which contains status flags. Implement wrapper methods to set these flags:

type TxStatus struct {
    *sql.Tx
    Committed, RolledBack bool
}

func (ts *TxStatus) Commit() error {
    ts.Committed = ts.Tx.Commit() == nil
    return nil
}

func (ts *TxStatus) Rollback() error {
    ts.RolledBack = ts.Tx.Rollback() == nil
    return nil
}

// Initialize with: ts := &TxStatus{Tx: tx}
// Status check with: ts.Committed or ts.RolledBack after ts.Commit() or ts.Rollback()

Let's dive deeper into the world of transaction management and explore robust techniques and patterns for handling Commit and Rollback scenarios.

Managing errors and panics - Keep your guard up!

In transaction management, being diligent about handling both errors and panics is essential. Here's what you need do:

defer is your time-lapse guardian angel. Enlist its help to ensure tx.Rollback() and tx.Commit() are called no matter what comes swinging!
Employ recover() within defer to swoop in and catch any thrown panic and immediately initiate a Rollback.
error is moody, always inspect its state returned by Commit() and Rollback().

Procedure for Commit and Rollback - The Step-by-Step Dance

Set up defer to Rollback initially, but hang on! If Commit is successful, we skip over this step!
Protect tx.Commit() with an error-checking armor. If err guards are down (is nil), Commit can pass through!

defer func() {
    if err != nil {
        if rbErr := ts.Rollback(); rbErr != nil {
            log.Fatalf("tx.Rollback just failed miserably: %v", rbErr)
        }
    }
}()

// Magic happens here...
if err = tx.Commit(); err != nil {
    // Handle err
}

If err wears the cloak of mystery (is not nil), don't let Rollback steal err's identity after a failed Commit.

if err = tx.Commit(); err != nil {
    log.Printf("Failed to commit! It's not you, it's me: %v", err)
    if rbErr := tx.Rollback(); rbErr != nil && err == nil {
        err = rbErr
    }
}

Lean back and relax! Uncommitted transactions are ruthlessly rolled back by the database on Tx close.

Streamlining with a transaction handler - The Puppet Master

Strive for simplicity by encapsulating Commit or Rollback handling within an all-knowing helper, a 'transaction handler':

func withTransaction(db *sql.DB, fn func(*TxStatus) error) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    
    ts := &TxStatus{Tx: tx}
    err = fn(ts)
    
    if err != nil {
        ts.Rollback() // Oops, something went horribly wrong
        return err
    }
    return ts.Commit() // All systems go!
}

This allows you to perform operations within a transaction without worrying about Commit or Rollback specifics — withTransaction expertly handles it for you.

Common Missteps - What not to do!

To achieve an Oscar-winning transaction management performance, beware of these common pitfalls:

Conditional Rollback complexity

Plenty of action in defer that handles transaction Rollback can lead to a convoluted plot. Simplicity is king — stick to it.

The villain of shadows - Error shadowing

Don't let the villain secretly swap places with someone else! This can happen unnoticed in error handling within the defer closure, beware!

Transaction leaks - Sneaky Retreats

Be watchful! If a function that owns the transaction makes an early exit without ensuring the transaction is committed or rolled back, it may lead to database resource leaks.

Unchecked Rollback errors - The Invisible Enemy

Overlooking errors from Rollback() can spell disaster as they can indicate issues with resource cleanup or potentially even data corruption. Handle them!

explain-codes / Sql / Database/sql Tx - detecting Commit or Rollback

Linked

How to rollback or commit a transaction in SQL Server



Best way to work with transactions in MS SQL Server Management Studio



Sql Server - transactions roll back on error?



Creating a stored procedure if it does not already exist



How can I write a `try`/`except` block that catches all exceptions?

