CODEWITHSUNDEEP
mysqltransactions
Matija Lukic
Matija Lukic

Reputation: 669

Mysql transaction doesn't work

I am trying to find what is the problem with this SQL transaction: 

START TRANSACTION;
INSERT INTO address VALUES (null, 'Address name', 18);
-- First INSERT statement is CORRECT
INSERT INTO users VALUES (null, 'First_name', 'Last_name', 'username', 'password', '2', '[email protected]', last_insert_id(), 1);
-- Second INSERT statement is INCORRECT
COMMIT;

First SQL query was successfully executed although the second query was failed. Purpose of transaction is to execute all statements or none. Both users and address tables are InnoDB. Did I write this transaction correctly?

Upvotes: 2

Views: 1798

Answers (1)

Matija Lukic
Matija Lukic

Reputation: 669

I've just solved problem. I put transaction inside procedure and also add SQL code for SQL EXCEPTION. Now works fine.

DROP PROCEDURE IF EXISTS insert_user;

DELIMITER //
CREATE PROCEDURE insert_user()
BEGIN

DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
  ROLLBACK;
END;

START TRANSACTION;
INSERT INTO address VALUES (null, 'Address name', 18);
-- First INSERT statement is CORRECT
INSERT INTO users VALUES (null, 'First_name', 'Last_name', 'username', 'password', '2', '[email protected]', last_insert_id(), 1);
-- Second INSERT statement is INCORRECT
COMMIT;
END //
DELIMITER ;

Upvotes: 3

Related Questions