# Conflict Serializability

As discussed in Concurrency control , serial schedules have less resource utilization and low throughput. To improve it, two are more transactions are run concurrently. But concurrency of transactions may lead to inconsistency in database. To avoid this, we need to check whether these concurrent schedules are serializable or not.

**Conflict Serializable:** A schedule is called conflict serializable if it can be transformed into a serial schedule by swapping non-conflicting operations.

**Conflicting operations:** Two operations are said to be conflicting if all conditions satisfy:

- They belong to different transactions
- They operate on the same data item
- At Least one of them is a write operation

Example: –

**Conflicting**operations pair (R_{1}(A), W_{2}(A)) because they belong to two different transactions on same data item A and one of them is write operation.- Similarly, (W
_{1}(A), W_{2}(A)) and (W_{1}(A), R_{2}(A)) pairs are also**conflicting**. - On the other hand, (R
_{1}(A), W_{2}(B)) pair is**non-conflicting**because they operate on different data item. - Similarly, ((W
_{1}(A), W_{2}(B)) pair is**non-conflicting.**

Consider the following schedule:

S1: R_{1}(A), W_{1}(A), R_{2}(A), W_{2}(A), R_{1}(B), W_{1}(B), R_{2}(B), W_{2}(B)

If O_{i} and O_{j} are two operations in a transaction and O_{i}< O_{j }(O_{i }is executed before O_{j}), same order will follow in schedule as well. Using this property, we can get two transactions of schedule S1 as:

T1: R_{1}(A), W_{1}(A), R_{1}(B), W_{1}(B) T2: R_{2}(A), W_{2}(A), R_{2}(B), W_{2}(B)

**Possible Serial Schedules are: T1->T2 or T2->T1**

-> **Swapping non-conflicting operation**s R_{2}(A) and R_{1}(B) in S1, the schedule becomes,

S11:R_{1}(A), W_{1}(A), R_{1}(B),WR_{2}(A),_{2}(A),WR_{1}(B),_{2}(B), W_{2}(B)

-> Similarly, s**wapping non-conflicting operations** W_{2}(A) and W_{1}(B) in S11, the schedule becomes,

S12:R_{1}(A), W_{1}(A), R_{1}(B), W_{1}(B), R_{2}(A), W_{2}(A), R_{2}(B), W_{2}(B)

S12 is a serial schedule in which all operations of T1 are performed before starting any operation of T2. Since S has been transformed into a serial schedule S12 by swapping non-conflicting operations of S1, S1 is conflict serializable.

Let us take another Schedule:

S2: R_{2}(A), W_{2}(A), R_{1}(A), W_{1}(A), R_{1}(B), W_{1}(B), R_{2}(B), W_{2}(B)

Two transactions will be:

T1: R_{1}(A), W_{1}(A), R_{1}(B), W_{1}(B) T2: R_{2}(A), W_{2}(A), R_{2}(B), W_{2}(B)

**Possible Serial Schedules are: T1->T2 or T2->T1**

Original Schedule is:

S2:R_{2}(A), W_{2}(A),RW_{1}(A),_{1}(A), R_{1}(B), W_{1}(B),RW_{2}(B),_{2}(B)

Swapping non-conflicting operations R_{1}(A) and R_{2}(B) in S2, the schedule becomes,

S21:R_{2}(A), W_{2}(A), R_{2}(B),WR_{1}(A),_{1}(B), W_{1}(B), R_{1}(A),W_{2}(B)

Similarly, swapping non-conflicting operations W_{1}(A) and W_{2}(B) in S21, the schedule becomes,

S22:R_{2}(A), W_{2}(A), R_{2}(B), W_{2}(B), R_{1}(B), W_{1}(B), R_{1}(A), W_{1}(A)

In schedule S22, all operations of T2 are performed first, but operations of T1 are not in order (order should be R_{1}(A), W_{1}(A), R_{1}(B), W_{1}(B)). So S2 is not conflict serializable.

**Conflict Equivalent:** Two schedules are said to be conflict equivalent when one can be transformed to another by swapping non-conflicting operations. In the example discussed above, S11 is conflict equivalent to S1 (S1 can be converted to S11 by swapping non-conflicting operations). Similarly, S11 is conflict equivalent to S12 and so on.

**Note 1:** Although S2 is not conflict serializable, but still it is conflict equivalent to S21 and S21 because S2 can be converted to S21 and S22 by swapping non-conflicting operations.

**Note 2:** The schedule which is conflict serializable is always conflict equivalent to one of the serial schedule. S1 schedule discussed above (which is conflict serializable) is equivalent to serial schedule (T1->T2).

**Question:** **Consider the following schedules involving two transactions. Which one of the following statement is true?**

S1: R_{1}(X) R_{1}(Y) R_{2}(X) R_{2}(Y) W_{2}(Y) W_{1}(X)

S2: R_{1}(X) R_{2}(X) R_{2}(Y) W_{2}(Y) R_{1}(Y) W_{1}(X)

- Both S1 and S2 are conflict serializable
- Only S1 is conflict serializable
- Only S2 is conflict serializable
- None

[GATE 2007]

**Solution:** Two transactions of given schedules are:

T1: R_{1}(X) R_{1}(Y) W_{1}(X) T2: R_{2}(X) R_{2}(Y) W_{2}(Y)

Let us first check serializability of S1:

S1: R_{1}(X) R_{1}(Y) R_{2}(X) R_{2}(Y) W_{2}(Y) W_{1}(X)

To convert it to a serial schedule, we have to swap non-conflicting operations so that S1 becomes equivalent to serial schedule T1->T2 or T2->T1. In this case, to convert it to a serial schedule, we must have to swap R_{2}(X) and W_{1}(X) but they are conflicting. So S1 can’t be converted to a serial schedule.

Now, let us check serializability of S2:

S2:RR_{1}(X) R_{2}(X)_{2}(Y) W_{2}(Y) R_{1}(Y) W_{1}(X)

Swapping non conflicting operations R_{1}(X) and R_{2}(X) of S2, we get

S2’: R_{2}(X)RW_{1}(X) R_{2}(Y)_{2}(Y) R_{1}(Y) W_{1}(X)

Again, swapping non conflicting operations R_{1}(X) and R_{2}(Y) of S2’, we get

S2’’: R_{2}(X) R_{2}(Y)RR_{1}(X) W_{2}(Y)_{1}(Y) W_{1}(X)

Again, swapping non conflicting operations R_{1}(X) and W_{2}(Y) of S2’’, we get

S2’’’: R_{2}(X) R_{2}(Y) W_{2}(Y) R_{1}(X) R_{1}(Y) W_{1}(X)

which is equivalent to a serial schedule T2->T1.

So, **correct option is C**. Only S2 is conflict serializable.

**Related Article:**

View Serializability

Precedence Graph For Testing Conflict Serializability

Article contributed by Sonal Tuteja. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above

## Recommended Posts:

- DBMS | Precedence Graph For Testing Conflict Serializability
- Neo4j Introduction
- SELECT INTO statement in SQL
- Extendible Hashing | A Dynamic approach to DBMS
- LOB Locator and LOB Value
- Basic operations and Working of LOB
- Cascadeless in DBMS
- Weak Entity Set in ER diagrams
- Boyce-Codd Normal Form (BCNF)
- Third Normal Form (3NF)
- Second Normal Form (2NF)
- First Normal Form (1NF)
- B*-Trees implementation in C++
- Data Mining | Set 2