By: Prem Tiwari | Last Updated: | In: Mysql, Oracle
A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit.
These statements provide control over use of transactions:
START TRANSACTION or
BEGIN start a new transaction.
COMMIT commits the current transaction,
making its changes permanent.
ROLLBACK rolls back the current
transaction, canceling its changes.
SET autocommit disables or enables the
default autocommit mode for the current session.
1 2 3 4 5 6 7 8 9 10 11 12 | START TRANSACTION [transaction_characteristic [, transaction_characteristic] ...] transaction_characteristic: WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1} |
MySQL transaction example
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | -- start a new transaction start transaction; -- get latest order number select @orderNumber := max(orderNUmber) from orders; -- set new order number set @orderNumber = @orderNumber + 1; -- insert a new order for customer 145 insert into orders(orderNumber, orderDate, requiredDate, shippedDate, status, customerNumber) values(@orderNumber, now(), date_add(now(), INTERVAL 5 DAY), date_add(now(), INTERVAL 2 DAY), 'In Process', 145); -- insert 2 order line items insert into orderdetails(orderNumber, productCode, quantityOrdered, priceEach, orderLineNumber) values(@orderNumber,'S18_1749', 30, '136', 1), (@orderNumber,'S18_2248', 50, '55.09', 2); -- commit changes commit; -- get the new inserted order select * from orders a inner join orderdetails b on a.ordernumber = b.ordernumber where a.ordernumber = @ordernumber; |
Prem Tiwari is the founder of FreeWebMentor.com and also a professional developer who has vast experience in PHP and open source technologies. Apart from this, he is a blogger by hobby and also he has been a regular speaker of WordPress sessions in various IT Companies. View all posts by Prem Tiwari
