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
