12IntroductionToTransactionProcessing(3)

Introduction to Transaction Processing

Outline

Serialization graph
Serialization graph testing protocol
Two phase locking protocol (2PL)
Timestamp ordering protocol

In this presentation we explain three standard solution to a problem of correct concurrent processing of database transactions.nThe solutions include: serialization graph testing protocol, two phase locking protocol and timestamp ordering protocol.

We start from a concept of serialization graph. Serialization graph is a binary directed graph where the nodes represent the concurrently running database transactions and the edges represent the orders in which transactions access data items in conflicting modes.

Serialization graph is constructed in the following way.

When a transaction T starts its concurrent processing with the other transactions we add a node labeled with T to a serialization graph.

If the transactions Ti and Tj process conflicting operations such that Ti processes its operation first then we add an edge directed from a node labelled with Ti to a node labeled with Tj.

A sample concurrent processing of the database transactions T1, T2 and T3 together with a simultaneous construction of a conflict serialization graph is visualized at the bottom of the present slide.

A transaction T1 starts and it processes an operation write(x,10). We add a node labeled with T1 to a serialization graph.

A transaction T2 starts and it processes an operation write(x, 20). We add a node labelled with T2 to a serialization graph. An operation write(x,20) processed by a transaction T2 conflicts with an operation write(x, 10) processed by operation T1. T1 processed a conflicting operation first. This is why we add an edge from a node T1 to a node T2.

Serialization graph

Serialization graph is constructed in the following way
- If a transaction T participates in a concurrent execution then we add a node labeled with T to a serialization graph
- If the transactions T_i and T_j process conflicting operations such that T_i processes its operation first then we add an edge directed from T_i to T_j

Sample construction of a serialization graph


T1                             T2                             T3

write(x, 10)

Create a node T1

                               write(x, 20)

Create a node T2 and add an edge from T1 to T2

A transaction T3 starts and it processes an operation write(x,30). We add a node labelled with T3 to a serialization graph. An operation write(x,30) conflicts with operation write(x,20) processed by a transaction T2. Hence, we add an edge from a node T2 to a node T3. An operation write(x,10) conflicts with an operation write(x,30). it is why we add an edge from a node labelled with T1 to a node labelled with T3.

Next, a transaction T2 processes an operation write(y,10). A data item y has not been accessed by any transaction before processing of the operation does not contribute to any conflict. No new edge is added to a conflict graph.

Next, a transaction T1 processes an operation a=read(y). The operation conflicts with an operation write(y,10) earlier (a moment ago) processed by a transaction T2. We add an edge from T2 to T1 because a transaction T1 processed a conflict operation after a transaction T2 processed a conflicting operation.

A serialization graph is visualized in the middle of the present slide. Note that a cycle T1 -> T2 -> T1 make serialization of the concurrently processed transactions impossible. A transaction T1 cannot be processed before a transaction T2 and in the same moment after a transaction T2.

Serialization graph

                                                             write(x,30)

Create a node T3 and add the edges from T1 to T3 and from T2 to T3

                               write(y,10)

a=read(y)

Add an edge from T2 to T1

ORM

In the present slide we show another concurrent not conflict serializable processing of database transactions.

A transaction T1 starts processing from a=read(x). We create a node labelled with T1.

Next, a transaction T2 processes an operation b=read(x). We create a new node labelled with T2. The operations processed by T1 and T2 use the same item x but the operations do not conflict because both operations are "read" operations.

Next a transaction T1 processes an operation write(x,a-10). The operation conflicts with an operation b=read(x) earlier processed by a transaction T2. Hence we add an edge from a node T2 to a node T1.

Next, a transaction T2 processes an operation write(x,b+20). The operation conflicts with the operations a=read(x) and write(x,a-10) earlier processed by a transaction T1. Hence we add an edge from a node T1 to a node T2.

Serialization graph

A serialization graph of not conflict serializable execution of database transactions

Create a node T1

                                               b=read(x)

Create a node T2

Add an edge from T2 to T1

                                               write(x,b+20)

Add an edge from T1 to T2

Serialization graph

A serialization graph for not conflict serializable execution of database transactions


T1                                              T2
a=read(x)
                                                b=read(x)
write(x,a-10)
                                                write(x,b+20)

ORM

Introduction to Transaction Processing

A serialization graph testing protocol is directly based on a concept of conflict serializability. All requests to access a data item in either read or write mode are submitted to a serialization graph testing scheduler. A serialization graph testing scheduler is responsible for creation and maintenance of serialization graph.

Each time a new transaction starts the scheduler creates a new node and labels the node with a unique transaction identifier. Then when a transactions would like to process either read or write operation such operation is send to the scheduler and the scheduler tries to find if such operation conflicts with one or more operations processed by already running and not committed transactions. If the scheduler detects a conflict then an edge is added to a conflict serialization graph. Then, the scheduler tests a conflict serialization graph for acyclicity. In the other words the scheduler checks if a new added edge closes a cycle somewhere in a conflict serialization graph. If the scheduler cannot detect a cycle then positive confirmation is sent to a transaction and an operation submitted by a transaction is processed.

When a new edge closes a cycle then the scheduler terminates processing of a transaction whose operation requires the addition of a new edge. The transaction is rolled back, a node labelled with its identifier is removed from the graph and all edges connected to the node and pointing to other transactions are removed as well.

When a transaction commits itself, commit operation is sent to a scheduler and the scheduler removes a node with an identifier of committed transaction from the graph and all edges connected to the node and pointing to other transactions.

The main advantage of the conflict serialization graph testing protocol is its simplicity. Practically entire protocol is based on creation, maintenance and testing acyclicity of a binary directed graph. The protocol belongs to a class of so called "optimistic protocols" where the transactions are never blocked (as long as a cycle is created in a graph). Optimistic protocols are considered as better than blocking (pessimistic protocols) in the environments where conflict do not happen too frequently. It means the environments where the chances for conflicts are low and closing a cycle in a conflict serialization graph are low.

However, the protocol has two important disadvantages. First, the protocol may trigger cascading aborts in a situation where a transaction that close a cycle and must be aborted create a data item (earlier called as dirty data) later on read by one or more other transactions. Then other transactions that "read from" an aborted transaction must be aborted as well. Then the transactions that read from aborted transactions must be aborted and so on and so on.

Cascading abort is a price we have to pay for "optimistic" scheduling of database transactions.

As cascading aborts do not completely eliminate a protocol then the second reason is difficult to accept. The second problem is a computational complexity of an algorithm testing acyclicity of a conflict serialization graph. The best algorithm testing acyclicity has a square complexity O(n^2). A square complexity means that time spent on testing acyclicity grows proportionally to n^2 where n is the total number of transactions involved a concurrent execution. Computational complexity of the scheduling protocol is far too slow. It is why a serialization graph testing protocol is only a good educational tool.

Serialization graph testing protocol (SGT)

Principles
- Scheduler maintains and tests serialization graph
- If an operation issued by a transaction violates conflict serializability, i.e. if it creates a cycle in serialization graph then such transaction is aborted
Problems
- Cascading aborts: if a transaction T that created a data item x is aborted then all transactions that read a new value of x must be aborted
- Performance: testing acyclicity of serialization graph has O(n2) complexity

Introduction to Transaction Processing

Outline

Serialization graph
Serialization graph testing protocol
Two phase locking protocol (2PL)
Timestamp ordering protocol

The more realistic and faster protocol is two-phase locking protocol. Two-phase locking protocol belongs to a group of "pessimistic protocols" A "pessimistic protocol reflect a pessimistic state of mind of a scheduler. Whenever and operation submitted to a scheduler conflicts with an already processed operation a scheduler blocks a new coming operation until conflict is eliminated by commit/abort of another transaction.

The principles of two-phase locking are the following.

(1) Access to data items is controlled by shared (read) and exclusive (write) locks

(2) A transaction can read a data item only if a data item is read or write locked by the same transaction or a data item is read locked by another transaction

(3) A transaction can write a data item only if a data item is write locked by the same same transaction

(4) Each transaction must acquire all locks before releasing any lock.

Locking is used by a scheduler to block processing of operations that have been already accessed by another concurrently running transactions. Locking itself is not sufficient to have a correct protocol. It is possible that the transactions lock data items and still concurrent processing may corrupt a database. A principle saying that "(4) Each transaction must acquire all locks before releasing any lock" solves the problem.

Two-phase locking protocol eliminates a problem of cascading aborts because a transaction is never allowed to access dirty data.

Like serialization graph testing the two-phase locking protocol has some disadvantages. The first problems are deadlocks. It is possible that two or more transactions are waiting for each other to access a data item that must be released by another transaction. The second problem is such that sometimes locking is not needed and in such situation it only delays processing of the transactions.

Two-phase locking (2PL) protocol

Principles
- Access to data items is controlled by shared (read) and exclusive (write) locks
- A transaction can read a data item only if a data item is read or write locked by the same transaction or a data item is read locked by another transaction
- A transaction can write a data item only if a data item is write locked by the same same transaction
- Each transaction must acquire all locks before releasing any lock
Two-phase locking protocol belongs to a class of pessimistic protocols
Problems
- Deadlocks
- Unnecessary locks and delays when an execution is conflict serializable

Now, we shall look at an example of concurrent execution of two transactions controlled by two-phase locking protocol. To simplify the protocol we consider a single "lock" operations instead of "shared lock" and "exclusive lock".

A transaction T1 starts processing and attempts to access a data item u with an operation a=read(u). A scheduler locks a data item u and grants lock to a transaction T1. Then a transaction T1 process an operation a=read(u).

Next, a transaction t2 attempts to access a data item v and it obtains a lock on v from the scheduler. With lock granted a transaction T2 processes and operation write(v, 10).

Next a transaction T1 attempts to lock a data item v. A data item v is already locked by a transaction T2 and a transaction T1 must wait for a lock.

Next, a transaction T2 gets a lock on a data item x and it reads a value of data item x with b=read(x).

A transaction T1 still waits for a lock on a data item v.

Next a transaction T2 releases a lock on a data item v. Due to "two-phase" protocol a transaction T2 ends its "locking phase" and it is not allowed to lock any more data items. A data item is unlocked.

Next, a transaction T2 write a new value into a data item x.

Next, a transaction T1 locks a data item v earlier unlocked by a transaction T2.

Next, a transaction T2 unlocks a data item x.

Next, a transaction T1 writes a new value into a data item v.

Finally a transaction T1 unlocks the data items v and u. The first unlock of data item v ends a locking phase of transactions T1 and it starts unlocking phase of a transaction T1.

Please note the locking phases and unlocking phases of both transactions.

Two-phase locking (2PL) protocol

A sample execution controlled by two-phase locking protocol


T1                                        T2

lock(u) a=read(u)

                                          lock(v) write(v,10)

write(u,a+2)

lock(v) wait

                                          lock(x) b=read(x)

                                          unlock(v)

                                          write(x,b+2)

lock(v)

                                          unlock(x)

write(v,a+1)

unlock(v)

unlock(u)

In this slide we look at the first problem associated with any locking protocol: "deadlock".

A transaction T1 locks and reads a data item u with an operation a=read(u).

Next, a transaction T2 locks and reads a data item v with an operation b=read(v) and immediately after that it writes a new value into a data item v.

Now, the transactions try to lock and to access the date items already locked by an opposite transaction.

A transaction T2 attempts to lock a data item v and it must wait because a data item v has been already locked by a transaction T2.

Next, a transaction T2 attempts to lock a data item u and it must wait because u has been already locked by a transaction T1.

At the end a transaction T1 is waiting for release of a lock by a transaction T2 and a transaction T2 waits fro a release of a lock by a transaction T1. None of T1 and T2 can progress in a moment. Such situation is called a "deadlock".

Luckily, database schedulers are able to break a deadlock. Typically, a scheduler maintains a "wait-for" graph (similar to conflict serialization graph) and when a cycle is discovered in a "wait-for" graph one of the transactions involved in a cycle is aborted. Another solution is to look at time a transaction is waiting for access to a data item. If such time is too long a transaction is aborted by a scheduler. it is a solution called "timeout".

Two-phase locking (2PL) protocol

A sample execution that ends in a deadlock

		
T1                                        T2

lock(u) a=read(u)

                                          lock(v) b=read(v)

                                          write(v,b+10)

lock(v) wait

                                          lock(u) wait

Both transactions are in a wait state
In a database system deadlock is eliminated through either wait for graph or throught timeout

The second problem typical for locking protocol is a completely unnecessary attempt to delay a transaction waiting for a lock.

Assume that a transaction T1 locks and reads a data item u with an operation a=read(u).

Next, a transaction T2 attempts to lock a data item u. A data item u is already locked by T1 and a transaction T2 must wait.

Next, a transaction T1 locks a data item v and writes a new value into a data item v. A transaction t1 is still waiting for a lock on a data item u.

Next a transaction T1 unlocks both data items v and u. It allows a transaction T1 to get a lock on a data item u and to process an operation b=read(u). At the end a transaction T2 writes a new value into a data item u.

Why locking of a data item is not needed ?

The transactions T1 and T2 access only one common data item: u. A transaction T1 reads a data item u and a transaction T2 reads and writes a data item u. For any combination of read by T1 and read and write by T2 a not conflict serializable execution will never happen. It is impossible to create more than one conflict over such sets of operations. Hence, a transaction T1 does not need to wait for a lock on a data item u. Locking of data item v by T1 is also not needed because v is not accessed by a transaction T2.

Two-phase locking (2PL) protocol

A sample execution that unnecessarily delays a transaction

		
T1                                        T2

lock(u) a=read(u)

                                          lock(u) wait

lock(v) write(v,a+1)

unlock(v,u)

                                          lock(u) b=read(u)

                                          write(u,b+1)

The transactions T1 and T2 never get into not conflict serializable execution
Therefore, there is no need to delay a transaction T2

Introduction to Transaction Processing

Outline

Serialization graph
Serialization graph testing protocol
Two phase locking protocol (2PL)
Timestamp ordering protocol

Timestamp ordering protocol belongs to a category of ''optimistic'' protocols. An optimistic protocol allow a transaction to access data items without any restrictions and whenever not conflict serializable execution is detected then one of the transactions is aborted and rolled back. A serialization graph testing protocol explained earlier in this presentation belongs to a category of optimistic protocols.

The principles behind a timestamp ordering protocol are the following.

(1) Each transaction obtains a timestamp at its start point. A timestamp is usually taken from a system clock with a precision up to a single milisecond.

(2) Data items are stamped each time any transaction accesses data items in a read or write mode. In our simplified version of the protocol we shall not distinguish between read timestamps and write timestamps. Then, whenever a transactions accesses a data item such data item is "stamped" with a timestamp a transaction got from a scheduler at start time.

(3) Access to data items is permitted only in an increasing order of timestamps. It means that if a transaction wants to access a data item then a timestamp assigned to the transaction at start time must be higher than all timestamps a data item is stamped up to now. A transaction cannot access a data item after such data item was accessed by a transaction that started later.

If access to the data items in an increasing order of timestamps is impossible then a transaction that violates the rule is aborted by the systemn and it is automatically rolled back.

As all other protocols a timestamp ordering protocols has its advantages and disadvantages. The main advantage is its "optimistic" nature that provides better performance than locking protocols in the environments where there are not many conflicts when accessing data items. The only overhead is granting a timestamp to a transaction and to maintain information about timestamps attached to the data items.

On the other hand a timestamp ordering protocol has two disadvantages:

- cascading aborts (as all "optimistic" protocols),

- unnecessary aborts when execution is conflict serializable (it is a similar problem to unnecessary locks by two-phase locking protocol).

Timestamp ordering (TO) protocol

Principles
- Each transaction obtains a timestamp at its start point
- Data items are stamped each time any transaction accesses data items in a read or write mode
- Access to data items is permitted in an increasing order of timestamps
Timestamp ordering protocol belongs to a class of optimistic protocols
Problems
- Cascading aborts
- Unnecessary aborts when execution is conflict serializable

We start from a sample concurrent execution of database transactions controlled by timestamp ordering protocol.

The transaction T1 and T2 access data items x and y.

A concurrent execution of T1 and T2 starts from a transaction T1 accesing a data item x. A transaction T1 obtains a timestamp t1 at start point and an operation a=read(x) forces an item x to be timestamped with t1 (x:t1).

Next, a transaction T1 changes a value of data item x with an operation write(x,a-10). A data item x is already stamped with a timestamp t1, hence there is no need to do it next time.

Next, a transaction T2 starts and it obtains a timestamp t2. A transaction T2 writes data item x and stamps it with t2. Now, x has two timestamps t1 and t2 and it has been accessed in a correct order of timestamps (t1 < t2). A transaction T1 accessed a data item x before a transaction T2 accessed a data item x. An order of timestamps is correct and both T1 and T2 continue their processing.

Next, a transaction T2 reads a data item y with an operation b=read(y). A data item y is stamped with a timestamp t2.

Finally, a transaction T1 attempt to write a data item y with an operation write(y,a+1). An attempt to stamp a data item y with a timestamp t1 fails because a data item y is already stamped with t1 < t2. A data item cannot be stamped in a decreasing order of timestamps. A transaction T1 cannot process an operation write(y,a+1), it is aborted by a scheduler and it is rolled back.

Timestamp ordering (TO) protocol

A sample execution controlled by the timestamp ordering protocol


T1                                 T2                          x,y

timestamp(t1)

a=read(x)                                                      x:t1

write(x,a-10)

                                   timestamp(t2)

                                   write(x,10)                 x:t1:t2

                                   b=read(y)                   y:t2

write(y,a+1)                                                   y:t2:t1

abort

The present slide shows a sample cascading abort.

A transaction T1 starts first, obtains a timestamp t1, reads a data items c and writes a data item x. A data item x is stamped with t1.

Then a transaction T2 starts, gets a timestamp t2 > t1 and reads a data item x. A data item x is stamped with t2. An order of time stamp t2 > t1 is consistent with an order a data item has been accessed by the transactions T1 and T2.

Then a transaction T1 fails, for example due to a programming error, a physical error when accessing persistent storage or any kind of error. A transaction T1 must be rolled back. A transaction T2 read a data item written by a transaction T1 and because of that it must be aborted and rolled back ss well.

A transaction T2 is forced to abort because it reads a dirty data item written by a failed transaction T1.

Timestamp ordering (TO) protocol

A sample execution controlled by the timestamp ordering protocol with cascading abort


T1                                 T2                          x

timestamp(t1)

a=read(x)                                                      x:t1

write(x,x-10)

                                   timestamp(t2)

                                   b=read(x)                   x:t1:t2

fail

                                   forced abort

A transaction T2 is forced to abort because it reads a dirty data item written by a failed transaction T1

In the present slide we show a sample A sample execution controlled by the timestamp ordering protocol where a transaction is unnecessarily aborted.

The execution start from granting a timestamp t2 to a transaction T2. Then a transaction T2 does nothing and a transaction T1 starts. A transaction T1 obtains a timestamp t1. This time t1 > t2.

Next, a transaction T1 reads a data item x with an operation a=read(x). A data item x is stamped with t1.

Next, a transaction T1 writes a data item x with an operation write(x,a-10).

Finally, a transaction T2 attempts to read data item x with an operation b= read(x). An attempt to read x by T2 fails because data item x has been already stamped with a timestamp t1 greater than a timestamp t2. According to timestamp ordering protocol a data item must be stamped with an increasing order of timestamps. As a consequence a transaction t2 is aborted and rolled back.

But ... the execution is conflict serializable, A transaction T2 is aborted even the execution is conflict serializable

Timestamp ordering (TO) protocol

A sample excution controlled by the timestamp ordering protocol where a transaction is unnecessarily aborted


T1                                 T2                          x

                                   timestamp(t2)

timestamp(t1)

a=read(x)                                                      x:t1

write(x,a-10)

                                   b=read(x) forced abort      x:t1:t2

A transaction T2 is aborted even the execution is conflict serializable

References

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