TRANSACTION

Lê Hồng Hải
UET-VNUH
Overview

1 Introduction

2 Commit-Rollback

3 Isolation Levels

4 Deadlock

2
Transaction example

 A bank customer transfers money from his

savings account to his current account
 A transaction to add new sales order:
1. Insert a new sales order into the orders table
for a given customer.
2. Insert new sales order items into
the orderdetails table

3
Transaction example

 Now, imagine what would happen if

one or more steps above fail due to
some reasons

4
Transaction

 Transaction allows you to execute a set of

operations to ensure that the database
never contains the result of partial
operations
 If one of them fails, the rollback occurs to
restore the database to its original state
 If no error occurs, the entire set of
statements is committed to the database

5
Transaction-Commit-Rollback

6
Script that performs the above steps:
-- 1. start a new transaction
START TRANSACTION;
-- 2. Get the latest order number
SELECT
@orderNumber:=MAX(orderNUmber)+1
FROM
orders;
-- 3. insert a new order for customer 145
INSERT INTO orders(orderNumber,
orderDate,
requiredDate,
shippedDate,
status,
customerNumber)
VALUES(@orderNumber,
'2005-05-31',
'2005-06-10',
'2005-06-11',
'In Process',
145);
7
Script that performs the above steps (ctn..)

-- 4. Insert 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);

-- 5. commit changes
COMMIT;

8
More on Transaction

 The SAVEPOINT command defines a

marker in a transaction
 The ROLLBACK TO SAVEPOINT command
allows rolling back to a previous marker

9
Transaction Savepoint

10
MySQL Transaction

 MySQL InnoDB storage engine

supports transactions
 MyISAM does not support transactions

11
Transaction Properties

 ACID (Atomic, Consistent, Isolated,

Durable)

https://www.ibm.com/docs/en/cics-
ts/5.4?topic=processing-acid-properties-
transactions

12
Transaction in programming languages

 http://www.mysqltutorial.org/mysql-
transaction.aspx
 http://www.mysqltutorial.org/mysql-jdbc-
transaction/

13
Transaction in Distributed System

 The two-phase commit protocol provides

atomicity for distributed transactions to
ensure that each participant in the
transaction agrees on whether the
transaction should be committed or not

14
Today’s Overview

1 Introduction

2 Commit-Rollback

3 Isolation Levels

4 MVCC

15
Transaction isolation levels

 In databases, multiple users can view

and edit data simultaneously
 Concurrent operations can result in
inconsistent and inaccurate data

16
Dirty Reads

 Dirty read occurs when a transaction is allowed to read data

being updated by another uncommitted transaction

17
Nonrepeatable read

18
Phantom Reads

 Occurs when within a transaction, two

identical queries return different sets of
rows when executed

19
MySQL Isolation Levels

Nonrepea
Dirty Phantom
Isolation Levels Usage table
reads reads
reads

Use in situations where

READ UNCOMMITTED accuracy is not so Yes Yes Yes
important

READ COMMITTED Prevent dirty reads No Yes Yes

Default Isolation level in

REPEATABLE-READ No No Yes
MySQL
Transactions are
completely isolated
SERIALIZABLE from each other and No No No
are processed
sequentially

20
Choose Isolation Level

 To decide the isolation level to use, it is

necessary to balance between the required
level of accuracy of the retrieved data and
the processing performance
 The higher the isolation level, the more
impact it has on performance

21
Setting Isolation level in MySQL

 SET SESSION tx_isolation='READ-COMMITTED';

 SELECT @@global.tx_isolation, @@session.tx_isolation

Mysql8 has renamed tx_isolation to transaction_isolation.

22
Multi-versioned concurrency control (MVCC)

 MySQL uses MVCC in transaction

management
 MVCC provides each connection to the
database with a snapshot of the data
 Any changes will not be visible to other users
until the transaction is committed

http://en.wikipedia.org/wiki/Multiversion_concurrency_
control

23
Overview

1 Introduction

2 Commit-Rollback

3 Isolation Levels

4 Deadlock

24
Deadlock i

 A deadlock happens when two or more transactions

are mutually holding and requesting locks on the
same resources, creating a cycle of dependencies

25
Deadlock

 To solve this problem, database systems

implement various forms of deadlock
detection and timeout
 The InnoDB storage engine will notice
circular dependencies and return an error
instantly

SELECT * from performance_schema.data_locks;

26
Non Transactional engine (MyISAM)

 To lock tables that do not support

transactions, use the LOCK TABLES
statement
 Once you have completed updating the
tables, you need to use the UNLOCK
TABLES statement to release the tables

27
REPLICATION
Database Replication

29
Database Replication

 Updates to one database are automatically

replicated to other replicas
 Updates to the master server (primary)
 Queries that read data can be assigned to
the master server or slave servers
(replicas)

30
Replication Advantages

 Availability: Replicas can be used as "hot"

backups; if the master database is not
available, the replicas can take over as the
master until the error is resolved
 Backups: Replicas can be used as backups,
which can be used to perform long
backups without locking the master
 Load Balancing: Read queries can be
distributed to different replicas

31
Asynchronous Replication in MySQL

 In MySQL, replication is a one-way,

asynchronous process
 The master database will store all updates
in a binary log file. Updates in the log file
are then used to synchronize the database
on the slave server
 Slave servers connect to the master server
to read log files and update changes

32
LOG Formats

 Statement-based binary logging: the

master writes SQL statements to the
binary log
 Row-based logging: the master
writes events to the binary log that
indicate how individual table rows are
changed
 Depending on certain statements, and
also the storage engine being used, the log
is automatically switched to row-based in
particular cases

33
MySQL Asynchronous Replication

34
Config Slave server

CHANGE MASTER TO MASTER_HOST='192.168.0.100',

MASTER_USER='slave_user',
MASTER_PASSWORD='<some_password>',
MASTER_LOG_FILE='mysql-bin.006',
MASTER_LOG_POS=183;

35
Config Slave server

◼ START SLAVE;
◼ SHOW SLAVE STATUS

36
MySQL Semisynchronous Replication

37
Semisynchronous Replication

 While the master is blocking (waiting for

acknowledgment from a slave), it does not
return to the session that performed the
transaction
 When the block ends, the master returns
to the session, which then can proceed to
execute other statements

38
MySQL Group Replication

 Implements a multi-master update

everywhere replication protocol.

39
Some Multi-Master solutions for MySQL

40
THANKS YOU