Use Hibernate and SQLAlchemy with CockroachDB (2026)

CockroachDB has a fascinating approach to ACID transactions that often trips up ORMs designed for traditional databases.

Let’s see how Hibernate and SQLAlchemy, two popular Object-Relational Mappers, interact with CockroachDB.

Hibernate with CockroachDB

Hibernate’s default transaction handling relies heavily on acquiring locks and holding them for the duration of a transaction, which can lead to deadlocks in a distributed database like CockroachDB where transactions are designed to be short-lived and conflict resolution happens at commit time.

Here’s a typical Hibernate configuration for CockroachDB, focusing on the critical hibernate.connection.url and hibernate.dialect:

hibernate.connection.driver_class=org.postgresql.Driver
hibernate.connection.url=jdbc:postgresql://<your-cockroachdb-host>:26257/<your-database>?sslmode=verify-full&sslrootcert=<path-to-ca.crt>
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.jdbc.batch_size=100
hibernate.show_sql=true
hibernate.format_sql=true

# Crucial for CockroachDB: optimistic locking for concurrent transactions
hibernate.optimistic_lock_style=dirty
hibernate.jdbc.use_scrollable_resultset=false
hibernate.jdbc.fetch_size=100

# Disable deprecated features and tune for CockroachDB's distributed nature
hibernate.auto.create.schema=false
hibernate.flush.mode=auto
hibernate.jdbc.use_native_stats=false
hibernate.jdbc.use_get_generated_keys=false

The PostgreSQLDialect is used because CockroachDB is wire-compatible with PostgreSQL. The sslmode=verify-full and sslrootcert are essential for secure connections to CockroachDB.

The Problem: When Hibernate attempts to update an entity, it often uses a SELECT followed by an UPDATE. In a high-concurrency scenario with CockroachDB, this can lead to a situation where two transactions read the same data, then both try to update it. CockroachDB’s transactional guarantees mean that only one can succeed, and the other will likely fail with a RETRY_WRITE_TOO_OLD or a similar error.

The Fix: The key is to ensure Hibernate uses optimistic locking and minimizes transaction duration.

Enable Optimistic Locking: Add @Version annotation to your entity classes. Hibernate will then add a version column to your tables and use it to detect conflicts.

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @Version
    private int version; // This is crucial for optimistic locking

    // Getters and setters
}

Configure Hibernate for Optimistic Locking: Set hibernate.optimistic_lock_style=dirty in your persistence.xml or properties. This tells Hibernate to use the version column for locking.
Shorten Transaction Times: Avoid long-running transactions. Keep your @Transactional methods as focused as possible, performing only the necessary database operations.

Retry Logic: Implement retry logic in your application. If you encounter a org.hibernate.StaleObjectStateException (which indicates a version mismatch due to a concurrent update), you should catch this exception and re-attempt the transaction.

@Transactional
public void updateProduct(Long productId, String newName) {
    try {
        Product product = entityManager.find(Product.class, productId);
        if (product != null) {
            product.setName(newName);
            entityManager.merge(product); // Hibernate checks version here
        }
    } catch (StaleObjectStateException e) {
        // Log the conflict and retry the operation
        System.out.println("Transaction conflict, retrying...");
        // Implement retry mechanism (e.g., exponential backoff)
        updateProduct(productId, newName); // Recursive call for simplicity, use a proper retry loop in production
    }
}

Disable Unnecessary Features: Set hibernate.jdbc.use_scrollable_resultset=false and hibernate.jdbc.fetch_size=100 (or a small value) to prevent Hibernate from fetching large result sets or using cursors that might hold locks longer than necessary.

The next error you’ll likely hit is a TransactionRolledbackException if your retry logic isn’t robust enough to handle persistent contention.

SQLAlchemy with CockroachDB

SQLAlchemy’s flexibility is a double-edged sword with CockroachDB. While it’s highly configurable, its default behaviors can also lead to transaction contention.

Here’s a basic SQLAlchemy setup for CockroachDB:

from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# Connection string for CockroachDB
# Ensure you have the PostgreSQL driver installed (e.g., psycopg2)
# SSL is highly recommended for production
db_url = "postgresql://<user>:<password>@<your-cockroachdb-host>:26257/<your-database>?sslmode=verify-full&sslrootcert=<path-to-ca.crt>"

engine = create_engine(db_url, echo=True) # echo=True for debugging SQL

Base = declarative_base()

class Product(Base):
    __tablename__ = 'products'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    version = Column(Integer, default=1) # For optimistic locking

    def __repr__(self):
        return f"<Product(id={self.id}, name='{self.name}', version={self.version})>"

# Create tables if they don't exist (for development/testing)
# Base.metadata.create_all(engine)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Example of using the session
db = SessionLocal()
try:
    # ... database operations ...
    db.commit()
except Exception as e:
    db.rollback()
    raise e
finally:
    db.close()

The Problem: Similar to Hibernate, SQLAlchemy’s default transaction management can lead to issues. If you perform a read operation and then a write operation within the same transaction, and another transaction modifies the data between your read and write, CockroachDB will detect this conflict. SQLAlchemy, by default, might not have explicit retry logic for these specific CockroachDB errors.

The Fix: The core principles for SQLAlchemy are also optimistic locking and short transactions.

Implement Optimistic Locking: Add a version column to your models, as shown in the Product example above.
Use session.commit() Frequently: Keep your database transactions as short as possible. Avoid performing long-running application logic between session.add() or session.query() and session.commit().

Catch and Retry Specific CockroachDB Errors: CockroachDB’s error codes are key here. You’ll often see errors related to transaction retries. The specific error message might vary, but you’re looking for something indicating a write conflict.

A common pattern is to use a decorator or a function to wrap your session operations with retry logic.

from sqlalchemy.exc import OperationalError
from cockroachdb.sqlalchemy import RETRY_WRITE_TOO_OLD # Or the appropriate exception from your driver

MAX_RETRIES = 5
BACKOFF_FACTOR = 0.5 # Seconds

def retry_on_conflict(session, func, *args, **kwargs):
    for attempt in range(MAX_RETRIES):
        try:
            return func(session, *args, **kwargs)
        except RETRY_WRITE_TOO_OLD: # Catch the specific CockroachDB retry error
            print(f"Conflict detected, retrying operation (attempt {attempt + 1}/{MAX_RETRIES})...")
            import time
            time.sleep(BACKOFF_FACTOR * (2 ** attempt)) # Exponential backoff
        except Exception as e:
            # Handle other exceptions or re-raise
            raise e
    raise Exception(f"Operation failed after {MAX_RETRIES} retries due to transaction conflicts.")

def update_product_name(session, product_id, new_name):
    product = session.query(Product).filter(Product.id == product_id).first()
    if product:
        product.name = new_name
        # SQLAlchemy's versioning might need explicit handling
        # For simple version increments, you might do:
        # product.version += 1
        # Or rely on ORM features if available and configured.
        session.add(product)
        session.flush() # Flush to check version before commit
        # If flush() raises StaleObjectError or similar, it's a conflict.
        # For explicit version checks:
        # current_version = product.version
        # updated_product = session.query(Product).filter(Product.id == product_id, Product.version == current_version).first()
        # if updated_product is None:
        #     raise RETRY_WRITE_TOO_OLD("Version mismatch detected")
        # updated_product.name = new_name
        # updated_product.version += 1
        # session.add(updated_product)
    return product

# Usage:
db = SessionLocal()
try:
    # Wrap the actual update logic
    retry_on_conflict(db, update_product_name, 123, "New Product Name")
    db.commit()
except Exception as e:
    db.rollback()
    print(f"Operation failed: {e}")
finally:
    db.close()

Disable Autocommit/Autoflush (Carefully): While autocommit=False and autoflush=False are common defaults, ensure you have explicit control over when commit() is called. autoflush=True (the default for sessionmaker) can sometimes cause unexpected flushes and potential conflicts if not managed carefully.
Use session.flush() for Early Conflict Detection: Calling session.flush() before session.commit() can trigger the database to check for conflicts earlier, potentially allowing your retry logic to catch them before a full commit attempt.
Be Mindful of SELECT within Transactions: If you’re performing a SELECT and then a UPDATE on the same rows in a single transaction, and another transaction modifies those rows, you’ll hit a conflict. CockroachDB’s transaction retry mechanism is designed to handle this by aborting one of the transactions. Your application needs to be prepared to retry.

The next hurdle you’ll encounter is managing complex transaction lifecycles where multiple independent operations need to be atomic, requiring careful orchestration of retries and possibly manual transaction management to ensure data consistency.