0
# Transaction Management
1

2
This document covers transaction control, isolation levels, savepoints, and automatic savepoint management in the PostgreSQL JDBC driver.
3

4
## Capabilities
5

6
### Basic Transaction Control
7

8
Standard JDBC transaction management methods.
9

10
```java { .api }
11
import java.sql.Connection;
12
import java.sql.SQLException;
13

14
/**
15
 * Transaction control methods on Connection interface.
16
 */
17
public interface Connection {
18
    /**
19
     * Sets auto-commit mode.
20
     * When true, each statement is automatically committed.
21
     * When false, statements must be explicitly committed or rolled back.
22
     *
23
     * @param autoCommit true for auto-commit, false for manual transactions
24
     * @throws SQLException if mode cannot be set
25
     */
26
    void setAutoCommit(boolean autoCommit) throws SQLException;
27

28
    /**
29
     * Gets the current auto-commit mode.
30
     *
31
     * @return true if auto-commit is enabled
32
     * @throws SQLException if check fails
33
     */
34
    boolean getAutoCommit() throws SQLException;
35

36
    /**
37
     * Commits the current transaction.
38
     * Makes all changes since last commit/rollback permanent.
39
     *
40
     * @throws SQLException if commit fails
41
     */
42
    void commit() throws SQLException;
43

44
    /**
45
     * Rolls back the current transaction.
46
     * Undoes all changes since last commit/rollback.
47
     *
48
     * @throws SQLException if rollback fails
49
     */
50
    void rollback() throws SQLException;
51

52
    /**
53
     * Closes the connection.
54
     * If auto-commit is false and transaction is active, behavior depends
55
     * on implementation (PostgreSQL driver rolls back by default).
56
     *
57
     * @throws SQLException if close fails
58
     */
59
    void close() throws SQLException;
60
}
61
```
62

63
**Usage Examples:**
64

65
```java
66
import java.sql.Connection;
67
import java.sql.PreparedStatement;
68
import java.sql.SQLException;
69

70
// Example 1: Basic transaction
71
public class BasicTransactionExample {
72
    public static void transferFunds(Connection conn, int fromAccount,
73
                                     int toAccount, double amount)
74
            throws SQLException {
75
        // Disable auto-commit to start transaction
76
        conn.setAutoCommit(false);
77

78
        try {
79
            // Debit from account
80
            try (PreparedStatement pstmt = conn.prepareStatement(
81
                    "UPDATE accounts SET balance = balance - ? WHERE id = ?")) {
82
                pstmt.setDouble(1, amount);
83
                pstmt.setInt(2, fromAccount);
84
                pstmt.executeUpdate();
85
            }
86

87
            // Credit to account
88
            try (PreparedStatement pstmt = conn.prepareStatement(
89
                    "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
90
                pstmt.setDouble(1, amount);
91
                pstmt.setInt(2, toAccount);
92
                pstmt.executeUpdate();
93
            }
94

95
            // Commit transaction
96
            conn.commit();
97
            System.out.println("Transfer completed");
98

99
        } catch (SQLException e) {
100
            // Roll back on error
101
            conn.rollback();
102
            System.err.println("Transfer failed, rolled back");
103
            throw e;
104
        } finally {
105
            // Restore auto-commit
106
            conn.setAutoCommit(true);
107
        }
108
    }
109
}
110

111
// Example 2: Transaction with try-with-resources
112
public class TryWithResourcesTransaction {
113
    public static void executeInTransaction(Connection conn) throws SQLException {
114
        boolean originalAutoCommit = conn.getAutoCommit();
115

116
        try {
117
            conn.setAutoCommit(false);
118

119
            // Perform database operations
120
            try (PreparedStatement pstmt = conn.prepareStatement(
121
                    "INSERT INTO audit_log (action, timestamp) VALUES (?, ?)")) {
122
                pstmt.setString(1, "USER_LOGIN");
123
                pstmt.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis()));
124
                pstmt.executeUpdate();
125
            }
126

127
            try (PreparedStatement pstmt = conn.prepareStatement(
128
                    "UPDATE users SET last_login = ? WHERE id = ?")) {
129
                pstmt.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis()));
130
                pstmt.setInt(2, 123);
131
                pstmt.executeUpdate();
132
            }
133

134
            conn.commit();
135

136
        } catch (SQLException e) {
137
            conn.rollback();
138
            throw e;
139
        } finally {
140
            conn.setAutoCommit(originalAutoCommit);
141
        }
142
    }
143
}
144
```
145

146
### Transaction Isolation Levels
147

148
PostgreSQL supports standard SQL isolation levels.
149

150
```java { .api }
151
import java.sql.Connection;
152
import java.sql.SQLException;
153

154
/**
155
 * Transaction isolation level constants and methods.
156
 */
157
public interface Connection {
158
    // Isolation level constants
159
    int TRANSACTION_NONE = 0;
160
    int TRANSACTION_READ_UNCOMMITTED = 1; // Treated as READ COMMITTED in PostgreSQL
161
    int TRANSACTION_READ_COMMITTED = 2;   // PostgreSQL default
162
    int TRANSACTION_REPEATABLE_READ = 4;
163
    int TRANSACTION_SERIALIZABLE = 8;
164

165
    /**
166
     * Sets the transaction isolation level.
167
     * Must be called before starting a transaction.
168
     *
169
     * @param level Isolation level constant
170
     * @throws SQLException if level cannot be set
171
     */
172
    void setTransactionIsolation(int level) throws SQLException;
173

174
    /**
175
     * Gets the current transaction isolation level.
176
     *
177
     * @return Current isolation level
178
     * @throws SQLException if level cannot be retrieved
179
     */
180
    int getTransactionIsolation() throws SQLException;
181
}
182
```
183

184
**Isolation Level Behavior:**
185

186
| Level | Dirty Read | Non-Repeatable Read | Phantom Read | PostgreSQL Implementation |
187
|-------|------------|---------------------|--------------|--------------------------|
188
| READ UNCOMMITTED | Prevented | Possible | Possible | Same as READ COMMITTED |
189
| READ COMMITTED | Prevented | Possible | Possible | Default level |
190
| REPEATABLE READ | Prevented | Prevented | Prevented | Snapshot isolation |
191
| SERIALIZABLE | Prevented | Prevented | Prevented | True serializability |
192
193
**Usage Examples:**
194

195
```java
196
import java.sql.Connection;
197
import java.sql.SQLException;
198

199
// Example 1: Set isolation level
200
public class IsolationLevelExample {
201
    public static void executeWithIsolation(Connection conn) throws SQLException {
202
        // Save current level
203
        int originalLevel = conn.getTransactionIsolation();
204

205
        try {
206
            // Set to REPEATABLE READ for consistent reads
207
            conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
208
            conn.setAutoCommit(false);
209

210
            // Execute queries - will see consistent snapshot
211
            // of database as of transaction start
212

213
            conn.commit();
214

215
        } catch (SQLException e) {
216
            conn.rollback();
217
            throw e;
218
        } finally {
219
            // Restore original level
220
            conn.setTransactionIsolation(originalLevel);
221
            conn.setAutoCommit(true);
222
        }
223
    }
224
}
225

226
// Example 2: SERIALIZABLE for strict consistency
227
public class SerializableExample {
228
    public static void serializableTransaction(Connection conn) throws SQLException {
229
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
230
        conn.setAutoCommit(false);
231

232
        try {
233
            // Perform operations that require serializable isolation
234
            // PostgreSQL will detect serialization conflicts and
235
            // may throw SQLException with SQLSTATE 40001
236

237
            conn.commit();
238

239
        } catch (SQLException e) {
240
            if ("40001".equals(e.getSQLState())) {
241
                // Serialization failure - retry transaction
242
                System.out.println("Serialization conflict, retry needed");
243
            }
244
            conn.rollback();
245
            throw e;
246
        }
247
    }
248
}
249
```
250

251
### Savepoints
252

253
Savepoints allow partial rollback within a transaction.
254

255
```java { .api }
256
import java.sql.Connection;
257
import java.sql.Savepoint;
258
import java.sql.SQLException;
259

260
/**
261
 * Savepoint methods for partial transaction rollback.
262
 */
263
public interface Connection {
264
    /**
265
     * Creates an unnamed savepoint in the current transaction.
266
     *
267
     * @return Savepoint object
268
     * @throws SQLException if savepoint cannot be created
269
     */
270
    Savepoint setSavepoint() throws SQLException;
271

272
    /**
273
     * Creates a named savepoint in the current transaction.
274
     *
275
     * @param name Savepoint name
276
     * @return Savepoint object
277
     * @throws SQLException if savepoint cannot be created
278
     */
279
    Savepoint setSavepoint(String name) throws SQLException;
280

281
    /**
282
     * Rolls back to the specified savepoint.
283
     * Undoes all changes after the savepoint but keeps changes before it.
284
     *
285
     * @param savepoint Savepoint to roll back to
286
     * @throws SQLException if rollback fails
287
     */
288
    void rollback(Savepoint savepoint) throws SQLException;
289

290
    /**
291
     * Releases the savepoint and frees resources.
292
     * The savepoint cannot be used after being released.
293
     *
294
     * @param savepoint Savepoint to release
295
     * @throws SQLException if release fails
296
     */
297
    void releaseSavepoint(Savepoint savepoint) throws SQLException;
298
}
299

300
/**
301
 * Savepoint interface.
302
 */
303
public interface Savepoint {
304
    /**
305
     * Gets the savepoint ID.
306
     * Valid only for unnamed savepoints.
307
     *
308
     * @return Savepoint ID
309
     * @throws SQLException if savepoint is named
310
     */
311
    int getSavepointId() throws SQLException;
312

313
    /**
314
     * Gets the savepoint name.
315
     * Valid only for named savepoints.
316
     *
317
     * @return Savepoint name
318
     * @throws SQLException if savepoint is unnamed
319
     */
320
    String getSavepointName() throws SQLException;
321
}
322
```
323

324
**Usage Examples:**
325

326
```java
327
import java.sql.Connection;
328
import java.sql.PreparedStatement;
329
import java.sql.Savepoint;
330
import java.sql.SQLException;
331

332
// Example 1: Basic savepoint usage
333
public class SavepointExample {
334
    public static void useSavepoints(Connection conn) throws SQLException {
335
        conn.setAutoCommit(false);
336

337
        try {
338
            // First operation
339
            try (PreparedStatement pstmt = conn.prepareStatement(
340
                    "INSERT INTO orders (customer_id, total) VALUES (?, ?)")) {
341
                pstmt.setInt(1, 123);
342
                pstmt.setDouble(2, 100.00);
343
                pstmt.executeUpdate();
344
            }
345

346
            // Create savepoint after successful order
347
            Savepoint savepoint1 = conn.setSavepoint("after_order");
348

349
            try {
350
                // Second operation (might fail)
351
                try (PreparedStatement pstmt = conn.prepareStatement(
352
                        "UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?")) {
353
                    pstmt.setInt(1, 10);
354
                    pstmt.setInt(2, 456);
355
                    int rows = pstmt.executeUpdate();
356

357
                    if (rows == 0) {
358
                        throw new SQLException("Product not found");
359
                    }
360
                }
361

362
            } catch (SQLException e) {
363
                // Roll back only the inventory update
364
                System.out.println("Rolling back to savepoint");
365
                conn.rollback(savepoint1);
366
                // Order insert is still valid
367
            }
368

369
            // Commit the transaction (or whatever is left of it)
370
            conn.commit();
371

372
        } catch (SQLException e) {
373
            // Roll back entire transaction
374
            conn.rollback();
375
            throw e;
376
        } finally {
377
            conn.setAutoCommit(true);
378
        }
379
    }
380
}
381

382
// Example 2: Multiple savepoints
383
public class MultipleSavepointsExample {
384
    public static void complexTransaction(Connection conn) throws SQLException {
385
        conn.setAutoCommit(false);
386

387
        try {
388
            // Step 1: Insert user
389
            Savepoint sp1 = conn.setSavepoint("user_created");
390
            try (PreparedStatement pstmt = conn.prepareStatement(
391
                    "INSERT INTO users (name) VALUES (?)")) {
392
                pstmt.setString(1, "John Doe");
393
                pstmt.executeUpdate();
394
            }
395

396
            // Step 2: Insert profile
397
            Savepoint sp2 = conn.setSavepoint("profile_created");
398
            try {
399
                try (PreparedStatement pstmt = conn.prepareStatement(
400
                        "INSERT INTO profiles (user_id, bio) VALUES (?, ?)")) {
401
                    pstmt.setInt(1, 123);
402
                    pstmt.setString(2, "Bio text");
403
                    pstmt.executeUpdate();
404
                }
405
            } catch (SQLException e) {
406
                conn.rollback(sp2); // Keep user, discard profile
407
            }
408

409
            // Step 3: Insert preferences
410
            try {
411
                try (PreparedStatement pstmt = conn.prepareStatement(
412
                        "INSERT INTO preferences (user_id, theme) VALUES (?, ?)")) {
413
                    pstmt.setInt(1, 123);
414
                    pstmt.setString(2, "dark");
415
                    pstmt.executeUpdate();
416
                }
417
            } catch (SQLException e) {
418
                // This error is minor, continue without preferences
419
                System.out.println("Preferences not saved, continuing");
420
            }
421

422
            conn.commit();
423

424
        } catch (SQLException e) {
425
            conn.rollback();
426
            throw e;
427
        } finally {
428
            conn.setAutoCommit(true);
429
        }
430
    }
431
}
432
```
433

434
### Automatic Savepoints
435

436
PostgreSQL JDBC driver supports automatic savepoint management.
437

438
```java { .api }
439
package org.postgresql.jdbc;
440

441
/**
442
 * Automatic savepoint modes.
443
 * Controls when the driver automatically creates savepoints.
444
 */
445
public enum AutoSave {
446
    /**
447
     * Never create automatic savepoints.
448
     * Default behavior - application must handle errors.
449
     */
450
    NEVER,
451

452
    /**
453
     * Always create a savepoint before each query.
454
     * Allows automatic recovery from query errors without
455
     * aborting the entire transaction.
456
     * Higher overhead but maximum safety.
457
     */
458
    ALWAYS,
459

460
    /**
461
     * Create savepoints conservatively.
462
     * Driver automatically creates savepoints in certain scenarios
463
     * to enable better error recovery.
464
     * Good balance of safety and performance.
465
     */
466
    CONSERVATIVE;
467

468
    /**
469
     * Returns the string value of this enum for use in connection properties.
470
     *
471
     * @return Lowercase string value ("never", "always", or "conservative")
472
     */
473
    public String value();
474

475
    /**
476
     * Parses a string value and returns the corresponding AutoSave enum constant.
477
     *
478
     * @param value String value (case-insensitive)
479
     * @return AutoSave enum constant
480
     * @throws IllegalArgumentException if value is invalid
481
     */
482
    public static AutoSave of(String value);
483
}
484
```
485

486
**Using AutoSave:**
487

488
```java { .api }
489
package org.postgresql;
490

491
import org.postgresql.jdbc.AutoSave;
492
import java.sql.Connection;
493
import java.sql.SQLException;
494

495
/**
496
 * AutoSave configuration on PGConnection.
497
 */
498
public interface PGConnection extends Connection {
499
    /**
500
     * Gets the current autosave mode.
501
     *
502
     * @return Current AutoSave setting
503
     */
504
    AutoSave getAutosave();
505

506
    /**
507
     * Sets the autosave mode.
508
     *
509
     * @param autoSave AutoSave mode to use
510
     */
511
    void setAutosave(AutoSave autoSave);
512
}
513
```
514

515
**Usage Examples:**
516

517
```java
518
import org.postgresql.PGConnection;
519
import org.postgresql.jdbc.AutoSave;
520
import java.sql.Connection;
521
import java.sql.DriverManager;
522
import java.sql.PreparedStatement;
523
import java.sql.SQLException;
524

525
// Example 1: Configure autosave in connection properties
526
public class AutoSaveConnection {
527
    public static Connection getConnectionWithAutoSave() throws SQLException {
528
        String url = "jdbc:postgresql://localhost/mydb?autosave=conservative";
529
        return DriverManager.getConnection(url, "user", "password");
530
    }
531
}
532

533
// Example 2: Set autosave programmatically
534
public class ProgrammaticAutoSave {
535
    public static void useAutoSave(Connection conn) throws SQLException {
536
        PGConnection pgConn = conn.unwrap(PGConnection.class);
537

538
        // Enable conservative autosave
539
        pgConn.setAutosave(AutoSave.CONSERVATIVE);
540
        conn.setAutoCommit(false);
541

542
        try {
543
            // Even if one of these statements fails, others can succeed
544
            try (PreparedStatement pstmt = conn.prepareStatement(
545
                    "INSERT INTO logs (message) VALUES (?)")) {
546
                pstmt.setString(1, "Log entry 1");
547
                pstmt.executeUpdate();
548
            }
549

550
            try (PreparedStatement pstmt = conn.prepareStatement(
551
                    "INSERT INTO logs (message) VALUES (?)")) {
552
                // This might fail due to constraint violation
553
                pstmt.setString(1, "Duplicate key");
554
                pstmt.executeUpdate();
555
            } catch (SQLException e) {
556
                // With autosave, this error doesn't abort the transaction
557
                System.out.println("One insert failed: " + e.getMessage());
558
            }
559

560
            try (PreparedStatement pstmt = conn.prepareStatement(
561
                    "INSERT INTO logs (message) VALUES (?)")) {
562
                pstmt.setString(1, "Log entry 3");
563
                pstmt.executeUpdate();
564
            }
565

566
            // Commit successful operations
567
            conn.commit();
568

569
        } catch (SQLException e) {
570
            conn.rollback();
571
            throw e;
572
        } finally {
573
            conn.setAutoCommit(true);
574
        }
575
    }
576
}
577

578
// Example 3: ALWAYS autosave for maximum safety
579
public class AlwaysAutoSave {
580
    public static void maximumSafety(Connection conn) throws SQLException {
581
        PGConnection pgConn = conn.unwrap(PGConnection.class);
582
        pgConn.setAutosave(AutoSave.ALWAYS);
583

584
        conn.setAutoCommit(false);
585

586
        try {
587
            // Each statement is protected by automatic savepoint
588
            // If any statement fails, only that statement is rolled back
589

590
            for (int i = 0; i < 100; i++) {
591
                try (PreparedStatement pstmt = conn.prepareStatement(
592
                        "INSERT INTO data (value) VALUES (?)")) {
593
                    pstmt.setInt(1, i);
594
                    pstmt.executeUpdate();
595
                } catch (SQLException e) {
596
                    // Log error but continue
597
                    System.out.println("Failed to insert " + i);
598
                }
599
            }
600

601
            conn.commit();
602

603
        } catch (SQLException e) {
604
            conn.rollback();
605
            throw e;
606
        }
607
    }
608
}
609
```
610

611
### Best Practices
612

613
**Transaction Guidelines:**
614

615
1. **Always use explicit transactions for multiple operations**
616
   ```java
617
   conn.setAutoCommit(false);
618
   try {
619
       // Multiple operations
620
       conn.commit();
621
   } catch (SQLException e) {
622
       conn.rollback();
623
       throw e;
624
   } finally {
625
       conn.setAutoCommit(true);
626
   }
627
   ```
628

629
2. **Set appropriate isolation level**
630
   ```java
631
   // For read-mostly workloads
632
   conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
633

634
   // For consistent reads
635
   conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
636

637
   // For strict consistency
638
   conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
639
   ```
640

641
3. **Use savepoints for complex transactions**
642
   ```java
643
   Savepoint sp = conn.setSavepoint();
644
   try {
645
       // Risky operation
646
   } catch (SQLException e) {
647
       conn.rollback(sp); // Partial rollback
648
   }
649
   ```
650

651
4. **Configure autosave for error recovery**
652
   ```java
653
   // In connection URL or properties
654
   String url = "jdbc:postgresql://localhost/mydb?autosave=conservative";
655
   ```
656

657
5. **Keep transactions short**
658
   - Minimize time between begin and commit
659
   - Don't perform long-running operations in transactions
660
   - Release locks quickly to avoid blocking
661

662
6. **Handle serialization failures**
663
   ```java
664
   int maxRetries = 3;
665
   for (int i = 0; i < maxRetries; i++) {
666
       try {
667
           // Transaction code
668
           conn.commit();
669
           break; // Success
670
       } catch (SQLException e) {
671
           if ("40001".equals(e.getSQLState()) && i < maxRetries - 1) {
672
               conn.rollback();
673
               // Retry
674
           } else {
675
               throw e;
676
           }
677
       }
678
   }
679
   ```
680