Question

Consider the following history H:

T2:R(Y), T1:R(X), T3:R(Y), T2:R(X), T2:W(Y), T2:Commit, T1:W(X), T1:Commit, T3:R(X), T3:Commit

Assume that each transaction is consistent.

Does the final database state satisfy all integrity constraints? Explain.

Answer 1

To preserve integrity transaction must satisfy ACID properties :

1. Atomicity (A)
2. Consistency (C)
3. Isolation(I)
4. Durability (D)

Generally, reading the data does not compromise the integrity. The integrity is compromised when a write operation is done without commit (means saving of the updated information).

Atomicity :

It means execute all operations of transaction including commit or execute none of the operations of transaction by the time of transaction termination.

In all the three transactions, T1, T2 and T3 first data is readed, that is, R(Y), R(X) and R(Y) respectively. So no compromise of atomicity till now.

Next, in transaction T2 X data is readed, that is also acceptable.

Now in T2 write operation is performed on Y that is W(Y), since no uncomitted operation in other transactions on Y till now so it is acceptable. Next the operation of T2 is comitted.

Now in T1 write operation is performed on X that is W(X), sinec no uncommitted operation till now on X so it is acceptable. After that transaction is comitted and all updates are saved.

Next in T3 read of X is done that is R(X) which is acceptable and comitted of the updates.

Since there are no dirty read and all the write operations are comitted without any error hence atomicity is followed in this history.

Consistency :

Consistency means the transaction does not have error done by users. It is given that the history is consistent.

Hence, this property also satisfies.

Isolation :

Isolation is satisfied if concurrent execution two or more transactions may be equal to result of any serial execution of transactions. (Serial schedule means transaction must execute one after other.)

It means any read or write operation performed in one transaction should not be impacted by other read and write in other transaction.

One of the serial schedule can be given as : T2 -> T1 -> T3 . In this the final value of X and Y is same as that of concurrent transaction.

Durability :

It means in case of any failure the transaction must be able to recover.

Since all the transactions are having commit after update then we can redo at that point. Hence, we can handle the scenario in case of failure and durability satisfies.

Since the history satisfies all the four ACID properties. Hence it satisfies all integrity constraints.