Implementasi SQL Transaction pada mySQL

Langsung aja, tanpa basa-basi.

Implementasi di Stored Procedure

Cara pertama:

CREATE PROCEDURE `sp_fail`()
BEGIN
 DECLARE `_rollback` BOOL DEFAULT 0;
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
 START TRANSACTION;
 INSERT INTO `tablea` (`date`) VALUES (NOW());
 INSERT INTO `tableb` (`date`) VALUES (NOW());
 INSERT INTO `tablec` (`date`) VALUES (NOW()); -- FAIL
 IF `_rollback` THEN
  ROLLBACK;
 ELSE
  COMMIT;
 END IF;
END$$

Cara kedua:

CREATE PROCEDURE `sp_fail`()
proc: BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION, NOT FOUND 
 BEGIN
  ROLLBACK;
  LEAVE proc;
 END;

 START TRANSACTION;
 insert into myTable values1 ...
 insert into myTable values2 ... 
 COMMIT; 
END$$

DELIMITER ;

Implementasi pada PHP: 

mysql_query("START TRANSACTION");

$a1 = mysql_query("INSERT INTO rarara (l_id) VALUES('1')");
$a2 = mysql_query("INSERT INTO rarara (l_id) VALUES('2')");

if ($a1 and $a2) {
 mysql_query("COMMIT");
} else { 
 mysql_query("ROLLBACK");
}

Read more