Bài giảng Cơ sở dữ liệu - Chương 9, Phần 2: Transaction - Nguyễn Hồng Phương
10/11/2019
Contents
Introduction
Transaction
Properties of a transaction
Processing a transaction
Some examples
NGUYEN HongPhuong
Email: [email protected]
Hanoi University of Science and Technology
1
2
Introduction
Properties of a transaction
Transactions in SQL are a group of
SQL statements. If transaction is
The transaction has 4 standard
a
properties, referenced by ACID
made successfully, all data changes
made in the transaction are saved to
the database. If a transaction fails and
is rolled back, all data modifications will
be deleted (data is restored to the state
before the transaction was executed).
Atomicity: ensures that all operations
within the work unit are completed
successfully. Otherwise, the transaction is
aborted at the point of failure and all the
previous operations are rolled back to their
former state.
3
4
1
10/11/2019
Properties of a transaction
Transaction States and Additional Operations
Consistency: ensures that the database
properly changes states upon a successfully
committed transaction.
Isolation: enables transactions to operate
independently of and transparent to each
other.
Durability: ensures that the result or effect of
a committed transaction persists in case of a
system failure.
A transaction is an atomic unit of work
that is either completed in its entirety
or not done at all.
For recovery purposes, the system
needs to keep track of when the
transaction starts, terminates, and
commits or aborts.
Hence, the recovery manager keeps
track of the following operations:
5
6
Processing a transaction
A state transition diagram
READ, WRITE
The following commands are used to
process transactions.
COMMIT: to save the changes.
ROLLBACK: to return to the previous state
PARTIALLY
COMMITTED
ACTIVE
COMMITTED
COMMIT
BEGIN
TRANSACTION
END
TRANSACTION
before changing.
ABORT
SAVEPOINT: create points within the
transaction group to ROLLBACK, i.e. to return
to that status point.
ABORT
FAILED
TERMINATED
SET TRANSACTION: give a name to a
transaction.
These commands are only used with DML:
INSERT, UPDATE and DELETE.
7
8
2
10/11/2019
COMMIT command
ROLLBACK command
Used to save the changes invoked by a
Used to return transactions to a state
before changes have not been saved to
the database.
Can only be used to undo transactions
from the last COMMIT or ROLLBACK
command.
transaction to the database.
Stores all transactions in the Database
since the last COMMIT or ROLLBACK
command.
The basic syntax of a COMMIT
command is as follows:
The basic syntax:
COMMIT;
ROLLBACK;
ROLLBACK TO SavePointName;
9
10
SAVEPOINT
RELEASE SAVEPOINT command
A SAVEPOINT is a point in a transaction
when you can undo the transaction to a
specific point without having to roll it
back to the first state before that
change.
Used to delete a SAVEPOINT that you
have created.
The basic syntax
RELEASE SAVEPOINT SAVEPOINT_NAME;
The basic syntax of the SAVEPOINT is
Once a SAVEPOINT has been deleted,
you can no longer use the ROLLBACK
command to undo the transaction to
that SAVEPOINT.
as follows:
SAVEPOINT SAVEPOINT_NAME;
11
12
3
10/11/2019
Transactions, read and write operation,
DBMS buffer
SET TRANSACTION command
The database operations that form a
transaction can either be embedded within
an application program or they can be
specified interactively via a high-level
query language such as SQL.
One way of specifying the transaction
boundaries is by specifying explicit begin
transaction and end transaction
Can be used to initiate a Database
Transaction. This command is used to
characterize the transaction.
For example, you can specify a
transaction as read only or read write.
The basic syntax
SET TRANSACTION [READ WRITE | READ ONLY];
statements in an application program
13
14
A read-only transaction
The basic unit of data transfer from disk to
main memory is one block
Executing a read_item(X) command
do not update the database but only retrieve
data
To simplify, the basic database access
operations that a transaction can include
are as follows:
includes the following steps:
Find the address of the disk block that
contains item X
read_item(X): Reads a database item named
X into a program variable also named X.
write_item(X): Writes the value of program
Copy that disk block into a buffer in main
memory (if that disk block is not already in
some main memory buffer)
variable X into the database item named X
Copy item X from the buffer to the program
variable named X
15
16
4
10/11/2019
Executing a write_item(X) command includes the
A transaction includes read_item and
following steps:
write_item operations to access and
update the database.
The read-set of a transaction is the set
of all items that the transaction reads
The write-set is the set of all items that
Find the address of the disk block that contains item X.
Copy that disk block into a buffer in main memory (if
that disk block is not already in some main memory
buffer).
Copy item X from the program variable named X into its
correct location in the buffer.
Store the updated block from the buffer back to disk
(either immediately or at some later point in time).
the transaction writes.
17
18
Why Concurrency Control Is Needed
Problem
The Lost Update
T1
T2
The Temporary Update (Dirty Read)
The Incorrect Summary
The Unrepeatable Read
read_item (X);
X:=X-N;
write_item (X);
read_item (Y);
Y:=Y+N;
read_item (X);
X:=X+M;
write_item (X);
write_item (Y);
19
20
5
10/11/2019
The Lost Update Problem
The Temporary Update Problem
Occurs when two transactions that access the
same database items have their operations
interleaved in a way that makes the value of
some database items incorrect.
Occurs when one transaction updates a
database item and then the transaction fails for
some reason.
The updated item is accessed by another
transaction before it is changed back to its
original value.
T1
T2
read_item (X);
X:=X-N;
T1
T2
read_item (X);
Transaction T1 fails
and must change
the value of X back
to its old value;
meanwhile T2 has
read the
X:=X+M;
read_item (X);
X:=X-N;
write_item (X);
Time
write_item (X);
read_item (Y);
write_item (X);
Y:=Y+N;
Time
read_item (X);
X:=X+M;
write_item (X);
write_item (Y);
“temporary”
incorrect value of X.
read_item (Y);
21
22
The Incorrect Summary Problem
The Unrepeatable Read Problem
T1
T3
If one transaction is
calculating an
aggregate summary
function on a number
of records while other
transactions are
updating some of
these records, the
aggregate function
may calculate some
values before they
are updated and
others after they are
updated
A transaction T reads an item twice and
the item is changed by another
transaction T' between the two reads
Hence, T receives different values for
sum:=0;
read_item(A);
sum:=sum+A;
read_item(X);
X:=X-N;
write_item(X);
read_item(X);
sum:=sum+X;
read_item(Y);
sum:=sum+Y;
Time
its two reads of the same item.
read_item(Y);
Y:=Y+N;
write_item(Y);
T3 reads X after N is subtracted
and reads Y before N is added
23
24
6
10/11/2019
Why Recovery Is Needed
Some examples
The DBMS must not permit some operations of a
transaction T to be applied to the database while other
operations of T are not. This may happen if a transaction
fails after executing some of its operations but before
executing all of them.
Using an explicit transaction
CREATE TABLE ValueTable (id int);
INSERT INTO ValueTable VALUES(1);
INSERT INTO ValueTable VALUES(2);
BEGIN TRANSACTION;
DELETE FROM ValueTable
WHERE id = 2;
There are several possible reasons for a transaction to fail
in the middle of execution:
A computer failure (system crash)
A transaction or system error
Local errors or exception conditions detected by the
transaction
COMMIT;
Rolling back a transaction
BEGIN TRANSACTION;
Concurrency control enforcement
Disk failure
Physical problems and catastrophes
INSERT INTO ValueTable VALUES(3);
INSERT INTO ValueTable VALUES(4);
ROLLBACK;
25
26
Some examples (2)
Some examples (3)
Naming a transaction
Marking a transaction
BEGIN TRANSACTION Del
WITH MARK N'Deleting a row';
DELETE FROM ValueTable WHERE id = 1;
COMMIT TRANSACTION Del;
DECLARE @TranName VARCHAR(20);
SELECT @TranName = 'MyTransaction';
BEGIN TRANSACTION @TranName;
DELETE FROM ValueTable WHERE id = 1;
COMMIT TRANSACTION @TranName;
27
28
7
10/11/2019
29
8
8 trang
13701
Free
Bạn đang xem tài liệu "Bài giảng Cơ sở dữ liệu - Chương 9, Phần 2: Transaction - Nguyễn Hồng Phương", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- bai_giang_co_so_du_lieu_chuong_9_phan_2_transaction_nguyen_h.pdf
