MySQL-Transaction.md

Exmaple - 01

CREATE TABLE widgetInventory ( id SERIAL, description VARCHAR(255), onhand INTEGER NOT NULL );

CREATE TABLE widgetSales ( id SERIAL, inv_id INTEGER, quan INTEGER, price INTEGER );

INSERT INTO widgetInventory ( description, onhand ) VALUES ( 'rock', 25 ); INSERT INTO widgetInventory ( description, onhand ) VALUES ( 'paper', 25 ); INSERT INTO widgetInventory ( description, onhand ) VALUES ( 'scissors', 25 );

START TRANSACTION; INSERT INTO widgetSales ( inv_id, quan, price ) VALUES ( 1, 5, 500 ); UPDATE widgetInventory SET onhand = ( onhand - 5 ) WHERE id = 1; COMMIT;

SELECT * FROM widgetInventory; SELECT * FROM widgetSales;

START TRANSACTION; INSERT INTO widgetInventory ( description, onhand ) VALUES ( 'toy', 25 ); ROLLBACK; SELECT * FROM widgetInventory; SELECT * FROM widgetSales;

Example - 02 INSERT Query using Transaction

CREATE TABLE test ( id SERIAL, data VARCHAR(256) );

- Insert 1,000 times ...

INSERT INTO test ( data ) VALUES ( 'this is a good sized line of text.' );

Insert 1000 times using Transaction

START TRANSACTION;

INSERT INTO test ( data ) VALUES ( 'this is a good sized line of text.' ); COMMIT;