Answer.

Background
In business logic processing operations, it is often necessary to change data in several related tables. If something fails (for example, due to a violated constraint), it is important to roll back not only the last operation but all actions within the logic. This is done using transaction mechanics and error handling.

Problem
Not all DBMS automatically guarantee a rollback on error in multi-step procedures (especially when using TRY/CATCH, EXCEPTION handlers). Incorrectly written transactional logic leads to "partial" changes (some data updated, some not), which compromises business integrity.

Solution
For correct handling, explicit opening and closing of transactions (BEGIN TRANSACTION, COMMIT/ROLLBACK) with error catching are used. The syntax and capabilities differ across DBMS.

Example for MS SQL Server:

CREATE PROCEDURE update_balances(@from INT, @to INT, @amount DECIMAL(10,2))
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE accounts SET balance = balance - @amount WHERE id = @from;
      UPDATE accounts SET balance = balance + @amount WHERE id = @to;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    INSERT INTO error_log(err_message, err_date) VALUES(ERROR_MESSAGE(), GETDATE());
    THROW; -- re-throwing the error
  END CATCH
END;

For PostgreSQL:

CREATE OR REPLACE FUNCTION update_balances(from_id INT, to_id INT, amount NUMERIC)
RETURNS void AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  -- An exception will automatically roll back the transaction
EXCEPTION WHEN OTHERS THEN
  INSERT INTO error_log(err_message, err_date) VALUES(SQLERRM, NOW());
  RAISE;
END;
$$ LANGUAGE plpgsql;

Key features:

• Open and explicitly close the transaction.
• Handle errors in catch/exception blocks and roll back changes.
• Log error information for auditing.

Trick questions.

Can an error inside a stored procedure always automatically roll back the transaction? No! For example, in MS SQL Server such rollback is not guaranteed — explicit ROLLBACK is required. In PostgreSQL, any error renders the entire transaction "corrupted" and requires completion.

Can you commit "partially" within a single procedure? Bad practice. It is recommended to do commit/rollback only once at the end of the business operation. Some platforms allow "savepoints", but this is for special tasks.

What types of errors cannot be caught via TRY/CATCH/EXCEPTION in SQL? Some system failures (e.g., loss of connection with the server) will not be caught by the handler and may make rollback impossible.

Typical mistakes and anti-patterns

• Leaving COMMIT without error handling: fixing "broken" or partially changed data in the database
• Multiple COMMIT/ROLLBACK within a single business operation
• Ignoring error logging

Real-life example

Negative case

The stored procedure updated three tables and ended with COMMIT without error catching. As a result, when an error occurred in the second table, the first was already changed, and it took hours to "roll back" the data manually from backups.

Pros:
Simple and "works" until a failure occurs.

Cons:
Risk of inconsistency; impossible to recover quickly in case of errors.

Positive case

Implemented explicit error handling + transaction rollback with logging of all failures in a separate log. Returning to a consistent state of the database takes seconds, failures are analyzed and minimized.

Pros:
Guarantee of unchanged clean data even in case of failures; transparent logic.

Cons:
Slightly more complicated for beginners, requires discipline in coding.