DBMS - Testing Serializability

Serializability plays an important role in maintaining data consistency when multiple transactions are executed concurrently. Testing serializability means determining whether a schedule (sequence of transactions) is equivalent to a serial schedule or not. Here the transactions are executed one at a time.

Read this chapter to learn the concept of serializability and how to test it using precedence graphs. We will also understand its rules with detailed examples and step-by-step construction of graphs.

What is Serializability?

Serializability means the result of executing transactions concurrently is the same as if they were executed one by one in some serial order.

• A serial schedule is used to processes transactions sequentially. This gives accuracy but reducing system efficiency.
• A non-serial schedule interleaves instructions from multiple transactions. It increases performance at the risk of producing data conflicts.

To maintain both accuracy and efficiency, we need to test whether a non-serial schedule behaves like a serial one or not. If it does, the schedule is considered serializable. If not, it risks producing incorrect results. This is making the schedule non-serializable.

Importance of Testing Serializability

Testing serializability helps us analyze whether databases handle multiple transactions correctly or not, even when the transactions are running simultaneously.

Consider an online banking system where two customers transfer money simultaneously. If their transactions are not scheduled correctly, then the balances may be incorrect. It can cause serious financial issues. Testing serializability prevents such problems by validating that overlapping transactions produce the correct results.

How to Test Serializability

The most common method for testing serializability is constructing a precedence graph (also called a serializability graph). This is one graph based approach which can be represented visually. This directed graph helps determine whether a schedule is serializable.

• If the graph has no cycles, the schedule is serializable.
• If the graph contains cycles, the schedule is non-serializable.

Rules for Constructing a Precedence Graph

Follow these steps to create a precedence graph for a schedule −

Create a Node for Each Transaction − Each transaction gets its own node. For example, if the schedule has T1 and T2, then make two nodes as labeled T1 and T2.

Add a Directed Edge $(T_i \rightarrow T_j)$ − Draw a directed edge from $T_i$ to $T_j$. If one of these dependencies exists −

• Rule 1: Write-Read (W - R) − If $T_i$ writes a value, and $T_j$ reads that value afterward, draw an edge $T_i \rightarrow T_j$.
• Rule 2: Read-Write (R - W) − If $T_i$ reads a value, and $T_j$ writes that value afterward. Then draw an edge $T_i \rightarrow T_j$.
• Rule 3: Write-Write (W - W) − If $T_i$ writes a value, and $T_j$ writes the same value later. Then draw an edge $T_i \rightarrow T_j$.

These rules track conflicts where transaction order matters. If these dependencies create a cycle then the transactions cannot be serialized. So it is making the schedule non-serializable.

Detailed Examples of Testing Serializability

Let us work through two detailed examples to understand how precedence graphs are built.

Example 1: Non-Serializable Schedule

Consider three transactions T1, T2, and T3 executing the following operations −

Step 1: Create Nodes

We create nodes for T1, T2, and T3.

Step 2: Apply Precedence Rules

• T1 → T2 (W - R) − T1 writes A, and T2 reads A. This creates an edge T1 → T2.
• T2 → T3 (W - R) − T2 writes B, and T3 reads B. This creates an edge T2 → T3.
• T3 → T1 (W - W) − T3 writes A, which T1 wrote earlier. This creates an edge T3 → T1.

Step 3: Draw the Graph

The precedence graph looks like this −

Since the graph contains a cycle, the schedule is non-serializable. It means interleaving transactions in this order could lead to incorrect results.

Example 2: Serializable Schedule

Consider another schedule involving transactions T4, T5, and T6 −

Step 1: Create Nodes

We create nodes for T4, T5, and T6.

Step 2: Apply Precedence Rules

• T4 → T5 (W - R) − T4 writes A, and T5 reads A. This creates an edge T4 → T5.
• T4 → T6 (W - R) − T4 writes C, and T6 reads C. This creates an edge T4 → T6.
• T5 → T6 (W - R) − T5 writes B, and T6 reads B. This creates an edge T5 → T6.

Step 3: Draw the Graph

The precedence graph looks like this −

Since the graph has no cycles, the schedule is serializable. This means executing the transactions in this order will produce correct results.

Special Considerations in Serializability Testing

• Same Transaction Operations − If a transaction reads and writes the same variable internally, no edges are created.
• Independent Variables − If two transactions work on different variables, they are independent, and no edges are drawn.
• Graph Analysis − Check for cycles carefully, as even one cycle makes the schedule non-serializable.

Conclusion

In this chapter, we explained how to test the serializability of transactions using precedence graphs. We discussed the core concept of serializability and highlighted the rules for building precedence graphs. Testing serializability is necessary for database systems because it shows that even when transactions run simultaneously, they produce correct and consistent results.