Is it possible to commit a transaction if the first table successfully recorded data, but an error occurred in the second?

No, it is not possible to commit a transaction if the second table caused an error after successfully committing data in the first. A transaction is an atomic operation that either completes entirely or is completely rolled back.

If an error occurs while working with the second table within a single transaction, the entire transaction should be canceled (rollback) to maintain data integrity. Otherwise, the data will be in an inconsistent state.

Example in Java using JDBC:

try {
    connection.setAutoCommit(false);
    // Insert into the first table
    // ...
    // Insert into the second table
    // ...
    connection.commit();
} catch (SQLException e) {
    connection.rollback(); // rollback all changes
}