I have been trying to understand MySQL transactions for a few days now. I wrote a small piece of code which performs a transaction. My transaction contains a series of update statements like this:
START TRANSACTION;
UPDATE ORDERS
SET AMOUNT =1 WHERE AMOUNT > 5000;
UPDE ORDERS
SET AMOUNT =2 WHERE AMOUNT > 5000 AND AMOUNT < 10000;
UPDATE ORDERS
SET AMOUNT =3 WHERE AMOUNT > 10000 AND AMOUNT < 15000;
UPDATE ORDERS
SET AMOUNT =4 WHERE AMOUNT > 15000;
COMMIT;
NOTE that I intentionally misspelled UPDATE in 2nd query to test the atomic property of transactions. It does work. But when I replace these update statements with a series of INSERT statements with one of them misspelled, I found out that all the insert statements before the misspelled one got committed. So I was wondering that do transactions work only for UPDATE or any DML statements? If it supports all DML statements, Can you tell me why does it fail for my insert statements? And I use InnoDB storage engine which supports transactions.
Here is my code for transaction of insert statements: (Note that the 5th INSERT is misspelled intentionally)
START TRANSACTION;
INSERT INTO ORDERS VALUES (1111,2007-12-17,2099,999,'ABC','ADI11',987,88888);
INSERT INTO ORDERS VALUES (1112,2007-12-17,2099,999,'ABC','MHXYZ22',987,88888);
INSERT INTO ORDERS VALUES (1113,2007-12-17,2099,999,'ABC','MHXYZ33',987,88888);
INSERT INTO ORDERS VALUES (1114,2007-12-17,2099,999,'ABC','MHXYZ44',987,88888);
ISERT INTO ORDERS VALUES (1115,2007-12-17,2099,999,'ABC','MHXYZ55',987,88888);
INSERT INTO ORDERS VALUES (1116,2007-12-17,2099,999,'ABC','MHXYZ66',987,88888);
INSERT INTO ORDERS VALUES (1117,2007-12-17,2099,999,'ABC','MHXYZ77',987,88888);
INSERT INTO ORDERS VALUES (1118,2007-12-17,2099,999,'ABC','MHXYZ88',987,88888);
INSERT INTO ORDERS VALUES (1119,2007-12-17,2099,999,'ABC','MHXYZ99',987,88888);
INSERT INTO ORDERS VALUES (1120,2007-12-17,2099,999,'ABC','MHXYZ1111',987,88888);
INSERT INTO ORDERS VALUES (1121,2007-12-17,2099,999,'ABC','MHXYZ2222',987,88888);
COMMIT;
