Grails 7: Transaction/locking behaviour changes?

[nsk-dvlpr]

Hi,

After upgrading our application to Grails 7 one of our endpoints became waaay slower under significant traffic.

After doing some investigation we noticed that there's a number of transactions stuck waiting for a row lock. Ultimately they are taking up all the Hikari connections and making the app unusable.

The code, that is causing the issue looks like below:

// called concurrently by multiple consumers
@grails.gorm.transactions.Transactional
void handle(String message) {
    // omitted
    synchroniser.update(caseId, requestId)
}

Status update(String caseId, String requestId) {
    Counter.withNewSession {
        Counter.withNewTransaction {
            Counter.executeUpdate(
                "update Counter set value = value - 1 where caseId = :caseId and requestId = :requestId", [caseId: caseId, requestId: requestId]
            )
        }
    }
}

@EqualsAndHashCode(includes = ['caseId', 'requestId'])
class Counter implements Serializable {

    String caseId
    String requestId
    Integer value

    static mapping = {
        cache false
        version false
        id composite: ['caseId', 'requestId']
    }
}

ENGINE  OBJECT_NAME INDEX_NAME  LOCK_TYPE   LOCK_MODE       LOCK_STATUS LOCK_DATA
INNODB  counter     PRIMARY     RECORD      X,REC_NOT_GAP   GRANTED     '1000220484', '9e96aed1-1947-40ed-a3eb-13428f6e0c8f'
INNODB  counter     PRIMARY     RECORD      X,REC_NOT_GAP   WAITING     '1000220484', '9e96aed1-1947-40ed-a3eb-13428f6e0c8f'
INNODB  counter     PRIMARY     RECORD      X,REC_NOT_GAP   WAITING     '1000220484', '9e96aed1-1947-40ed-a3eb-13428f6e0c8f'

• That single update on Grails 6 (under the same traffic) is taking ~500us and on Grails 7 ~800ms
• Order of calls (based on the Hibernate transaction/session/sql logs) seems to be exactly the same
• We are still using Hibernate 5
• Indy is disabled

Are you aware of any changes around the transaction handling/locking in Grails 7 that could have caused the issue?

Many thanks

[jdaugherty]

Hi @nsk-dvlpr,

I'm not aware of any changes in this area, but we did switch to hikari by default. You do not have to use hikari and can continue to use tomcat-jdbc instead. Have you tried using that instead to see if that fixes the performance problem?

Which specific Grails 7.x version are you using? We released 7.0.9 on Monday with a critical fix that caused jdbc connection properties to not be set on the Hikari configuration. Are you setting any properties that could explain this performance difference?

[nsk-dvlpr]

Hi @jdaugherty

Thanks for your quick reply.

• We are already using HikariCP on the Grails 6.x version and the config between the upgrades is the same (including maximumPoolSize). We only bumped the HikariCP version from 4.0.3 to 6.x as part of the upgrade.
• We can still see the issue on 7.0.9 (having your latest fix)

It doesn't seem that the problem is caused by the Hikari itself. Seems that the connections are not released in time by the transactions holding them up waiting for a row lock, so likely something has changed in that area.

I remember seeing a similar bug during the upgrade from Grails 3.x to 4.x when deleteAll() started locking the whole table instead of a single row and we had to change it to delete each entry by its PK, but doesn't seem to be the case here.

Is there anything else you can think of that has changed between 6.x and 7.x that we can investigate?

Thanks

[jdaugherty]

7.0.8 probably had more bug fixes related to queries & Grails Data Mapping than any other release in the past 7+ years. Did this issue exist with 7.0.7? We could narrow down based on that if so.

Otherwise, I know of no intentional changes. With that said, Groovy 4 changed the compiled behaviors in several places, and early on in the 7.x release process I had to work upstream to fix numerous issues. It's possible it could be related to that.

Under the covers all of the upstream libraries changed, so it could be an issue with Hikari or Spring Boot itself. I noticed the readme in the Hikari was changed in October 2024 to even add warnings about not recovering (brettwooldridge/HikariCP@604d7d5)

If you can provide a sample application that reproduces this behavior, I can take a closer look to see if there's anything obvious. It would help if you provided a Grails 6 & a Grails 7 one.

[jamesfredley]

Problem

After upgrading from Grails 6 to Grails 7, a concurrent executeUpdate pattern using
withNewSession + withNewTransaction inside an @Transactional method went from
~500us to ~800ms per operation. InnoDB shows multiple transactions stuck waiting for
the same row lock, eventually exhausting all HikariCP connections.
The code:

@grails.gorm.transactions.Transactional
void handle(String message) {
    synchroniser.update(caseId, requestId)
}
Status update(String caseId, String requestId) {
    Counter.withNewSession {
        Counter.withNewTransaction {
            Counter.executeUpdate(
                "update Counter set value = value - 1 where caseId = :caseId and requestId = :requestId",
                [caseId: caseId, requestId: requestId]
            )
        }
    }
}

User reports: same config, same HikariCP maximumPoolSize, Hibernate 5, indy disabled.
HikariCP version bumped from 4.0.3 to 6.x as part of the upgrade.

Diagnosis: Connection Pool Starvation from Nested Physical Transactions

The two-connection-per-thread pattern

Thread 1: @Transactional handle()     -> holds Connection A (outer tx)
           +-- withNewSession          -> needs Connection B from pool
               +-- withNewTransaction  -> PROPAGATION_REQUIRES_NEW on Connection B
                   +-- executeUpdate   -> acquires row lock, commits, releases B
Thread 2: same pattern                -> holds Connection C, needs Connection D
Thread 3: same pattern                -> holds Connection E, needs Connection F
...

Each thread requires two physical JDBC connections simultaneously - one for the outer
@Transactional and one for the withNewSession/withNewTransaction block. With N
concurrent threads, 2N pool connections are needed.
When the pool cannot provide the second connection, threads block waiting. But the
connections they already hold (for the outer transaction) will not be returned until
the inner work completes. This is a classic connection pool deadlock.

Why it worked in Grails 6 but breaks in Grails 7

This pattern was always fragile, but three things changed simultaneously:
1. HikariCP 4.0.3 to 6.x connection acquisition behavior
HikariCP 6 introduced stricter connection validation, autoCommit state reset on
borrow/return, and different internal queuing under contention. Under high concurrency,
connection acquisition takes measurably longer (microseconds to low milliseconds). This
widens the window where both connections are held simultaneously, increasing the
probability of pool exhaustion from near-zero to significant.
2. Spring 6 TransactionSynchronizationManager refactoring
Spring 6 refactored thread-local resource binding for virtual thread (Loom) readiness.
The suspend/resume cycle when PROPAGATION_REQUIRES_NEW switches from the outer to
the inner transaction now has more overhead. Resource unbinding/rebinding is more strictly
guarded, adding latency at the transaction boundary.
Reference: Spring Framework #26250 -
documents the connection pool deadlock risk with REQUIRES_NEW explicitly.
3. The 7.0.9 datasource properties fix
Before 7.0.9, JDBC connection properties were not being set on HikariCP correctly. After
the fix, properties ARE applied - meaning HikariCP now actually enforces connectionTimeout,
validation queries, etc. that may have been silently ignored before. If connectionTimeout
defaults to 30 seconds (HikariCP default), a thread waiting for a second connection will
block for up to 30 seconds before failing, holding its first connection the entire time.

Evidence

The InnoDB lock output:

GRANTED     '1000220484', '9e96aed1-...'  <- One thread holds the row lock
WAITING     '1000220484', '9e96aed1-...'  <- Second thread waiting for same row
WAITING     '1000220484', '9e96aed1-...'  <- Third thread waiting for same row

The ~800ms timing matches connection pool wait time + database operation, not the database
operation alone. The 500us in Grails 6 was the raw database operation time. The 800ms in
Grails 7 is pool contention + database time.

How withNewSession/withNewTransaction works internally

• withNewSession (in GormStaticApi.groovy) calls datastore.connect() to create a
  new Hibernate Session and binds it to the thread via DatastoreUtils.bindNewSession().
  This new session acquires a separate JDBC connection from the pool.
• withNewTransaction sets propagationBehavior to PROPAGATION_REQUIRES_NEW and
  delegates to GrailsTransactionTemplate. Spring's transaction manager suspends the
  outer transaction and starts a new one on the new session's connection.
• The @Transactional AST transform defaults to PROPAGATION_REQUIRED (not
  REQUIRES_NEW), wrapping the method in GrailsTransactionTemplate.execute().
  The combination means two physical connections are held for the duration of the outer
  method - one for the outer @Transactional session, one for the withNewSession block.

Recommended Fixes

Option 1: Increase pool size (immediate fix)

Set maximumPoolSize to at least 2 * max_concurrent_threads. This directly addresses
the two-connection-per-thread requirement.

dataSource:
    pooled: true
    properties:
        maximumPoolSize: 40   # if you expect up to 20 concurrent handle() calls

Option 2: Remove withNewSession (preferred fix)

If the goal is just a new transaction, withNewTransaction alone creates
PROPAGATION_REQUIRES_NEW which suspends the outer transaction. The withNewSession
forces a second physical connection. Without it, the new transaction may reuse the
connection from the suspended outer transaction.

Status update(String caseId, String requestId) {
    Counter.withNewTransaction {
        Counter.executeUpdate(
            "update Counter set value = value - 1 where caseId = :caseId and requestId = :requestId",
            [caseId: caseId, requestId: requestId]
        )
    }
}

This reduces the connection requirement to one per thread.

Option 3: Remove the outer @transactional (simplest fix)

If handle() does not need its own transaction (the only database work is the inner
executeUpdate which has its own transaction), remove @Transactional from handle():

void handle(String message) {
    // omitted
    synchroniser.update(caseId, requestId)
}

This eliminates the two-connection requirement entirely.

Option 4: Set explicit connectionTimeout (diagnostic aid)

Set a low connectionTimeout on HikariCP to fail fast instead of blocking for 30
seconds when the pool is exhausted:

dataSource:
    properties:
        connectionTimeout: 5000   # 5 seconds instead of 30 second default

This makes the issue visible immediately as SQLTransientConnectionException rather than
causing cascading timeouts that mask the root cause.

Broader Implication

Any Grails application using withNewSession + withNewTransaction inside an
@Transactional method is vulnerable to this pattern after upgrading to Grails 7. The
combination of HikariCP 6's stricter connection handling and Spring 6's refactored
transaction synchronization reduces the margin for connection pool sizing. Applications
should audit for this nested-connection pattern during upgrade.

[jdaugherty]

@nsk-dvlpr I think @jamesfredley & my answers should cover this. Can you let us know if we can close this discussion / mark it as solved?

[nsk-dvlpr]

Hi @jdaugherty,

Sorry for taking so long to reply.

I couldn't reproduce the issue locally, but seems that connection issues were a symptom, not a cause and the slowness was caused by our resources (CPU) quickly being maxed out (>95%) after the upgrade.

I did run a simple perf test to compare the results and below you can find a comparison of Grails 6 and Grails 7 CPU usage under the same load:

Grails 6:

Grails 7.0.9:

I'm still trying to figure out what exactly is taking those resources and if it's generic or specific to our application.

Have you encountered a similar issue?