10IntroductionToTransactionProcessing(1)

Introduction to Transaction Processing

Outline

An interesting experiment
Where is a problem ?
Principles of transaction processing
Update synchronisation
ACID properties
Protocols

We start from an interesting experiment. First, start pgAdmin4 and create to connections to PostgreSQL server.

You can use any PostgreSQL server. It is important that you create two different connections and you use each c

onnection to accesse the same server. An experiment cannot be perfromed over connection and two tabs with psql

command line interface.

Next, use the connections you created in the previous step to and connect to the same database server as the same user.

Next, in each connection start psql command line client in the following way.

$psql -h localhost -U your-user-name -e

You will get two tabs/windows with the connections to thesame database server as the same user.

Next, start the transactions and set isolation level to READ COMMITTED in each window in the following way.

BEGIN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

An interesting experiment

Use psql to create two simultaneous connections to the same user account

$psql -h localhost -U jrg -e

	                                $psql -h locahost -U jrg -e jrg

Next, start the transactions in each connection and set READ COMMITTED isolation level for both transactions

  
jrg=> BEGIN;
jrg=*> SET TRANSACTION ISOLATION LEVEL
                        READ COMMITTED;
                                        jrg=> BEGIN;
                                        jrg=*>SET TRANSACTION ISOLATION LEVEL
                                                               READ COMMITTED;

An interesting experiment

Next, process the same SELECT statement in both connections

  
jrg=*> SELECT COUNT(*) FROM DEPARTMENT;
 count
-------
      8
(1 row)

  
                                        jrg=*> SELECT COUNT(*) FROM DEPARTMENT;
                                         count
                                        -------
                                              8
                                        (1 row)

Obviously, the results are the same

Now, we insert a row into a relational table DEPARTMENT through one of the connections.

INSERT INTO DEPARTMENT VALUES('IT','SCIT',5,'James Krook',1000);

If insert statement is processed successfully then a relational table DEPARTMENT must have 9 rows.

To confirm it we process in a connection used for insertion of a new row SELECT statement

that counts the total number of rows in a relational table DEPARTMEMT

SELECT COUNT(*)FROM DEPARTMENT;

We get the following result.

count

-------

(1 row)

As we might expect the result is 9 because previously it was 8 and we inserted one new row.

We count the total number of rows in a relational table DEPARTMENT visible through another connection,

i.e. a connection that has not been used to insert a new row.

We get the following result.

count

-------

(1 row)

Surprise, surprise, ... the result of counting is 8 !

It means that the other connection does not know anything about a row inserted through the first connection.

Why ? Is it correct ? Or maybe it is just a bug in the system ? Well, it is not a bug in the system.

The result is actually correct.

Note that insertion performed through the first connection is not committed yet.

So, theoretically the following scenario is still possible.

If uncommitted insertion performed in the first connection is visible in through second connection then it may

happen that insertion is rolled back in the first connection. Then a user in the second connection is left with

incorrect information (9) about the total number of rows in a relational table.

It is because rollback performed through the first connection is not reported in the second connection.

Of course, someone may ask what if insertion performed through the first connection is immediately committed ?

Then, counting of the rows through the second connection returns the result 9. It is because after commit

it is impossible to roll it back. So, the present contents of the relational table can be visible through

the second connection.

An interesting experiment

Now, we use one of the connections to INSERT a row into a relational table DEPARTMENT

And now repeat the same SELECT statements in both connections

jrg=*> SELECT COUNT(*)FROM DEPARTMENT;
 count
-------
      9
(1 row)

  
	                                 jrg=*> SELECT COUNT(*) FROM DEPARTMENT;
                                          count
                                         -------
                                               8
                                         (1 row)

Surprise, surprise, the results are different ! Why ?

Introduction to Transaction Processing

Why an insertion performed through the first connection is not visible through the second connection?

An answer is quite simple, an insertion performed through the first connection is not committed yet.

An uncommitted insertion can be rolled back and because it cannot be visible through the second connection yet.

Another interesting question is: is it correct that we must see through the second connection all modifications

performed by through the first connection ? Yes, it is correct but the modifications performed through

the first connection must be committed to be visible through the second connection.

What if a modification performed through the first connection is immediately visible through the second connection

and after that the modifications are rolled back in the first connection ? Well, in such situation we get a corrupted view of

database through the second connection. A user accessing a database through the second connection is left with a view of incorrect data !

Hence, we may say that only committed data can be revealed to the other users. Is such conclusion always true ?

Well, commit operation takes some time. For example, commit of a long update transaction that modifies a lot of rows in

a relational table may take a lot of time and it may delay processing of a database application. So, sometime it is worth

to risk a bit an reveal updated data before commit assuming that commit would happen anyway later on.

In a general case concurrent processing of database transaction on a multiuser database system requires

a time-sharing operating system and the properties of such operating system. In a time-sharing operating system

the individual users are granted short time-slots for processing their applications. In the other words, time is shared

by the users. As a consequence processing of a single database transaction may not be entirely processed from start

to commit/rollback point in one time slot. A transaction is started when an operation system user obtains a time slot

and it is stopped when time slot expires. Then another user obtains a time slot fro processing of its application.

And so on and so on. As a consequence a database transactions are usually processed over many time slots by

a time-sharing operating system and because of that database transactions interleave their operations.

As we shall show in the future such uncontrolled processing of database transactions interleaving their operations o

ver shared data may lead to corruption of a database.

On the other hand processing of database transactions serially one-by-one is not realistic solution due to very poor

performance and response time. In the modern on-line electronic business systems response time is one of the most

important factors determining a quality of a system.

Hence, an objective of concurrent processing of database transaction on shared data needs the most efficient synchronisation

method for a set of concurrent processes accessing the shared database resources.

Now, we can formulate the following problem statement.

Given a multiuser database system, find the most efficient synchronisation method for a set of concurrent processes

accessing the shared database resources.

Where is a problem ?

Why a modification performed by the first user is not visible to the second user ?
Is it correct that the second user must see all modifications performed by the first user ?
What if a modification performed by the first user is immediately visible to the second user and after that the first user rolls back the modification ?
Then, the second user is left with incorrect data !
Hence, only committed data can be revealed to the other users
Is such conclusion always true ?
Problem statement
- Given a multiuser database system
- Find the most efficient synchronisation method for a set of concurrent processes accessing the shared database resources

Introduction to Transaction Processing

Outline

An interesting experiment
Where is a problem ?
Principles of transaction processing
Update synchronisation
ACID properties
Protocols

We shall start from a very simplified data model (similar to key-value pair data model) where a database consists of

data items each located at a unique address. Data items do not have any internal structure and can access with read

and write operations. So, for a while we shall forget about relational tables, rows, attributes, values of attributes and SQL.

We shall access a database with the operations like a=read(x) where x is a unique address of a data item

(we shall call it later on as a data item x) and a is a transient memory (RAM) variable.

A value of data item x is stored in a after reading it from persistent memory.

A can be considered as an ordinary variable of an atomic type (int, float, double, ...) available in

any general purpose procedural programming language.

The second operation used to transfer values to a database is an operation write(x, a) that takes the value of a transient variable (RAM) a

and writes it as a value of persistent storage data item x.

Two remaining operations used are commit and rollback.

An operation abort is a database scheduler operation that stops and automatically rollbacks a transaction.

A transaction is defined in such a data model as "a partially ordered set of read, write operations on the database items" .

Why it is a partially ordered set and no linearly ordered set of database operations ? It is because at the very and

a transaction is an ordinary computer program and its processing depending on input data and an environment it is processed in.

So, depending on input data a computer program may process through one sequence of operations while for different input data

it may process through another sequence of operations. It is why a computer program is a partially ordered set of operations

(see a definition of "partial order" in math for computing subject). In our case we shall use only read, write, commit, rollback

and abort operations.

Users interact with a database by executing programs. it simply means that at the every end a transaction is a computer program,

for example JDBC (Java + SQL) program.

Execution (processing) of a program is equivalent to execution of a partially ordered set of read, write operations.

Well, if a transaction is a partially ordered set of operations than execution of a transaction must be equivalent to

processing the operations along one of the possible execution paths.

A database is visible to transactions as a collection of data items. we have already talked about that.

Just atomic data items, each one with a unique address and single atomic value.

Concurrently running transactions interleave their operations. It is because database systems are almost always installed

on top if time-sharing operation systems.

Transactions have no impact on execution of their operations. Execution is a sequence of operations and transaction has

no impact on processing of the operations.

Each transaction terminates by either commit or abort operation. We have already discussed the operations of database transactions.

Each transaction arrives at a consistent database state and must leave a database in a consistent state as well.

It means that correctly implemented transactions MUST NOT corrupt a database.

Principles of transaction processing

A partially ordered set of read, write operations on the database items is called as a transaction
Users interact with a database by executing programs
Execution of a program is equivalent to execution of a partially ordered set of read, write operations
A database is visible to transactions as a collection of data items
Concurrently running transactions interleave their operations
Transactions have no impact on execution of their operations
Each transaction terminates by either commit or abort operation
Each transaction arrives at a consistent database state and must leave a database in a consistent state as well

Now, we show a sample concurrent processing of database transactions that corrupts a database.

Assume that we have two users of a banking system and both users would like to operate on the same bank account x

such that at the moment $100 is deposited on the account. Assume that one user would like to withdraw $10 from

he account and the other user wants to deposit $20 at the account. The users interact with a database through

execution of database transactions. Assume that execution of database transactions is not controlled in

any possible way by a database systems.

Processing of the transactions is visualized as a the sequence of operations written vertically in two separate columns.

Each column represent execution of a transaction. If a diagram consists of two columns than it visualizes

a concurrent processing of two transactions. The time axis goes down from top to bottom of a diagram.

A single line in a diagram represent a time slot when one of the transactions executes its operation.

No two operations of the same or different transactions can be executed in the same time slot.

It means that we visualize concurrent execution and not parallel execution.

The first transaction reads the present state of a bank account a=read(x) and saves a value 100 in a transient variable a.

In the next time slot, the second transaction also reads a value of data item x and saves the value in a transient variable b.

The first user supposed to withdraw $10. Hence the first transaction deducts 10 from a value in a transient variable a

and writes a value of a into a data item x. At the end of execution of write(x,a-10) a data item x has a value 90.

The second user supposed to deposit $20. Hence the second transaction adds 20 to a value of a transient variable b (120)

and it writes a value of b into a data item x. At the end of execution of write(x,b+20) a data item x has a value 120.

Finally the transactions commit their operations. The total amount of money on a bank account is $120.

Well, a bank would not be happy because $10 is lost (100-10+20=110). It is why in 1980s of the previous century

the banks invested a lot of money into research on database transactions to avoid such situation.

Sometimes, I call this example "how to become a millionaire", but ... the chances for that are very very slim.

Our sample concurrent processing of database transactions shows that uncontrolled concurrent processing of database transactions

may corrupt a database.

Principles of transaction processing

A sample concurrent processing of database transactions


T1                      T2                     x: $100

a=read(x)                                      x: $100 a: $100

                        b=read(x)              x: $100 b: $100

write(x,a-10)                                  x: $90  a: $100

                        write(x,b+20)          x: $120 b: $100

                        commit                 x: $120

commit                                         x: $120

If a state of a bank account is $100 then withdrawal of $10 and deposit of $20 cannot change a state of bank account to $120
Uncontrolled concurrent processing of database transactions may corrupt a database

Introduction to Transaction Processing

Outline

An interesting experiment
Where is a problem ?
Principles of transaction processing
Update synchronisation
ACID properties
Protocols

It is an illusion that a problem presented on the previous slide can be solved through delaying updates

on a bank account to a point where the transactions commit their operations.

Yes, indeed a database transaction can perform update in two different ways:

(1) A transaction immediately writes uncommitted values into a database, it is so called "update-in-place"

(2) A transaction does not modify a database until the time it commits itself, it is so called "deferred-update"

In the last example the transactions applied "update-in-place" to modify a database because the updated values

were immediately written to a database.

In the next slide we show that a way how database transactions perform a "deferred-update").

We show that it has no impact on the final outcomes. It meabs that when a deferred-update is applied a database

still gets corrupted.

Update synchronisation

Database transaction can perform update in two different ways:
- A transaction immediately writes uncommitted values into a database - update-in-place
- A transaction does not modify a database until the time it commits itself - deferred-update
In the last example the transactions applied update-in-place to modify a database
A way how the transactions perform an update has no impact on the final outcomes, e.g. when deferred-update is applied a database still gets corrupted (see the next example)

In the present slide we apply a "deferred-update" strategy to modify a database. "Deferred-update" means that

the modifications of database contents are written to a log and a database is permanently changed by reading log at commit point.

Like before the first transaction reads a value 100 into a transient variable a. Then the second transaction reads 100 into a transient variable b.

Then, the first transaction deducts 10 from a and writes 90 into persistent storage, but .... this time a value 90 is written to a log file

and not into a data item x.

Next the second transaction increases a value in b by 20 and write 120 into persistent storage.

Like the first one, 120 is written into a log file.

however in a different way than before. The second transaction retrieves the final value of x from a log file and

it writes such value into a data item x.

The first transaction, retrieves the final vale of x from a log file and writes it into a data item x.

Still a database is corrupted and the only difference is that we loose $10 and not a bank.

If a state of a bank account is $100 then withdrawal of $10 and deposit of $20 cannot change a state of bank account to $90.

"Deferred-update" technique does not solve the problem.

So, now we can see that completely uncontrolled processing of database transactions over shared data may corrupt a database.

It is very important that processing is performed over "shared" data. If database transactions operate on disjoined sets of data items

then corruption of a database never happens. Such property of concurrent processing of database transaction provides an opportunities

for some interesting solutions of the problems that will be discussed in the future.

To solve the problem, we have to make a number of assumptions about the ways how database systems execute database transactions.

We shall do it in the next section of this presentation.

Update synchronisation

A sample concurrent processing of database transactions when deferred-update is applied


T1                      T2                     x: $100

a=read(x)                                      x: $100 a: $100

                        b=read(x)              x: $100 b: $100

write(x,a-10)                                  x: $100 log T1:$90

                        write(x,b+20)          x: $100 log T2:$120

                        commit                 x: $120

commit                                         x: $90

If a state of a bank account is $100 then withdrawal of $10 and deposit of $20 cannot change a state of bank account to $90
Deferred-update does not solve the problem

Introduction to Transaction Processing

Outline

An interesting experiment
Where is a problem ?
Principles of transaction processing
Update synchronisation
ACID properties
Protocols

Now, we try to set up few principles for concurrent processing of database transactions.

We make four assumptions about how database systems execute database transactions.

Processing of database transactions must satisfy so called A C I D properties.

The first property of execution of database transactions is atomicity. Atomicity stands for A in ACID properties.

Atomicity means that each database operation is treated as a single unit (all-or-nothing). It means that a database operation cannot be

partially processed. It is completely processed or it is not processed at all. You may say that write operation cannot partially processed.

But in the future we shall talk about transaction processing with SQL on relational databases. Then INSERT, UPDATE, DELETE are treated as

atomic operations. In a context of SQL atomicity means that, for example, UPDATE statement cannot be partially processed, such that some

of the rows are updated, while the other rows that supposed to be updated are not.

The second property of execution of database transactions is consistency. Consistency stands for C in ACID properties.

Consistency means that concurrent processing of database transactions must take a database from one consistent state to another.

In the other words, database transactions cannot corrupt a database.

The third property is isolation. Isolation stands for I in ACID properties. Isolation means that database transactions do not

directly communicate one with each other and they do not read the intermediate results of the other transactions.

Synchronization of database transactions cannot be implemented through any message exchange protocols.

The fourth property (the last one) is durability. Durability stands for D in ACID properties. Durability means that the results of

committed transactions must be permanent in a database in spite of failures. No matter happens to a database system the results of

by a transaction the all modifications done by the transaction must be permanently recorded in a database.

The database systems whose transaction processing systems satisfy A C I D properties explained above are call as ACID-compliant database system.

ACID properties set some sort of a standard for transaction processing systems.

ACID properties

Processing of database transactions must satisfy ACID properties
Atomicity
- Each database operation is treated as a single unit (all-or-nothing)
Consistency
- A transaction takes a database from one consistent state to another
Isolation
- Transactions do not directly communicate one with each other and they do not read the intermediate results of the other transactions
Durability
- The results of committed transactions must be permanent in a database in spite of failures

Introduction to Transaction Processing

Outline

An interesting experiment
Where is a problem ?
Principles of transaction processing
Update synchronisation
ACID properties
Protocols

A protocol is a way how a transaction processing system enforce ACID properties.

There are two types of protocols: execution atomicity protocol and failure atomicity protocol.

An execution atomicity protocol ensures Consistency property of ACID. Execution atomicity protocol is responsible for keeping

the contents of a database logically consistent.

A failure atomicity protocol ensures Atomicity, Isolation and Durability from ACID properties.

An example of concurrent processing where the final state of bank account is incorrect is a case

where execution atomicity fails (please see a visualization of concurrent processing in the present slide).

The final state of a database is such that it would not be achieve in any of two serial executions of database

transactions T1 and T2. It means that the contents of a database are inconsistent and it is why execution atomicity

is violated.

Protocols

An execution atomicity protocol ensures Consistency property
A failure atomicity protocol ensures Atomicity, Isolation and Durability properties

A sample incorrect execution atomicity protocol


T1                           T2                     x: $100

a=read(x)                                           x: $100 a: $100

                             b=read(x)              x: $100 b: $100

write(x,a-10)                                       x: $90  a: $100

                             write(x,b+20)          x: $120 b: $100

                             commit                 x: $120

commit                                              x: $120

This slide shows a visualization of sample concurrent execution of database transactions where failure atomicity protocol returns

incorrect results, i.e. it corrupts a database.

Like before assume that we have a bank account with $100. A transaction T1 withdraws $10 and a transaction T2 deposits $20.

A transaction T1 reads a value in data item x (10) into transient memory variable a. Then it deducts 10 from a value in a and

it writes 90 as a value in data item x. So far so good.

A transaction T2 reads a value in a data item x (90) into a transient variable b. Then, it increases a value in b by 20 and

it writes 110 into a data item x. Next a transaction t2 commits itself. A data item x has a value $110. So far so good.

Now a transaction T1 is aborted. For example a software implementing a transaction T1 has a bug, division by zero, etc.

T1 must be rolled back. When T1 is rolled back it must reverse all its modifications performed to a database.

It means that value in a data item x must be reversed to 100. Such reversal wipes out the outcomes of a transaction T2.

It violates Durability and Consistency principles.

If a state of a bank account is $100 then withdrawal of $10 and deposit of $20 cannot change a state of bank account to $100.

It means that a special failure atomicity protocol is needed to handle the failures of transactions in a correct way.

Protocols

A sample incorrect failure atomicity protocol


T1                             T2                      x: $100

a=read(x)                                              x: $100 a: $100

write(x,a-10)                                          x: $90  a: $100

                              b=read(x)                x: $90  b: $90

                              write(x,b+20)            x: $110 b: $90

                              commit                   x: $110

abort                                                  x: $100

If a state of a bank account is $100 then withdrawal of $10 and deposit of $20 cannot change a state of bank account to $100

Protocols

Execution atomicity protocol = Concurrency control protocol
Failure atomicity protocol = Recovery protocol

Lost update problem


T1                        T2                     x: $100

a=read(x)                                        x: $100 a: $100

                          b=read(x)              x: $100 b: $100

write(x,a-10)                                    x: $90  a: $100

                          write(x,b+20)          x: $120 b: $100

                          commit                 x: $120

commit                                           x: $120

Yet another interesting anomaly in a concurrent processing of database transactions is demonstrated in the present slide.

We consider a concurrent execution of two database transactions T1 and T2. The transactions operate on the persistent data items x and y.

The values in data items x and y are 100 and 50 respectively.

A transaction T1 reads a value in data item x into a transient variable a.Then a transaction T2 reads a value in a data item y into

a transient variable b.

Next, a transaction T1 deducts 10 from a and saves a new value in a transient variable a (90) in a data item x.

Next, a transaction T2 reads a new value of data item x (90) into a transient variable c. Please note that at this point

a transaction T2 has a correct view of the contents of data items x and y (90 and 50).

Next, a transaction T1 changes a value in a data item y to 100 - 30 = 70.

Finally, a transaction T2 adds the values of the transient variables b and c and prints the result (140).

A functionality of a transaction T2 is to find a value of x + y. Obviously the result is incorrect because

a value in a data item y comes from a different moment in time than a value in a data item x. At the moment x + y = 160.

A transaction T2 supposed to read both data items in the same moment in time to display a correct result of x + y.

Such case is commonly called as "inconsistent retrieval".

Protocols

Inconsistent retrieval problem


T1                    T2                   x        y        a         b        c

a=read(x)                                  100      50       100

                      b=read(y)            100      50       100       50

write(x,a-10)                              90       50       100       50

                      c=read(x)            90       50       100       50       90

write(y,a-30)                              90       70       100       50       90

                      print(b+c)140        90       70       100       50       90

References

T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 22.1 Transaction Support, Chapter 22.2 Concurrency Control, Pearson Education Ltd, 2015