MYSQL - TRANSACTIONS

Question

Good evening, I’m a beginner in mysql and I came up with the following question. When I make a transaction like this

   START TRANSACTION ;
        INSERT INTO PESSOA2(IDPESSOA,NOME) VALUES(NULL,'PEDRO2');
        INSERT INTO DEPENDENTES(IDDEP,NOME,IDPESSOA) VALUES(NULL,'CAROL',543);
        INSERT INTO DEPENDENTES(IDDEP,NOME,IDPESSOA) VALUES(NULL,'CAROL',1);
COMMIT;

is a test transaction, nothing specific ....

I put to give error in the second line purposely... but when the error happens, it does not insert anything in the dependent table (even the second Insert in the table is correct) ,but still inserts in the personal table2, why? I’m doing something wrong?! It wasn’t all or nothing?!

Thanks for your help

---

Good evening Ricardo, thank you for your attention. So, I’m very confused about this... if I do the following transaction in a single table, when finding the error, regardless if you have any correct line, it does not insert... Here comes the doubt, when the Insert is in a single table, to find error does not insert even containing other lines that are correct?!

START TRANSACTION ;

INSERT INTO DEPENDENT VALUES(NULL,'PEDRO',213); INSERT INTO TELEFONE VALUES (NULL,'CARLOS',1); COMMIT;

Continuing... Even without Handler, finding the error was not just meant to stop there?!

I had never seen Handler, I went to test the code you sent this giving error... "DECLARE IS NOT VALID AT THIS POSITION ..." Can you help me? Thank you very much!

Answer 1

Let’s go in pieces:

"I put to error in the second line purposely" , this will cause you to miss only that line, up to that point.

"but still inserts it into the personal table2, because?" because the first line did not fail. You need to better understand before the transaction concept.

The idea is to perform several operations in an atomic way, that is, as one.
In your example, you have three commands, once a transaction has been opened, all commands following the START TRANSACTION will be treated as a single transaction. The error ai in your code is, if you gave error, should ROLLBACK and not COMMIT. Until the moment of COMMIT nothing was "transacted" definitively in the bank, that is the purpose of transacation, ensure that nothing is definitive until you find a COMMIT.

In this case, you should check that all commands were executed without errors, and then do COMMIT, and otherwise ROLLBACK.

To do so, your code could look like this:

    DECLARE EXIT HANDLER FOR SQLEXCEPTION  -- cria um "handler" para tratar a transaction
    BEGIN
        ROLLBACK;  -- se acontecer algum erro, o handler vai fazer rollback
    END;

    START TRANSACTION;  -- inicia a transaction
        INSERT INTO PESSOA2(IDPESSOA,NOME) VALUES(NULL,'PEDRO2');
        INSERT INTO DEPENDENTES(IDDEP,NOME,IDPESSOA) VALUES(NULL,'CAROL',543);
        INSERT INTO DEPENDENTES(IDDEP,NOME,IDPESSOA) VALUES(NULL,'CAROL',1);
    COMMIT;  -- se chegou até aqui, o handler não pegou erro, então faz commit

EDIT the statement EXIT HANDLER is using inside stored procedures, if you are only running commands as script this cannot be using.

In that case, it shall examine the result and then decide whether to COMMIT or ROLLBACK manually. To do this:

• Remove the COMMIT;
• The mysql will do COMMIT by default at the end, to avoid this, start the script with SET autocommit = OFF, and do it manually at the end;
• Execute the code with START TRANSACTION and all desired commands;
• After termination, check for errors, run the command ROLLBACK, if all went well, execute the COMMIT.

For more information on transactions, read the mysql transaction documentation