CODEWITHSUNDEEP
mysqlsqltransactions
Łukasz
Łukasz

Reputation: 2172

MySQL: transaction not rolled back

doing a simple transaction:

START TRANSACTION;
INSERT INTO users_trips VALUES ('1', '1');
INSERT INTO users_trips VALUES ('1', '41'); <-- this fails due to foreign key contraints
COMMIT;

however, the first record is persisted. I am using InnoDB engine.

mysql> show create table users_trips;
+-------------+--------------------------------------------------------------------------------
| Table       | Create Table
+-------------+--------------------------------------------------------------------------------
| users_trips | CREATE TABLE `users_trips` (
  `user_id` int(11) NOT NULL DEFAULT '0',
  `trip_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`,`trip_id`),
  KEY `users_trips_trip_id_fk` (`trip_id`),
  CONSTRAINT `users_trips_trip_id_fk` FOREIGN KEY (`trip_id`) REFERENCES `trips`     (`trip_id`),
  CONSTRAINT `users_trips_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+--------------------------------------------------------------------------------
1 row in set (0.00 sec)

Why isn't it rolled back?

thanks

Upvotes: 0

Views: 97

Answers (1)

user3020494
user3020494

Reputation: 732

You are doing a COMMIT on your transactions. even if one of the transaction fails before commit, the others will be commited. You would need to issue ROLLBACK in this case.

Upvotes: 1

Related Questions