21. Explain how relational-database transactional guarantees should be exposed through an application API.
Explain the outward-facing behavior callers should expect from transactional APIs, including rollback, isolation, and failure visibility.
At a high level, I would expose clear transaction outcomes through the API while letting the relational database enforce ACID guarantees. The client sends an HTTPS POST /orders request to the API Endpoint. The application validates and authorizes it, runs the business operation, and keeps all related database changes inside one transaction. The transaction either commits everything or rolls everything back. The API returns clear success or failure responses with Problem Details. The trade-off is that stronger isolation gives safer concurrency behavior but can increase locking and reduce throughput.
This question asks how an API should behave when one request changes several pieces of database data. The caller needs to know whether the whole operation succeeded or failed. The caller should never see half-finished work. The API should also explain what other callers may see while changes are happening. When something fails, the caller needs a clear result instead of guessing. The diagram solves this by keeping transaction handling inside the application and database while exposing simple success, conflict, validation, and server-error outcomes to the caller.
- Which isolation level should this API use for normal requests?
- Should create-like operations support safe retries through idempotency?
- Which business or concurrency conflicts should return 409?
I would first keep the caller-facing contract simple. The API Client sends an HTTPS POST /orders request to the API Endpoint. The endpoint validates the input and authorizes the caller. It starts the transaction scope for the request. The caller does not need to understand database transaction commands. It only needs a clear API result. This keeps the public API contract separate from the database implementation.
The API Endpoint sends the use case to the Application Service. The Application Service orchestrates the operation and applies business rules. It can also perform an idempotency check when that behavior is used. Data access then continues through ADO.NET or EF Core to the Unit of Work. The Unit of Work begins the database transaction and keeps the related reads and writes inside that transaction. This allows the complete business operation to succeed or fail as one unit.
The Unit of Work sends SQL commands to the Relational Database while the transaction is active. The database enforces atomicity, consistency, isolation, and durability. Atomicity means all transaction changes commit together or none commit. Consistency means constraints and valid committed state are preserved. Isolation controls what concurrent transactions can observe. Durability means committed data survives later system or database failures. The API should document the chosen isolation level and explain what that level does and does not guarantee. The diagram shows Read Committed as the default, with Repeatable Read, Snapshot, and Serializable as other possible levels.
If every operation succeeds, the Unit of Work commits the transaction. The committed changes then become visible as the successful result. If an operation fails, the Unit of Work rolls the transaction back. No partial transaction result becomes visible to other callers. The database returns results or errors to the Unit of Work. This keeps rollback as an internal transaction action while the caller receives a clear outward-facing outcome.
Failures go through Exception Handling & Mapping. That component maps domain and database failures into stable HTTP errors. It includes useful failure details without leaking personally identifiable information. The mapped response returns to the API Endpoint and then back to the API Client over HTTPS. The diagram shows 2xx for committed success, 409 for a business-rule or concurrency conflict, 4xx for caller validation errors, and 5xx for an unexpected server failure. Error responses use Problem Details and include a correlation ID for tracing and support.
The caller should expect all-or-nothing changes, documented isolation behavior, valid committed state, durable commits, and explicit failure responses. Rollbacks should not expose partial data. For safe retries, create-like operations should define idempotency behavior. Transactions should stay short because long transactions can increase lock contention. Stronger isolation can make concurrent behavior safer and easier to reason about, but it can reduce throughput. The API should therefore document the actual isolation level and retry behavior instead of promising stronger guarantees than the database configuration provides.
The benefit of this design is that callers get a simple API contract while the database handles transaction correctness. Related changes stay inside one transaction, so partial updates are not exposed. The API also converts internal failures into stable HTTP outcomes and Problem Details. The downside is that stronger isolation can increase locking or other concurrency costs and reduce throughput. Long transactions can make contention worse, so the diagram recommends keeping transactions short. Idempotency can make retries safer for create-like operations, but it requires extra application logic. Correlation IDs and consistent error mapping also add implementation work. We accept these costs because they make failures clearer, retries safer, and transactional behavior easier to operate and support.
Interviewers want to see whether you can separate database guarantees from the public API contract. They are checking whether you understand commit, rollback, isolation, failure visibility, and HTTP error behavior. They also want sound judgment around concurrency conflicts, idempotency, short transactions, and stable error responses. A strong answer shows that you can explain what callers may rely on without promising guarantees that the selected database isolation level does not actually provide.









