cockroachdb-developer-patterns

CockroachDB Developer Patterns — JPA, Spring, JavaEE & Set-Based SQL

Framework-specific patterns from JPA Best Practices for CockroachDB, Transaction Retries Series, Set-Based Operations, and cockroachdb-best-practices-demo.

---

1. JPA/Hibernate Identity Generators

❌ NEVER: Use GenerationType.IDENTITY with CockroachDB

// BAD: IDENTITY disables Hibernate JDBC batching entirely.
// Hibernate must execute each INSERT individually to retrieve the generated ID.
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

✅ ALWAYS: Use UUID with GenerationType.AUTO

// GOOD: UUIDv4 generated in JVM — no DB round-trip, batching works, writes distributed
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(columnDefinition = "UUID")
private UUID id;

✅ Alternative: Explicit UUID generator for clarity

@Id
@GeneratedValue
@GenericGenerator(name = "uuid", strategy = "org.hibernate.id.UUIDGenerator")
@Column(columnDefinition = "UUID")
private UUID id;

If numeric PKs are unavoidable:

// Use unordered_unique_rowid() from CockroachDB — randomly distributed, not sequential
// Or use a custom generator that batches Hi-Lo allocation from a sequence in the JVM
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "order_seq")
@SequenceGenerator(name = "order_seq", allocationSize = 50)  // batch 50 IDs
private Long id;
// WARNING: Still creates some hot-spotting. UUID is strongly preferred.

---

2. Spring Boot Transaction Retry with AOP

The standard @Transactional annotation does NOT retry on 40001. Add an AOP aspect to intercept and retry.

✅ CORRECT: Spring AOP retry interceptor

@Aspect
@Order(Ordered.HIGHEST_PRECEDENCE)  // Must run BEFORE @Transactional proxy
@Component
public class CockroachRetryAspect {
    private static final int MAX_RETRIES = 5;

    @Around("@annotation(transactional)")
    public Object retryOnSerializationFailure(
            ProceedingJoinPoint pjp, Transactional transactional) throws Throwable {
        for (int attempt = 1; attempt <= MAX_RETRIES; attempt++) {
            try {
                return pjp.proceed();
            } catch (TransientDataAccessException ex) {
                Throwable root = ex.getMostSpecificCause();
                if (root instanceof SQLException sql
                        && "40001".equals(sql.getSQLState())
                        && attempt < MAX_RETRIES) {
                    long backoff = Math.min(
                        (long)(Math.pow(2, attempt) * 200 + Math.random() * 1000), 15000);
                    Thread.sleep(backoff);
                    continue;
                }
                throw ex;
            }
        }
        throw new ConcurrencyFailureException("Max retries exceeded for CockroachDB txn");
    }
}