MariaDB transaction requests are executed errors

Started by James Fisher, Sep 11, 2022, 03:44 AM

Previous topic - Next topic

James FisherTopic starter

Hello there,

I need advice regarding a simple transaction query I'm writing for a MariaDB 10.5 server. The query involves copying data from one table to another, marking it as archived, and then deleting it from the source table. Despite attempts to update non-existent rows, the code is executed both before and after these attempts, without cancelling the transaction.

To resolve this issue, I would like to know why the transaction doesn't cancel if there are errors in any of the requests. Additionally, I'm curious about how to implement a transaction mechanism that ensures the necessary record is inserted before deleting from the other table, or cancels all actions if this condition isn't met.

Thank you for your help.

SET autocommit=OFF;
START TRANSACTION;
DELETE FROM test2 WHERE pnum=11;/*delete just in case*/
INSERT INTO test2(fam,pnum) (SELECT fam,pnum FROM test1 WHERE id =334);/*copy string from test1 to test2*/
DELETE FROM test2 WHERE pnum=11;/*remove the copied line*/
UPDATE test2 SET location='arhiv' WHERE pnum=11;/*trying to update a non-existent row*/
INSERT INTO test2(fam,pnum) (SELECT fam,pnum FROM test1 WHERE id =334);/*insert this row again*/
COMMIT;
 
SELECT * FROM test2 WHERE pnum=11;/*here we see that the last insert worked and there is data*/

  •  

Digitel

There is no error thrown when trying to modify a non-existent record, as 0 records will be processed. In the case of an error, use the RollBack command to undo the transaction.

However, if there isn't an error and a mistake occurs, have the client catch the exception and launch a rollback to cancel all actions.
  •  

dasiydan88

The reason the transaction doesn't cancel if there are errors in any of the requests is because you have disabled autocommit by setting `autocommit=OFF` at the beginning. This means that you need to explicitly commit or rollback the transaction for it to take effect.

To implement a transaction mechanism that ensures the necessary record is inserted before deleting from the other table, or cancels all actions if this condition isn't met, you can use conditional statements and error handling.

Here's an example of how you can modify your code to achieve this:

```
SET autocommit=OFF;
START TRANSACTION;

BEGIN TRY
  DELETE FROM test2 WHERE pnum=11; -- delete just in case

  -- Insert records from test1 into test2
  INSERT INTO test2(fam,pnum) (SELECT fam,pnum FROM test1 WHERE id =334);

  -- Remove the copied line
  DELETE FROM test2 WHERE pnum=11;

  -- Update the non-existent row
  UPDATE test2 SET location='arhiv' WHERE pnum=11;

  -- Insert the row again
  INSERT INTO test2(fam,pnum) (SELECT fam,pnum FROM test1 WHERE id =334);

  COMMIT;
END TRY
BEGIN CATCH
  ROLLBACK;
END CATCH;

SELECT * FROM test2 WHERE pnum=11;
```

In this modified code, I've added a `BEGIN TRY` block to encapsulate the transaction and a `BEGIN CATCH` block to handle any errors that may occur. If an error occurs within the `TRY` block, the transaction will be rolled back and the error will be caught in the `CATCH` block. If no error occurs, the transaction is committed.

This way, if any of the statements within the transaction fail, the entire transaction will be cancelled, and the changes made so far will be rolled back.


Here's an extended version of the code that includes error handling and a conditional statement to ensure the necessary record is inserted before deleting from the other table:

```sql
SET autocommit=OFF;
START TRANSACTION;

BEGIN TRY
  DECLARE @insertedRows INT;

  DELETE FROM test2 WHERE pnum=11; -- delete just in case

  -- Insert records from test1 into test2
  INSERT INTO test2(fam,pnum) (SELECT fam,pnum FROM test1 WHERE id =334);
  SET @insertedRows = ROW_COUNT();

  -- Check if the necessary record was successfully inserted
  IF @insertedRows > 0 THEN
    -- Remove the copied line
    DELETE FROM test2 WHERE pnum=11;

    -- Update the non-existent row
    UPDATE test2 SET location='arhiv' WHERE pnum=11;

    -- Insert the row again
    INSERT INTO test2(fam, pnum) (SELECT fam,pnum FROM test1 WHERE id =334);

    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
END TRY
BEGIN CATCH
  ROLLBACK;
END CATCH;

SELECT * FROM test2 WHERE pnum=11;
```

In this updated code, I've added a variable `@insertedRows` to store the number of rows inserted by the `INSERT` statement. After inserting the records from `test1` into `test2`, we check if any rows were successfully inserted. If the necessary record was inserted (`@insertedRows > 0`), the subsequent statements will execute. Otherwise, the transaction is rolled back.

This ensures that the necessary record is inserted into `test2` before any deletions or updates occur. If the record is not successfully inserted, the entire transaction is cancelled and no changes are made to `test2`.
  •