{"record":{"id":"a8a21fc2d3e70e51","repo":"pentaho/pentaho-kettle","slug":"unable-to-commit-connection-after-having-inserted-rows","errorCode":null,"errorMessage":"Unable to commit connection after having inserted rows.","messagePattern":"Unable to commit connection after having inserted rows\\.","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"critical","filePath":"core/src/main/java/org/pentaho/di/core/database/Database.java","lineNumber":1793,"sourceCode":"            isBatchUpdate = true;\n            ps.executeBatch();\n            commit();\n          } else {\n            commit();\n          }\n        }\n\n        // Let's not forget to close the prepared statement.\n        //\n        ps.close();\n      }\n    } catch ( BatchUpdateException ex ) {\n      throw createKettleDatabaseBatchException( \"Error updating batch\", ex );\n    } catch ( SQLException ex ) {\n      if ( isBatchUpdate ) {\n        throw createKettleDatabaseBatchException( \"Error updating batch\", ex );\n      } else {\n        throw new KettleDatabaseException( \"Unable to commit connection after having inserted rows.\", ex );\n      }\n    }\n  }\n\n  /**\n   * Execute an SQL statement on the database connection (has to be open)\n   *\n   * @param sql The SQL to execute\n   * @return a Result object indicating the number of lines read, deleted, inserted, updated, ...\n   * @throws KettleDatabaseException in case anything goes wrong.\n   */\n  public Result execStatement( String sql ) throws KettleDatabaseException {\n    return execStatement( sql, null, null );\n  }\n\n  public Result execStatement( String rawsql, RowMetaInterface params, Object[] data ) throws KettleDatabaseException {\n    Result result = new Result();\n","sourceCodeStart":1775,"sourceCodeEnd":1811,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/core/src/main/java/org/pentaho/di/core/database/Database.java#L1775-L1811","documentation":"Kettle wraps any SQLException that occurs while committing a connection after a batch of inserts (in Database.commitInsert / the insert-rows commit path). The insert statements themselves succeeded or failed earlier; this error means the final connection.commit() call failed, so the inserted rows may not be durably committed. It distinguishes batch-update failures (which produce a KettleDatabaseBatchException instead).","triggerScenarios":"Calling Database.insertRow(...) with commit enabled / commitInsert() when connection.commit() throws SQLException — e.g. the connection was closed or broken mid-batch, a constraint/deferred-trigger violation surfaces only at commit, or the transaction was already rolled back by the driver.","commonSituations":"Long-running transformations where the DB connection times out or is killed by the network between inserts; deferred foreign-key or check constraints failing at COMMIT time; the transaction being aborted server-side (e.g. PostgreSQL 'current transaction is aborted'); calling commit after closing the connection in custom steps.","solutions":["Inspect the wrapped cause (getCause()) for the real SQLException message and fix the underlying SQL/constraint problem","Verify the connection is still open and healthy before committing; increase connection/transaction timeouts for long batches","Check for constraint violations (FK, unique, check) that only surface at COMMIT and pre-validate or correct the incoming rows","Catch KettleDatabaseException around insertRow/commit calls and implement rollback + retry with a fresh connection"],"exampleFix":"// before\ndatabase.insertRow(tableMeta, rowMeta, row);\n// after\ntry {\n  database.insertRow(tableMeta, rowMeta, row);\n} catch (KettleDatabaseException e) {\n  logError(\"Commit after insert failed: \" + e.getCause());\n  database.rollback();\n  throw e;\n}","handlingStrategy":"try-catch","validationCode":"if (!database.isOpened()) throw new IllegalStateException(\"Connection must be open before inserting/committing\");","typeGuard":null,"tryCatchPattern":"try {\n  database.insertRow(tableMeta, rowMeta, row);\n} catch (KettleDatabaseException e) {\n  Throwable cause = e.getCause();\n  logError(\"Insert/commit failed: \" + (cause != null ? cause.getMessage() : e.getMessage()));\n  database.rollback();\n  // reconnect and retry or rethrow\n}","preventionTips":["Keep transactions short; commit in smaller batches to reduce exposure to connection drops","Increase network/transaction timeouts for long-running transformations","Pre-validate constraint-sensitive data before inserting","Monitor and reconnect on connection-staleness between batches"],"tags":["database","jdbc","transaction","commit"],"backgroundTag":"database-write-failed","analyzedSha":"f3058517a153da500bf4551f46d79b91bf8ec552","analyzedAt":"2026-09-13T14:04:16.340Z","contentChangedAt":"2026-09-13T14:04:16.340Z","schemaVersion":2},"datasetVersion":"2026-09-20T23:17:15.980Z"}