Answer.

In SQL, when performing bulk changes, it is often necessary to implement an 'all or nothing' scenario - either all changes succeed, or they are rolled back in case of any error. This approach can be achieved by using transactions. The stored procedure should explicitly start a transaction (BEGIN TRANSACTION), wrap the relevant operations, handle errors, and in case of failure, perform a ROLLBACK, otherwise, COMMIT.

It is important to remember proper error handling using constructs like TRY...CATCH (SQL Server) or EXCEPTION (PostgreSQL).

Example for SQL Server:

CREATE PROCEDURE MassUpdate
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE Orders SET Status = 'Processed' WHERE Status = 'Pending';
        UPDATE Inventory SET Stock = Stock - 1 WHERE ProductID IN (SELECT ProductID FROM Orders WHERE Status = 'Processed');

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        -- Error logging
        THROW;
    END CATCH
END

Trick Question.

Question: Is it sufficient to use only BEGIN TRANSACTION and COMMIT to ensure correct rollback of changes on errors across all supported DBMS?

Answer: No, a transaction alone does not catch exceptions. It is necessary to use handlers (TRY...CATCH or similar) to catch the error and explicitly call ROLLBACK. In some DBMS (for example, MySQL with autocommit), additional configuration may also be needed.

Example of incorrect code (SQL Server):

BEGIN TRANSACTION;
UPDATE Users SET Name = 'Ivan' WHERE ID = 1;
UPDATE Orders SET Amount = Amount/0 WHERE UserID = 1; -- Division by zero error
COMMIT TRANSACTION;

In this case, if an error occurs - the transaction will remain open, and changes in the first table may be saved.

History

In an e-commerce project, they forgot to handle errors in transactions, which led to partial loss of related data: if there was a problem updating the stock when updating an order, only part of the changes were rolled back, violating the integrity of the information.

History

In a BI analytics project, they implemented bulk report recalculation through a procedure without explicit transaction control and error catching. The result: some reports were updated, others were not. The final data turned out to be inconsistent, as emergency situations did not lead to atomic rollback.

History

In one company, they mistakenly relied on autocommit in MySQL, not setting the transaction mode for large operations. When the server crashed, part of the data was already written, and part was not, causing long recovery work and loss of some orders.

Explain the approaches to error handling and rollback when working with bulk data changes through stored procedures. How to correctly implement the 'all or nothing' logic when the processing affects several tables at once?

Answer.

Trick Question.