SQ
SQL Error Handling
SQL error handling patterns using transactions, savepoints, and exception blocks
Details
Language / Topic
SQLCategory
Error Handling
Rules
balanced
- Wrap multi-statement data modifications in explicit `BEGIN` / `COMMIT` transactions so all changes succeed or all fail atomically — never rely on auto-commit for multi-step writes.
- In PostgreSQL stored procedures, use `EXCEPTION WHEN others THEN RAISE;` to log and re-raise unhandled exceptions rather than swallowing them silently.
- Use `SAVEPOINT sp_name` and `ROLLBACK TO SAVEPOINT sp_name` for partial rollback within a long transaction rather than rolling back all changes.
- In application code, always check the database driver's error code (e.g., `23505` for unique violation in PostgreSQL) and handle constraint violations explicitly rather than catching generic exceptions.
- Use `INSERT ... ON CONFLICT DO UPDATE` (PostgreSQL) or `MERGE` (SQL standard) instead of a `SELECT` then `INSERT` pattern — the two-step approach has a TOCTOU race condition.
- Test transaction isolation by running concurrent sessions in load tests — verify that `REPEATABLE READ` or `SERIALIZABLE` isolation prevents the anomalies your application requires.
- Document error codes returned by stored procedures in their header comments so application developers know which exceptions to expect.