Questions: Stored Procedures: Procedural Logic and Transaction Control

Question 1

A bank has a web app, a mobile app, and an internal admin tool — all of which need to transfer funds between accounts. What is the strongest argument for implementing the transfer logic as a stored procedure rather than in each application?

• A. Stored procedures execute faster because they run closer to the data
• B. Stored procedures guarantee that every transfer enforces the same rules regardless of which application calls the database
• C. Stored procedures prevent SQL injection attacks more reliably than application code
• D. Stored procedures automatically partition the workload across database servers

Answer: (B)

The core benefit of centralizing logic in a stored procedure is consistency and enforcement. If the transfer logic lives in each application, one app might have a bug, a different version, or be bypassed by someone running SQL directly. A stored procedure enforces the business rules at the database level — the single point of truth — regardless of who or what calls it. Performance is a secondary benefit; the enforcement guarantee is the primary justification.

Question 2

Inside a stored procedure for a fund transfer, after debiting Account A, you set a SAVEPOINT. Then the credit to Account B fails. You execute ROLLBACK TO SAVEPOINT. What is the state of the transaction?

• A. The entire transaction is rolled back — Account A's debit is also reversed
• B. Only the failed credit attempt is rolled back; Account A's debit remains
• C. The transaction is committed up to the savepoint — Account A's debit is permanent
• D. SAVEPOINT has no effect when a statement fails; only full ROLLBACK works

Answer: (B)

ROLLBACK TO SAVEPOINT undoes all work done since the savepoint was set, but leaves work done before the savepoint intact within the transaction. The debit (done before the savepoint) is still pending; the failed credit (done after) is undone. Crucially, 'intact within the transaction' does not mean committed — the transaction is still open, and the debit has not been written permanently. A subsequent COMMIT or full ROLLBACK will determine the final state.

Question 3

A stored procedure that wraps a fund transfer in BEGIN/COMMIT/ROLLBACK ensures that either both the debit and credit happen or neither happens.

• T. True
• F. False

Answer: True

This is the atomicity guarantee of transaction control. BEGIN marks the start of the transaction; COMMIT makes all changes permanent only if every step succeeds; ROLLBACK undoes all changes if any step fails. By wrapping both operations in the same transaction boundary, the stored procedure ensures they are treated as a single atomic unit — the 'all or nothing' property of ACID transactions. Without this wrapper, a failure between debit and credit would leave the accounts in an inconsistent state.

Question 4

Rolling back a transaction to a SAVEPOINT permanently commits most of the work done before the savepoint.

• T. True
• F. False

Answer: False

ROLLBACK TO SAVEPOINT undoes work done after the savepoint but does not commit anything. The transaction is still open; work done before the savepoint is preserved within the transaction but has not been written to the database permanently. Only a COMMIT statement makes changes permanent. SAVEPOINT simply creates a partial-undo checkpoint within an ongoing transaction — it does not split the transaction into committed and uncommitted segments.

Question 5

Why does putting business logic in a stored procedure provide stronger consistency guarantees than putting the same logic in application code, even if both implementations are correct?

Model answer: Application code can be deployed inconsistently across multiple applications, can have version mismatches, or can be bypassed by anyone with direct database access. A stored procedure lives inside the database itself — it is the only path through which the operation executes, regardless of which application, user, or tool initiates the call. It also runs atomically inside the database engine, so network failures between application steps cannot leave the database in a half-updated state.

The key insight is that the database is the single source of truth, and stored procedures enforce rules at that level. Application-layer logic is contingent on every caller using the same version of the correct code — which is hard to guarantee in practice. By contrast, a stored procedure ensures that the rules are enforced at the point where data actually changes, making consistency a property of the database rather than a property of every application that talks to it.