What causes "Invalid transaction isolation level: " in prestodb/presto?

Pass only the standard java.sql.Connection TRANSACTION_* constants (1-4)

prestodb/presto · error · SQLException

Invalid transaction isolation level:

Error message

Invalid transaction isolation level:

What it means

PrestoConnection.getIsolationLevel() throws this SQLException when setTransactionIsolation(level) is called with an integer that is not one of TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, or TRANSACTION_SERIALIZABLE. It is an argument-validation guard mapping the level constant to its Presto session name; unknown values cannot be mapped.

Source

Thrown at presto-jdbc/src/main/java/com/facebook/presto/jdbc/PrestoConnection.java:922

        if (resultSetHoldability != ResultSet.HOLD_CURSORS_OVER_COMMIT) {
            throw new SQLFeatureNotSupportedException("Result set holdability must be HOLD_CURSORS_OVER_COMMIT");
        }
    }

    private static String getIsolationLevel(int level)
            throws SQLException
    {
        switch (level) {
            case TRANSACTION_READ_UNCOMMITTED:
                return "READ UNCOMMITTED";
            case TRANSACTION_READ_COMMITTED:
                return "READ COMMITTED";
            case TRANSACTION_REPEATABLE_READ:
                return "REPEATABLE READ";
            case TRANSACTION_SERIALIZABLE:
                return "SERIALIZABLE";
        }
        throw new SQLException("Invalid transaction isolation level: " + level);
    }
}

View on GitHub (pinned to 55bb57d202)

Solutions

Pass only the standard java.sql.Connection TRANSACTION_* constants (1-4)
Remove TRANSACTION_NONE (0) / vendor-specific values from configuration
If config holds names, map them: "READ_COMMITTED" -> Connection.TRANSACTION_READ_COMMITTED

Example fix

// before
conn.setTransactionIsolation(5); // vendor constant
// after
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

Defensive patterns

Strategy: validation

Validate before calling

int level = Integer.parseInt(cfg.get("isolation"));
if (level != Connection.TRANSACTION_READ_UNCOMMITTED
        && level != Connection.TRANSACTION_READ_COMMITTED
        && level != Connection.TRANSACTION_REPEATABLE_READ
        && level != Connection.TRANSACTION_SERIALIZABLE) {
    throw new IllegalArgumentException("Unsupported isolation level: " + level);
}
conn.setTransactionIsolation(level);

Try / catch

try {
    conn.setTransactionIsolation(level);
} catch (SQLException e) {
    if (e.getMessage() != null && e.getMessage().startsWith("Invalid transaction isolation level")) {
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    } else {
        throw e;
    }
}

Prevention

Only pass java.sql.Connection TRANSACTION_* constants
Reject TRANSACTION_NONE and vendor-specific ints in config parsing
Map human-readable names in config to JDBC constants explicitly

When it happens

Trigger: setTransactionIsolation(0) or any nonstandard int (e.g. 5, vendor-specific constants from another driver); passing TRANSACTION_NONE; passing a level read from config as a raw string parsed to int.

Common situations: Using another database's isolation-level constant (e.g. SQL Server snapshot constants); config files storing level names instead of JDBC int constants; JDBC 4.x drivers exposing TRANSACTION_SNAPSHOT which Presto lacks.

Understand the failure class

Background: "Must be a positive integer", "Invalid value", "Unsupported": the invalid-argument-value error family, when a library rejects the value you pass — this error's family across 35 libraries.

Related errors

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/0c94e5bec4787dc9. Report an issue: GitHub.