11IntroductionToTransactionProcessing(2)

Introduction to Transaction Processing

Outline

Correctness
Conflict serializability versus view serializability
Order preserving conflict serializability
Recoverable executions
Cascadeless executions
Strict executions

If we would like to solve a problem of correct execution of concurrent database transactions then we must start from an answer to a question:

What makes concurrent execution of database transaction incorrect ? or from an answer to a question:

How do we define a correct concurrent execution of database transactions ?

To find a correctness criterion for a concurrent execution of database transactions we have to find an execution that is always correct and apply the properties of such execution to a definition of correctness.

Which concurrent execution is always correct ? If we assume that the transactions are written in a correct way then an execution where the transactions are processed serially (one-by-one) is always correct. Why ? It is because the transactions do not interleave their operations and process all operations one at a time till either commit or rollback operation. So, if a transaction is implemented in a correct way, i.e. a way that does not corrupt a database, then a serial execution of correctly written transactions must be correct. If it is correct than a state of a database after serial processing of database transactions is correct. Hence any concurrent and not serial execution that brings a database to the same state as one of serial executions must be correct. it is how we invented the first definition of correctness fro concurrent execution of database transactions.

We say that a concurrent execution of database transactions is view serializable (correct) if there exists a possible serial execution of the same set of transactions such that in both executions each transaction reads the same values and the final states of the database are the same. It simply means that in such a case it is impossible to distinguish between the outcomes of a concurrent execution and one of the serial executions.

Please note that our first definition of correctness does not determine well which serial execution is used to compare with a concurrent execution. We say, any serial execution will do. If a database is brought to the same state and each transaction reads the same data items then it is the same as one of serial execution.

The final conclusion is such that a concurrent execution of database transactions is correct when it is view serializable.

Correctness

What makes concurrent execution of database transaction incorrect ?
How do we define a correct concurrent execution of database transactions ?
Concurrent execution of database transactions is view serializable if there exists a possible serial execution of the same set of transactions such that in both executions each transaction reads the same values and the final states of the database are the same
A concurrent execution of database transactions is correct when it is view serializable

in the next two slides we show how to apply a definition of correctness based on view serialzability to verify correctness of concurrent execution of database transactions.

Consider a sample concurrent execution of database transactions. We shall show that the execution is view serializable, i.e it is correct accordingly to a definition of correctness based on view serializability.

To show that a concurrent execution is view serializable we must find at least one serial execution such that a state of a database after concurrent execution is the same as after a serial execution and each transaction in concurrent execution reads the same values as in a serial execution. If only two transactions T1 and T2 are involved in a concurrent execution then only two serial executions are possible: T1 before T2 and T2 before T1.

We find that both data items x and y are read by a transaction T2 after each one of them was updated by a transaction T1. Such observation suggest verification of a serial order T1 before T2 first. Then both b=read(x) and d=read(y) return the same values as in a concurrent execution. A transaction t2 does not change the contents of a database, hence after a serial processing T1 -> T2 the contents of a database is the same as in a concurrent execution.

Hence, the execution of database transactions is view serializable because there exists a serial execution of the same transactions (T1-> T2) such that in both executions the transactions read the same values and the final states of the database are the same (see next slide).

It is interesting that the second serial execution T2 before T1 cannot be used to prove view serializability condition because in such a case a transaction T2 would read different values from the values read n a concurrent execution. But at the end if we find one serial execution that satisfies the conditions of view serializability then it is completely sufficient to claim that a concurrent execution is correct.

Correctness

A sample view serializable execution of database transactions


T1                                        T2

a=read(x)

write(x, a-10)

                                          b=read(x)

c=read(y)

write(y,c+10)

                                          d=read(y)

The execution of database transactions above is view serializable because there exists a serial execution of the same transactions such that in both executions the transactions read the same values and the final states of the database are the same (see next slide)

Correctness

A sample serial execution equivalent to a concurrent execution from the previous slide


T1                                 T2

a=read(x)

write(x, a-10)

c=read(y)

write(y,c+10)

                                   b=read(x)

                                   d=read(y)

A definition of correctness of concurrent execution of database transactions based on a concept of view serializability is based on a very simple assumption saying that serial execution of database transactions must be correct. It is why, it very clearly determine whether a concurrent execution is correct or not. Unfortunately such definition has a serious problem.

The problem is related to the computational complexity of testing view serializability criterion. If n transactions are involved in concurrent execution then in the worst case we have to check n! serial orders to answer a question about view serializability. It means that verification that concurrent execution of Database transactions is view serializable is NP-complete. It means that it takes to much time to check whether execution of a database operation violates view serializability correctness criterion.

So we need a more practical correctness criterion.

A more practical correctness criterion is "conflict serializability". A definition of conflict serializability is the following. A concurrent execution of database transactions is conflict serializable if there exists a possible serial execution of the same set of transactions such that in both executions the order of conflicting operations is the same. Then, concurrent execution of database transactions is correct if it is conflict serializable.

A definition of conflict serializability is based a concept of conflicting operations. Which operations are conflicting operations ?

Two operations are conflicting operations if both access the same data item and one or both of them is write operation. A table given at the bottom of the present slide visualizes a definition of conflicting operations. Two read(x) operations performed on the asme data item x do not conflict because the results of read do not depend on the order of operations. The operations read(x) and write(x) performed by the transactions T1 and t2 respectively provide different results depending on the order of operations. For example, if x = 5 then read(x) and later on write(3,x) returns a result 5different from write(3,x) and later on read(x) (3). The same applies to a pair of operations write(a,x) performed by T1 and later on write(b,x) performed by T2. An order write(b,x) and later on write(a,x) returns a different state of a database.

Correctness

A problem with view serializability
- Verification that concurrent execution of Database transactions is view serializable is NP-complete
- It means that it takes to much time to check whether execution of a database operation violates view serializability correctness criterion
A more practical correctness criterion is conflict serializability
- Concurrent execution of database transactions is conflict serializable if there exists a possible serial execution of the same set of transactions such that in both executions the order of conflicting operations is the same
Which operations are conflicting operations ?

Two operations are conflicting operations if both access the same data item and one or both of them is write operation

                        read                  write

           read         NO                    YES

           write        YES                   YES

Detection of conflicting operations in a concurrent execution of of database transactions is quite simple. Atv least one of the operations must be write(a,x) and both operations must act on the same data item. hence the following pairs of operations are conflicting: , .

In the first example a transaction T1 processed write(x, a-10) and later on a transaction T2 processed b=read(x). Both operations act on the same data item x and one of them is write. So, the operations are conflicting operations. One more thing that must be done when conflicting operations are identified. We must find an order of conflicting operations. In the present example a transaction T1 executes write(x, a-10) before a transaction T2 executes b=read(x). Hence an order is T1 before T2.

In the second example it is possible to find two pairs of conflicting operations and . In both cases and order of conflicting operations is the same: T1 before T2.

Correctness

Conflicting operations in a sample concurrent execution of transactions

	      
T1                                 T2

write(x, a-10)

                                   b=read(x)

The operations write(x, a-10) and b=read(x) are conflicting operations because both access the same data item x and one of them is write
The operations write(y,a+10) and write(y,10) are conflicting operations because both access the same data item y and both of them are write

now, when we know how to detect conflicting operations and transaction orderings determined by conflicting operations we canb retrun to a concept of conflict serializability, and correctness of concurrent execution of database transactions.

We said that concurrent execution of database transactions is conflict serializable (read: "correct") if there exists a possible serial execution of the same set of transactions such that in both executions the order of conflicting operations is the same.

Let us find whether a sample concurrent execution of the transactions T1 and T2 is conflict serializable (correct). To do so we have to find the conflicting operations executed by the transactions T1 and T2. We start from the first operation executed by a transaction T1: a=read(x). Such operation does NOT conflict with b=read(x) executed by T2 because bothe operations are read and it also does not conflict with d=read(y) because both operations act on different data items and both of them are read.

Next, an operation write(x, a-10) executed by T1 conflicts with b=read(x) executed by T2 and an order of conflicting operations is T1 before T2.

Next, an operation c=read(y) executed by T1 does not conflict with any operation executed by T2 due to a different data item used ( b=read(x)) and both operations being read operation.

Next, an operation write(y,c+10) executed by T1 conflicts only with an operation d=read(y) executed by T2. In this case an order of conflicting operations is again T1 before T2.

If an order of conflicting operations is T1 before T2 then when the transactions are executed serially T1 and then T2 an order of conflicting operations is the same. I means that we found a serial order of transactions where an order of conflicting operations is the same as in a concurrent execution. It is an evidence that a concurrent execution is conflict serializable (correct).

Correctness

Concurrent execution of database transactions is conflict serializable if there exists a possible serial execution of the same set of transactions such that in both executions the order of conflicting operations is the same

A sample conflict serializable execution of database transactions


T1                                    T2

a=read(x)

write(x, a-10)

                                      b=read(x)

c=read(y)

write(y,c+10)

                                      d=read(y)

Order of conflicting operations: T1 before T2

Now, we shall look at a concurrent execution of database transactions that corrupts a database. It is the same execution as presented at the very beginning of the previous presentation to demonstrate that uncontrolled processing of database transactions on shared date may lead to a corrupted database.

We look at the conflicting operation and the orders of conflicting operations. A transaction T1 executes a=read(x) and such operation conflicts with write(x,b+20) executed by a transaction T2. An order of conflicting operations is T1 before T2.

A transaction T2 executes b=read(x) and such operation conflicts with write(x,a-10) executed by a transaction T1. In this case and order is T1 before T2.

Finally an operation write(x,a-10) executed by T1 conflicts with write(x,b+20) executed by T2. An order of conflicting operations is T1 before T2.

We got two orders of conflicting operations T1 before T2 and T2 before T1. It is impossible to find a serial execution of T1 and T2 such that it would satsfy both orders of conflicting operations. It means that does not exists a serial order of execution where order of conflicting operations is the same as in a concurrent execution and because of that a concurrent execution is not conflict serializable. It is not correct.

Order of conflicting operations: T1 before T2 and T2 before T1 means that it is impossible to serialize the concurrent execution of T1 and T2 It means that the concurrent execution of database transactions T1 and T2 is incorrect.

Correctness

A sample not conflict serializable execution of database transactions


T1                          T2                    x: $100

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

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

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

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

                            commit                x: $120 a: $100 b: $100

commit                                            x: $120 a: $100 b: $100

Order of conflicting operations: T1 before T2 and T2 before T1 means that it is impossible to serialize the concurrent execution of T1 and T2
It means that the concurrent execution of database transactions T1 and T2 is incorrect

Introduction to Transaction Processing

Outline

Correctness
Conflict serializability versus view serializability
Order preserving conflict serializability
Recoverable executions
Cascadeless executions
Strict executions

It is possible to prove that every conflict serializable execution is view serializable.

On the other hand a view serializable execution may not be conflict serializable. it means that a definition of correctness based on conflict serializability is more restrictive than a definition of correctness based on view serializability,

A sample concurrent execution of three transactions T1, T2 and T3 visualized on the present slide is not conflict serializable but it is still view serializable.

The concurrent execution is not conflict serializable because write(x, 10) executed by T1 conflicts with write(x, 20) executed by T2 enforcing an order T1 before T2. An operation write(y,10) executed by T2 conflicts with a=read(y) executed later by T1 and enforcing an order T2 before T1. Does not exist a serial order of T1 and T2 where T1 is processed before T2 and T2 is processed before T1. Hence the sample execution is not conflict serializable.

An execution below is view serializable because there exists equivalent serial execution where each transaction reads and writes the same data items, see a trace on the right hand side of a diagram given in the present slide and given in the next slide.

Conflict serializability versus view serializability

Every conflict serializable execution is view serializable
A view serializable execution may not be conflict serializable

An execution below is view serializable because there exists equivalent serial execution where each transaction reads and writes the same data items, see next slide


T1                      T2                      T3

write(x, 10)                                                     x: 10

                        write(x, 20)                             x: 20

                                                 write(x,30)     x: 30

                        write(y,10)                              x: 30 y: 10

a=read(y)                                                        x: 30 y: 10 a: 10

Conflict serializability versus view serializability

Equivalent serial execution


T1                      T2                      T3

                        write(x, 20)                            x: 20

                        write(y, 10)                            x: 20 y: 10

write(x,10)                                                     x: 10 y: 10

a=read(y)                                                       x: 10 y: 10 a: 10

  		                               write(x,30)      x: 30 y: 10 a: 10

Hence, the original execution is view serializable

Conflict serializability versus view serializability

However, the original execution is not conflict serializable


T1                            T2                            T3

write(x, 10)

                              write(x, 20)

                                                            write(x,30)

                              write(y,10)

a=read(y)

This is because T1 processes a conflicting operation write(x, 10) before T2 processes write(x,20) and T2 processes a conflicting operation write(y, 10) before T1 processes a=read(y)
Hence, the execution is not conflict serializable

Introduction to Transaction Processing

Outline

Correctness
Conflict serializability versus view serializability
Order preserving conflict serializability
Recoverable executions
Cascadeless executions
Strict executions

A definition of conflict serializability says that a concurrent execution is conflict serializable if an order of conflicting operations in a concurrent execution is the same as an order of conflicting operations in one of the serial executions. Please note "...one of..." ! It may happen that due to operating system scheduling of time sharing processing a serial order is different from an order how the transaction started their concurrent execution. Then, if observe an order in which the transaction started then we may get a pretty confusing observation that the execution order was in fact different. If we assume that certain order of execution is needed to get the desired results than if real serial order is different then we may get undesirable results, still correct accordingly to conflict serialiizability, but not the correct results we wanted. To avoid such problem we strengthen a definition of conflict serializability to order-preserving conflict serializability.

Concurrent execution of database transactions is order-preserving conflict serializable if it is:

(1) conflict serializable and

(2) all non-interleaved transactions have the same order in both original execution and some corresponding serial execution.

Every order-preserving conflict serializable execution is conflict serializable.

On the other hand, a conflict serializable execution may not be order-preserving conflict serializable.

Order preserving serializability

Concurrent execution of database transactions is order-preserving conflict serializable if it is
- conflict serializable and
- all non-interleaved transactions have the same order in both original execution and some corresponding serial execution
Every order-preserving conflict serializable execution is conflict serializable
A conflict serializable execution may not be order-preserving conflict serializable

Order preserving serializability

A sample conflict serializable and order-preserving serializable execution


T1                            T2                            T3

                                                            write(y,10)

write(x, 10)

                              a=read(x)

write(y,20)

ORM

Order of transactions indicated by their start timestamps is the same as serialization order (T3 before T1 and T1 before T2)

Order preserving serializability

A sample conflict serializable and not order-preserving serializable execution


T1                            T2                            T3

write(x, 10)

                              a=read(x)

                                                            write(y,10)

write(y,20)

ORM

The execution is not order-preserving serializable because order of transactions indicated by their start timestamps (T1 before T2 and T2 before T3) is different from serialization order (T3 before T1 and T1 before T2)

Introduction to Transaction Processing

Outline

Correctness
Conflict serializability versus view serializability
Order preserving conflict serializability
Recoverable executions
Cascadeless executions
Strict executions

Transaction manager must provide the all-or-nothing property of transactions even in the presence of various types of failures. It means that if a transaction fails then all modifications performed by such transaction must be reversed. In the other words transaction manager must recover from a failure of a transaction with any impact the state of a database. A failure of a transaction must not corrupt a database.

As an example where a failure of a transaction corrupts a database is given in the present slide. A transaction T1 reads data items x and write a new value into data item x.

Then a transaction T2 reads the same data items x and it writes a new value of data item x and it commits modification of x. When committed such modification cannot be changed. Now, assume that a transaction T1 fails. If T1 fails then all its modifications of a database must be reversed. If a transaction T1 updated data item x first then reversal of its operations bring the original value of data item x. An in such way a modification of x performed by a transaction T2 is lost.

To avoid lost update due to a failure of a transaction T1 a concurrent execution must enforce a principle of recoverability. A principle of recoverability says that execution is recoverable if every transaction T that reads a data item written by another transaction T’ commits after T’ is committed.

A principle of recoverability is violated by the concurrent execution given on the present slide because a transaction T2 reads data item earlier written by a transaction T1 and a transaction T2 commits before a transaction T1 either commits itself or it fails.

For the concurrent execution to be recoverable a transaction T2 must wait with commit until a transaction T1 commits itself. If T1 fails then T2 must be aborted by a scheduler because T2 read dirty data created by T1. Such situation contributes to cascaded aborts of database transactions. Cascading abort is a negative effect of the protocol because it reverses processing of the transactions that would correctly process themselves otherwise.

An additional rule must be enforced to avoid cascading aborts.

Recoverable executions

Transaction manager must provide the all-or-nothing property of transactions even in the presence of various types of failures
Execution is recoverable if every transaction T that reads a data item written by another transaction T’ commits after T’ is committed

A sample not recoverable execution


T1                            T2                             x: $100

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

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

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

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

                              commit                         x: $110 a: $100 b: $90

abort                                                        x: $100 a: $100 b: $90

Introduction to Transaction Processing

Outline

Correctness
Conflict serializability versus view serializability
Order preserving conflict serializability
Recoverable executions
Cascadeless executions
Strict executions

To avoid cascading abort a transaction must not read dirty data items, i.e. data items modified by uncommitted transactions. Such principle enforces a "cascadles concurrent execution".

A concurrent execution is cascadeless if none of the transactions reads data item written by any other transaction that is not committed or aborted.

A sample cascadeless execution is visualized on the present slide.

Like before a transaction T1 reads and writes a data item x. Next, a transaction T2 attempts to read a data item x created by a transaction T1. According to cascadeless execution principle a transaction T2 must wait until a transaction T1 commits itself or aborts. If T1 is able to commit all its modifications then T2 is allowed to read and to update a data item x. Otherwise, if T1 fails then it is rolled back, the contents of a data item x returns to its original value and then T2 is allowed to read and to update it.

Cascadeless executions

Execution is cascadeless if none of the transactions reads data item written by any other transaction that is not committed or aborted

A sample cascadeless execution


T1                          T2                             x: $100

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

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

                            b=read(x) wait                 x: $90  a: $100

abort                                                      x: $100 a: $100

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

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

The execution visualized in the present slide is not a cascadeless execution. A transaction T1 reads and modifies a data items x. Then a transaction T2 reads a new value of data item x and again modifies the new value of data item x. The execution is recoverable and a transaction T2 is not allowed to commit a modification of data item x.

Now, a transaction T1 fails and it rolled back. A value written by a transaction T1 becomes a dirty data. A transaction T2 used a dirty data written by a transaction T1. If T1 is aborted then a transaction T2 must be aborted creating the first level of potential cascade of aborts. If a transaction T2 modified another data item and such data item has been read by other transactions then a forced abort of a transaction T2 triggers the forced aborts of the other transactions. And so on, and so on.

Cascadeless executions

A sample not cascadeless execution


T1                            T2                           x: $100

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

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

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

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

abort                                                      x: $100         b: $90

                              forced abort                 x: $100

Introduction to Transaction Processing

Outline

Correctness
Conflict serializability versus view serializability
Order preserving conflict serializability
Recoverable executions
Cascadeless executions
Strict executions

What if a data item is read and its modified by a transaction T1, and then a transaction T2 overwrites a data item with a new value. Then such execution is still recoverable and it is still cascadeless. But, when a transaction T1 is rolled back then a new value of data item x is again overwritten by old value of data item x (before it has been changed by a transaction T1). It means that the principles of recoverability and cascadeless execution are not strong enough to eliminate the problem.

We have to add one more principle of so called strict execution.

We say that an execution is strict if any data item d is written by transaction T then any other transaction cannot either read or write data item d until T is committed or aborted.

A sample strict execution is visualized in the present slide. A transaction T1 read and later on writes a new value of data item x. Then a transaction T2 attempts to overwrite a data item x with a new value. A strict execution protocol does not allow for such operation and a transaction T2 must wait.

When a transaction T1 fails and it is rolled back and old value of a data item x is restored. Next, a transaction T2 is allowed to overwrite a data item x with a new value independent on the previous value of the same data item.

Strict executions

Execution is strict if any data item d is written by transaction T then any other transaction cannot either read or write data item d until T is committed or aborted

A sample strict execution


T1                          T2                             x: $100

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

write(x, a+10)                                             x: $110 a: $100

                            write(x,20) wait               x: $110 a: $100

abort                                                      x: $100

                            write(x,20)                    x: $20

                            commit                         x: $20

Strict executions

A sample not strict execution


T1                          T2                             x: $100

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

write(x, a+10)                                             x: $110 a: $100

                            write(x,20)                    x: $20  a: $100

                            commit                         x: $20

abort                                                      x  $100

Rollback of T1 destroys committed T2 :write(x,20)

This slide summarizes different classes of concurrent executions and relationships between such classes.

Assume that entire area of the current slide represents all concurrent executions.

Then a grey blob named "View serializable executions" represents all concurrent executions that satisfy view serializability correctness criterion. In the other words all executions that are correct according to a view serializability definition of correctness.

A light blue blob entirely included in a grey block and named "Conflict serializable executions" represents all concurrent execution that satisfy conflict serializability correctness criterion. In the other words, all concurrent executions that are correct according to conflict serializability definition of correctness.

Note that if a light blue blob is entirely included in a grey blob then every conflict serializable execution is also view serializable execution. It also means that some of view serializable executions are not correct from conflict serializability criterion of correctness point of view.

A darker blue blob named "Order-preserving Conflict serializable executions" represents all concurrent executions that satisfy order-preserving conflict serializability correct ness criterion. Note, that all order-preserving conflict serializable executions are conflct serializable executions. However, there exist conflict serializable executions that are not order preserving.

A light pink blob named "Recoverable executions" represents all concurrent executions that must enforce recoverability correctness criterion and such that each execution is conflict serializable. Note, that some of recoverable executions are not order preserving executions. Light pink blob spans over dark blue blob representing order-preserving conflict serializable executions and over a light blue blob representing only conflict serializable execution.

A dark pink blob named "Cascadeless executions" is entirely included in a light pink blob representing "Recoverable executions". It means that every cascadeless execution is a recoverable execution. However, the opposite is not true. Some of the recoverable executions ate not cascadeless.

Very dark pink blob named "Strict executions" represents all strict executions. Like before, every strict execution is a cascadeless execution and the opposite is not true. All cascadeless and strict executions must be conflict serializable.

Summary

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