DETAILED INF3703 Summary (Databases II)

Detailed
INF3703
Summary

,Note: The box with the ‘red E’ means it is an exam
question and the box with the ‘yellow A’ means it was
an assignment question in 2019 1st semester. For each
‘red E’ it means it was asked in an exam, thus multiple
of them mean it was asked in multiple exams.


Chapter 10 - Transaction Management and
Concurrency Control
DB Transaction
DB Transaction: Represent real-world transactions (instance of
buying or selling) that are triggered by events such as buying a
product, registering for a course ,etc.


A scenario when a sale is made have the following parts:




IN DB terms a transaction is any action that reads or writes to a DB. It may
consist of the following:

,Transaction Definition: A transaction is a logical unit of work that must be
entirely completed or entirely aborted; no intermediate states are acceptable.
e.g. in the previous scenario which is a multicomponent transaction must not
be partially completed. All of the SQL statements in the transaction must be
completed successfully.
If any of the SQL statements fail, the entire transaction is rolled back to the
original DB state.
A consistent database state is one in which all data integrity constraints are
satisfied.
Every transaction must begin with the DB in a known consistent state. All
transactions are controlled and executed by the DBMS to guarantee DB
integrity.


A DB request is the equivalent of a single SQL statement in an application
program or transaction.


Evaluating Transaction Results:
Not all transactions update the DB. Remember a SELECT query access from the
DB and an access of the DB is a transaction.
So if the DB existed in a consistent (acceptable state – my own wording of
consistent state)) state before the SELECT then after the SELECT it will still exist
in a consistent state after the SELECT because the data inside DB hasn’t been
altered.
Transaction may consist of single or multiple SQL statement.



Say a sale has been made, then to do all of this:




The SQL statements will look like this:

,The result of the transaction are shown in red:

,The transaction is defined by the user or programmer and the DBMS cannot
guarantee that the programmer coded in the transaction correctly. i.e. the
DBMS cannot evaluate whether the transaction represents the real-world
event correctly.


Transaction Properties
 Each individual transaction must have the following properties (also
E referred as the ACID test): In exem list and explain them and give
examples where necessary. *have to alter this a bit to include
examples and better explanation
o Atomicity: requires that all operations (SQL statements) of a
E
transaction be completed, if not the transaction must be aborted.
o Consistency: A transaction takes a DB from one consistent state to
another. (Indication of the permanence [permanent] of a DB’s
consistent state)
o Isolation: The data used during the execution of a transaction
cannot be used by a 2nd transaction until the 1st one is complete
o Durability: ensures that once transaction changes are done and
commited, they cannot be undone or lost
 Another important property which applies when multiple transactions
are executed concurrently is serializability which ensures that the
schedule for the concurrent execution of the transaction yields consistent
results. i.e. it ensures that concurrent transaction operations creates the
same final DB state that would have been produces if the transactions
had been executed in a serial fashion
With single user DBMS serializability is auto. ensured.
Think concurrent, happens at same time thus in series, which is serial



Transaction Management with SQL:
o Transaction support is provided by the SQL statements:
COMMIT and ROLLBACK

, o When a transaction is initiated, the sequence must continue
through all succeeding SQL statements until one of the
following happen:




o A transaction begins implicitly when the 1st SQL statements is
encountered, but some SQL implementation it must be coded
e.g:



The Transaction Log:
 A DBMS uses a transaction log to keep track of all transactions that
update the DB.
 The DBMS uses the info. stored in this log for a recovery requirement
triggered by a ROLLBACK statement, a program’s abnormal termination,
or a system failure.
 Transaction log stores the following:




 Here is table of simple transaction consisting of 2 SQL statements. The
transaction log is usually implemented as one or more files that are
managed separately from the actual DB files.

, PTR points to the previous Transaction ID and to the next Transaction ID.



Concurrency Control:
E
 Coordinating the simultaneous execution of transactions in a multiuser
E DB system is known as concurrency control.
E  Objective of concurrency control is to ensure the serializability of
transactions in a multiuser DB environment.
E  Concurrency control is important because the simultaneous execution of
transactions over a shared DB can create several data integrity and
consistency problems such as lost update, uncommitted data and
inconsistent retrievals.
 The 3 main problems cause by simultaneous execution of transactions
are: Think LUI
 Lost updates:
Caused when two concurrent (simultaneously) transactions
T1 and T2 are updating the same data element and one of
the updates is lost (overridden by the other transaction.
E.g.:
Say we have Transaction T1 and T2. T1 updates PROD_QOH
(table PRODUCT’s attribute) by 100 and T2 updates it by -
35. Also, say initially PROD_QOH is 35
Given by the table:

, Suppose that a transaction can read a product’s PROD_QOH
before a previous transaction has been committed.
Now T1 has not been COMITTED when T2 is executed. Thus,
T2 still operates on the value 35 and it subtraction yields 5
in memory. So T1 writes 135 to the disk, which is promptly
overwritten by T2 which writes 5 to the disk. This table
displays this: Note: T1 and T2 also reads.




 Uncommitted data:
A Occurs when T1 and T2 are executed concurrently and the
1st transaction T1 is rolled back after the 2nd transaction T2
has already accessed the uncommitted data, thus violating
the isolation property of transaction.
Suppose T2 reads data that was updated by T1, but T1 was
rolled back, thus the data was not supposed to be read.
This is how it should be:




Incorrect way (i.e. the roll backed or uncommitted data is
read) :

,  Inconsistent retrievals:
Occur when a transaction accesses data before and after
one or more other transactions finish working with such
data. Here T2 updates values while T1 is calc. total.
Example if T1 calculated some summary function over a set
of data while another transaction T2 was updating the same
data. Problem is T1 will read data before some data is
changed and after some of the data is changed, thus
yielding inconsistent results.




Suppose T2 represents updating of ‘1546-QQ2’ by
+10 and updating ‘1558-QW1’ by -10. So they are correcting mistakes
that were made with these updates. This table represents the correct
total:

, Here the updates are made before the total was calculated.
Here we have the incorrect one:




Here 23 is added before the subtraction was made.


The scheduler:
 The scheduler is a special DBMS process/component that establishes the
order in which the operations are executed within concurrent
transactions.
 The scheduler interleaves the execution of DB operations to ensure
serializability and isolation of transactions.
 Not all transactions are serializable, which means that they can occur at
the same time and still yield results which would be the same as if they
would have occurred one after each other.