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*/
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.
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`.
Refactor your transaction to include checks after each operation. For example, after the INSERT, verify that the row exists before proceeding to DELETE or UPDATE. If the row isn't found, trigger a ROLLBACK. This guarantees that all operations are contingent on the successful execution of the previous ones.
Here's a revised version of your transaction code that includes error handling to ensure that operations are executed safely and consistently:
SET autocommit=OFF;
START TRANSACTION;
-- Delete just in case
DELETE FROM test2 WHERE pnum=11;
-- Insert data from test1 to test2 and check for success
INSERT INTO test2(fam, pnum)
SELECT fam, pnum FROM test1 WHERE id = 334;
-- Check if the insert was successful
IF ROW_COUNT() = 0 THEN
ROLLBACK;
SELECT 'Insert failed, transaction rolled back.' AS message;
LEAVE; -- Exit the transaction
END IF;
-- Attempt to delete the copied line
DELETE FROM test2 WHERE pnum=11;
-- Check if the delete was successful
IF ROW_COUNT() = 0 THEN
ROLLBACK;
SELECT 'Delete failed, transaction rolled back.' AS message;
LEAVE; -- Exit the transaction
END IF;
-- Attempt to update a row and check for success
UPDATE test2 SET location='arhiv' WHERE pnum=11;
-- Check if the update was successful
IF ROW_COUNT() = 0 THEN
ROLLBACK;
SELECT 'Update failed, transaction rolled back.' AS message;
LEAVE; -- Exit the transaction
END IF;
-- Insert the row again if needed
INSERT INTO test2(fam, pnum)
SELECT fam, pnum FROM test1 WHERE id = 334;
-- Check if the insert was successful again
IF ROW_COUNT() = 0 THEN
ROLLBACK;
SELECT 'Final insert failed, transaction rolled back.' AS message;
LEAVE; -- Exit the transaction
END IF;
COMMIT;
SELECT * FROM test2 WHERE pnum=11; -- View the result
This code includes checks after each critical SQL operation to ensure that if any operation fails, the transaction will roll back, maintaining the integrity of your data. Adjust the error handling according to your specific needs and environment.