Prem Tiwari - - 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; |
Tags: Database
