0
# Transaction Management
1

2
Comprehensive transaction support with automatic resource management, rollback capabilities, and connection caching for improved performance.
3

4
## Capabilities
5

6
### Transaction Control
7

8
Execute operations within database transactions with automatic rollback on exceptions.
9

10
```java { .api }
11
// Execute closure within transaction
12
public Object withTransaction(Closure closure) throws SQLException;
13

14
// Manual transaction control
15
public void commit() throws SQLException;
16
public void rollback() throws SQLException;
17
```
18

19
**Example**:
20
```groovy
21
// Automatic transaction management
22
sql.withTransaction {
23
    sql.executeUpdate('INSERT INTO accounts (id, balance) VALUES (1, 1000)')
24
    sql.executeUpdate('INSERT INTO accounts (id, balance) VALUES (2, 500)')
25
    
26
    // Transfer money between accounts
27
    sql.executeUpdate('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, 1])
28
    sql.executeUpdate('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, 2])
29
    
30
    // If any operation fails, entire transaction is rolled back
31
}
32

33
// Manual transaction control
34
try {
35
    sql.executeUpdate('INSERT INTO orders (customer_id, total) VALUES (?, ?)', [customerId, total])
36
    def orderId = sql.firstRow('SELECT LAST_INSERT_ID() as id').id
37
    
38
    orderItems.each { item ->
39
        sql.executeUpdate('INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)',
40
                         [orderId, item.productId, item.quantity])
41
    }
42
    
43
    sql.commit()
44
} catch (Exception e) {
45
    sql.rollback()
46
    throw e
47
}
48
```
49

50
### Connection Caching
51

52
Cache database connections across multiple operations for improved performance.
53

54
```java { .api }
55
// Cache connection during closure execution
56
public Object cacheConnection(Closure closure) throws SQLException;
57

58
// Access cached connection
59
public Connection getConnection() throws SQLException;
60
public DataSource getDataSource();
61
```
62

63
**Example**:
64
```groovy
65
// Cache connection for multiple operations
66
sql.cacheConnection {
67
    // All operations in this block reuse the same connection
68
    def users = sql.rows('SELECT * FROM users')
69
    users.each { user ->
70
        sql.executeUpdate('UPDATE user_stats SET last_seen = NOW() WHERE user_id = ?', [user.id])
71
    }
72
    
73
    // Update summary statistics
74
    sql.executeUpdate('UPDATE system_stats SET total_users = (SELECT COUNT(*) FROM users)')
75
}
76

77
// Without caching, each operation would get a new connection from pool
78
```
79

80
### ResultSet Configuration
81

82
Configure ResultSet properties for scrolling, concurrency, and holdability.
83

84
```java { .api }
85
// ResultSet type (scrollability)
86
public int getResultSetType();
87
public void setResultSetType(int resultSetType);
88

89
// ResultSet concurrency (updatability)  
90
public int getResultSetConcurrency();
91
public void setResultSetConcurrency(int resultSetConcurrency);
92

93
// ResultSet holdability (transaction behavior)
94
public int getResultSetHoldability();
95
public void setResultSetHoldability(int resultSetHoldability);
96
```
97

98
**Example**:
99
```groovy
100
import java.sql.ResultSet
101

102
// Configure for scrollable, updatable ResultSet
103
sql.resultSetType = ResultSet.TYPE_SCROLL_INSENSITIVE
104
sql.resultSetConcurrency = ResultSet.CONCUR_UPDATABLE
105

106
sql.query('SELECT * FROM users') { rs ->
107
    // Can scroll backwards and forwards
108
    rs.last()
109
    println "Total rows: ${rs.row}"
110
    
111
    rs.first()
112
    while (rs.next()) {
113
        if (rs.getString('email') == null) {
114
            // Can update directly in ResultSet
115
            rs.updateString('email', 'no-email@example.com')
116
            rs.updateRow()
117
        }
118
    }
119
}
120

121
// Configure holdability
122
sql.resultSetHoldability = ResultSet.HOLD_CURSORS_OVER_COMMIT
123
```
124

125
### Statement Caching
126

127
Cache prepared statements for improved performance with repeated queries.
128

129
```java { .api }
130
// Statement caching configuration
131
public boolean isCacheStatements();
132
public void setCacheStatements(boolean cacheStatements);
133

134
// Execute with cached statements
135
public Object cacheStatements(Closure closure) throws SQLException;
136

137
// Statement configuration hooks
138
public Object withStatement(Closure configureStatement) throws SQLException;
139
public Object withCleanupStatement(Closure cleanupStatement) throws SQLException;
140
```
141

142
**Example**:
143
```groovy
144
// Enable statement caching
145
sql.cacheStatements = true
146

147
// Cached statements will be reused for identical SQL
148
def userIds = [1, 2, 3, 4, 5]
149
userIds.each { id ->
150
    // Same PreparedStatement reused for each call
151
    def user = sql.firstRow('SELECT * FROM users WHERE id = ?', [id])
152
    println user.name
153
}
154

155
// Temporary statement caching
156
sql.cacheStatements {
157
    // Statements cached only within this block
158
    100.times { i ->
159
        sql.executeUpdate('INSERT INTO temp_data (value) VALUES (?)', [i])
160
    }
161
}
162

163
// Configure statement properties
164
sql.withStatement { stmt ->
165
    stmt.queryTimeout = 30
166
    stmt.fetchSize = 1000
167
    stmt.maxRows = 10000
168
} {
169
    def results = sql.rows('SELECT * FROM large_table')
170
    // Statement configured for this operation
171
}
172
```
173

174
### Named Query Caching
175

176
Cache parsed named queries for improved performance with parameterized SQL.
177

178
```java { .api }
179
// Named query configuration
180
public boolean isEnableNamedQueries();
181
public void setEnableNamedQueries(boolean enableNamedQueries);
182
public boolean isCacheNamedQueries();
183
public void setCacheNamedQueries(boolean cacheNamedQueries);
184
```
185

186
**Example**:
187
```groovy
188
// Enable named query support and caching
189
sql.enableNamedQueries = true
190
sql.cacheNamedQueries = true
191

192
// Named parameters are parsed and cached
193
def params = [minAge: 25, dept: 'Engineering']
194
sql.eachRow('SELECT * FROM users WHERE age >= :minAge AND department = :dept', params) { row ->
195
    println "${row.name} - ${row.department}"
196
}
197

198
// Query parsing is cached for subsequent calls
199
def otherParams = [minAge: 30, dept: 'Sales']  
200
sql.eachRow('SELECT * FROM users WHERE age >= :minAge AND department = :dept', otherParams) { row ->
201
    println "${row.name} - ${row.department}"
202
}
203
```
204

205
### Connection Pool Integration
206

207
Work effectively with connection pools and DataSource configurations.
208

209
**Example**:
210
```groovy
211
import org.apache.commons.dbcp2.BasicDataSource
212

213
// Configure connection pool
214
def dataSource = new BasicDataSource()
215
dataSource.url = 'jdbc:mysql://localhost:3306/mydb'
216
dataSource.username = 'user'
217
dataSource.password = 'pass'
218
dataSource.driverClassName = 'com.mysql.cj.jdbc.Driver'
219
dataSource.initialSize = 5
220
dataSource.maxTotal = 20
221
dataSource.maxIdle = 10
222
dataSource.minIdle = 5
223

224
def sql = new Sql(dataSource)
225

226
// Use within transactions to ensure connection reuse
227
sql.withTransaction {
228
    // All operations use same connection from pool
229
    def orders = sql.rows('SELECT * FROM orders WHERE date = CURDATE()')
230
    orders.each { order ->
231
        sql.executeUpdate('UPDATE orders SET status = ? WHERE id = ?', ['processed', order.id])
232
    }
233
}
234

235
// Connection automatically returned to pool when transaction completes
236
```
237

238
### Isolation Levels
239

240
Work with transaction isolation levels for concurrent access control.
241

242
**Example**:
243
```groovy
244
import java.sql.Connection
245

246
// Get underlying connection to set isolation level
247
sql.cacheConnection {
248
    def conn = sql.connection
249
    def originalLevel = conn.transactionIsolation
250
    
251
    try {
252
        // Set stricter isolation for critical operations
253
        conn.transactionIsolation = Connection.TRANSACTION_SERIALIZABLE
254
        
255
        sql.withTransaction {
256
            // Critical financial transaction
257
            def balance = sql.firstRow('SELECT balance FROM accounts WHERE id = ?', [accountId]).balance
258
            if (balance >= amount) {
259
                sql.executeUpdate('UPDATE accounts SET balance = balance - ? WHERE id = ?', 
260
                                 [amount, accountId])
261
            } else {
262
                throw new RuntimeException('Insufficient funds')
263
            }
264
        }
265
    } finally {
266
        // Restore original isolation level
267
        conn.transactionIsolation = originalLevel
268
    }
269
}
270
```