How to write multiple queries using MySQL Transaction

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}

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;

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

If you like FreeWebMentor and you would like to contribute, you can write an article and mail your article to [email protected] Your article will appear on the FreeWebMentor main page and help other developers.

How to write multiple queries using MySQL Transaction

Recommended Posts:

webmentor