GitHub user jamesfredley added a comment to the discussion: Grails 7:
Transaction/locking behaviour changes?
## 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:
```groovy
@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](https://github.com/spring-projects/spring-framework/issues/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.
```yaml
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.
```groovy
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()`:
```groovy
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:
```yaml
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.
GitHub link:
https://github.com/apache/grails-core/discussions/15515#discussioncomment-16201567
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
