BKM_DATS: Databázové systémy
3. Transactions
Vlastislav Dohnal
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 2
Transactions
Transaction Concept
Transaction State
Concurrent Executions
Serializability
Recoverability
Implementation of Isolation
Transaction Definition in SQL
Testing for Serializability.
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 3
Transaction Concept
A transaction is a unit of program execution that accesses and
possibly updates various data items.
E.g., transaction to transfer $50 from account A to account B:
1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)
7. commit
Main issues to deal with:
Transaction interruption due failures of various kinds
such as hardware failures and system crashes
Concurrent execution of multiple transactions
Termination of transaction using abort command
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 4
Example of Fund Transfer
Transaction to transfer $50 from account A to account B:
1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)
7. commit
Atomicity requirement
if the transaction fails after step 3 and before step 6, money will be “lost”
leading to an inconsistent database state
Failure could be due to software or hardware
the system should ensure that updates of a partially executed transaction
are not reflected in the database
Durability requirement
once the user has been notified that the transaction has completed (i.e.,
the transfer of the $50 has taken place), the updates to the database by
the transaction must persist even if there are software or hardware
failures.
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 5
Example of Fund Transfer (Cont.)
Transaction to transfer $50 from account A to account B:
1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)
7. commit
Consistency requirement
E.g., the sum of A and B is unchanged by the execution of the transaction
In general, consistency requirements include
Explicitly specified integrity constraints such as primary keys and foreign
keys
Implicit integrity constraints
E.g., sum of balances of all accounts, minus sum of loan amounts
must equal value of cash-in-hand
A transaction must see a consistent database.
During transaction execution the database may be temporarily inconsistent.
When the transaction completes successfully the database must be consistent
Erroneous transaction logic can lead to inconsistency
Example of Fund Transfer (Cont.)
Transaction to transfer $50 from account A to account B:
Isolation requirement – if between steps 3 and 6, another
transaction T2 is allowed to access the partially updated database, it
will see an inconsistent database
The sum A + B will be less than it should be.
Isolation can be ensured trivially by running transactions serially
that is, one after the other.
However, executing multiple transactions concurrently has significant
benefits, as we will see later.
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 6
Transaction
T11.read(A)
2.A := A – 50
3.write(A)
4.read(B)
5.B := B + 50
6.write(B)
7.commit
Transaction
T21.read(A)
2.read(B)
3.print(A + B)
4.commit
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 7
ACID Properties
A transaction is a unit of program execution that accesses and possibly updates various
data items.
It is a sequence of operations that form a desired outcome (the unit of program).
To preserve the integrity of data the database system must ensure:
Atomicity.
Either all operations of the transaction are properly reflected in the database or
none are.
Consistency.
Execution of a transaction in isolation preserves the consistency of the database.
Isolation.
Although multiple transactions may execute concurrently, each transaction must
be unaware of other concurrently executing transactions. Intermediate transaction
results must be hidden from other concurrently executed transactions.
That is, for every pair of transactions Ti and Tj, it appears to Ti that either Tj,
finished execution before Ti started, or Tj started execution after Ti finished.
Durability.
After a transaction completes successfully, the changes it has made to the
database persist, even if there are system failures.
Transaction State
Active
the initial state
the transaction stays in this
state while it is executing
Partially committed
after the final statement has
been executed.
Committed
after successful completion.
Failed
after the discovery that normal execution can no longer proceed.
Aborted
after the transaction has been rolled back and the database restored to
its state prior to the start of the transaction.
Two options after it has been aborted:
restart the transaction
can be done only if no internal logical error
kill the transaction
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 8
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 9
Concurrent Executions
Multiple transactions are allowed to run concurrently in the system.
Advantages are:
increased processor and disk utilization, leading to better
transaction throughput
E.g., one transaction can be using the CPU while another is
reading from or writing to the disk
reduced average response time for transactions
E.g., short transactions need not wait behind long ones.
Concurrency control schemes – mechanisms to achieve isolation
that is, to control the interaction among the concurrent
transactions in order to prevent them from destroying the
consistency of the database
Analysis of conflicting operations
Locking – of records, tables
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 10
Schedules
Schedule – a sequence of instructions that specify the chronological
order in which instructions of concurrent transactions are executed
a schedule for a set of transactions must consist of all instructions of
those transactions
must preserve the order in which the instructions appear in each
individual transaction
A transaction that successfully completes its execution
will have a commit instruction as the last statement
by default, transaction assumed to execute commit instruction as its
last step
A transaction that fails to complete its execution
will have an abort instruction as the last statement (rollback command)
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 11
Schedule 1
Let T1 transfer $50 from A to B, and T2 transfer 10% of the
balance from A to B.
A serial schedule in which
T1 is followed by T2:
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 12
Schedule 2
A serial schedule where T2 is followed by T1
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 13
Schedule 3
Let T1 and T2 be the transactions defined previously.
The following schedule is not
a serial schedule
but it is equivalent to
Schedule 1 (serial schedule).
In Schedules 1, 2 and 3,
the sum A + B is preserved.
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 14
Schedule 4
The following concurrent schedule does not preserve the value of
(A + B).
These changes to A
will be discarded by
write(A) in T1
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 15
Conflict Serializability (Cont.)
Schedule 3 can be transformed into Schedule 1, a serial
schedule where T2 follows T1, by a series of swaps of nonconflicting
instructions.
Therefore Schedule 3 is (conflict) serializable.
Schedule 3 Schedule 1
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 16
Schedule 5
Example of a schedule that is not (conflict) serializable:
We are unable to swap instructions in the above schedule to obtain
either the serial schedule < T3, T4 >, or the serial schedule < T4, T3 >.
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 17
Weak Levels of Consistency
Some applications are willing to live with weak levels of consistency,
allowing schedules that are not serializable and recoverable
E.g.
a read-only transaction that wants to get an approximate total
balance of all accounts
database statistics computed for query optimization can be
approximate
Such transactions need not be serializable with respect to other
transactions
Tradeoff accuracy for performance
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 18
Levels of Consistency in SQL-92
Consistency levels (from highest to lowest):
Serializable — default
Snapshot isolation — (not part of SQL-92) only committed records to be
read, reads must return the value present at the beginning of transaction;
better performance while retaining most of serializability.
Repeatable read — only committed records to be read, repeated reads of
same record must return same value.
However, a transaction may not be serializable:
it may find some new records inserted by a committed transaction.
Read committed — only committed records can be read, but successive
reads of record may return different (but committed) values.
Read uncommitted — even uncommitted records may be read.
Lower degrees of consistency useful for gathering approximate information
about the database
Warning: some database systems do not ensure serializable schedules by
default
Phantom
records
Levels of Consistency
Snapshot isolation does not mean serializable!
Example:
One transaction turns each of the white marbles into black marbles.
The second transaction turns each of the black marbles into white marbles.
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 19
BKM_DATS, Vlastislav Dohnal, FI MUNI, 2022 20
Transaction Definition in SQL
Data manipulation language must include a construct for specifying the
set of actions that comprise a transaction.
A transaction begins implicitly.
Some systems may use begin to start a new transaction
A transaction ends by:
Commit: commits current transaction and begins a new one.
Rollback: causes current transaction to abort.
Often, SQL statement also commits implicitly if it executes successfully
Mainly when libraries are used to access database.
Implicit commit can be turned off
E.g., in JDBC, connection.setAutoCommit(false);