How to write multiple queries using MySQL Transaction

By: Prem Tiwari | Last Updated: November 26, 2014 | 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.

mysql_transaction

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

-- 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;

About: Prem Tiwari

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

Database