DBMS - Timestamp-Based Protocol

Handling multiple transactions in databases can be tricky. If we do not use a proper concurrency control mechanism, the data can get mixed up or lost. Timestamp-based Protocol helps in managing transactions by assigning timestamps to them. This method helps the transactions to execute in the correct order, thereby helping prevent problems like data overwrites or lost updates.

What are Timestamps?

A timestamp is like a tag that marks the exact time when something happens. In databases, a timestamp shows when a transaction starts or when a data item is accessed. We can think of it as a digital timestamp printed on a receipt that tells when a purchase was made.

There are two main ways to generate timestamps −

• System Clock − The computer's system clock can give a precise time. For example: If a transaction starts at 9:00 AM, its timestamp is 9:00.
• Logical Counter − A counter that increases every time a new transaction starts. For example: The first transaction gets 1, the next one gets 2, and so on.

These methods gives that earlier transactions have lower timestamps. While the later ones have higher timestamps.

Why Use Timestamps?

Timestamp-based protocols help databases know the order in which tasks happen. If a transaction with a lower timestamp comes first, then it gets priority over one with a higher timestamp. This will prevent newer transactions from changing data that older ones still need to use.

Example of Timestamps in Action

Let us understand the concept with example. Suppose transaction T1 starts at 9:00 AM and gets a timestamp $TS_1 = 9:00$. After a while, another transaction T2 comes at 9:05 AM and gets a timestamp $TS_2 = 9:05$. We can clearly see $TS_1 < TS_2$, this means T1 is older than T2.

If we use a logical counter instead of the system time, then T1 could get $TS_1 = 1$, and T2 might get $TS_2 = 5$. If other transactions happen in between. Again, T1 is considered older since 1 < 5.

Key Timestamp Records for Data Items

Each data item in a database has two important timestamps −

• W-Timestamp(Q) − The largest timestamp of any transaction that successfully write data item Q.
• R-Timestamp(Q) − The largest timestamp of any transaction that successfully read data item Q.

These timestamps are useful They help to keep track of when data items were last read or written. Here this information helps the system decide whether new transactions can proceed or must wait.

Timestamp-Ordering Protocol

We have understood the basic idea. We can make this idea into proper protocol. The Timestamp-Ordering Protocol uses timestamps to decide the correct order of transactions. It manages the conflicting read and write operations based on transaction timestamps.

Reading Data (Read Q)

When a transaction Ti wants to read data item Q, two situations can occur −

1. If $TS(T_i)$ < W-Timestamp(Q) −

• This means $T_i$ is older than the last transaction that wrote to Q.
• Since $T_i$ is too old, this will reject the read request.

2. If $TS(T_i)$ >= W-Timestamp(Q) −

• $T_i$ is allowed to read Q.
• The database updates R-Timestamp(Q) to $TS(T_i$) if needed.

Writing Data (Write Q)

When a transaction $T_i$ wants to write to data item Q, then these cases arise −

1. If $TS(T_i)$ < R-Timestamp(Q) −

• A newer transaction has already read Q.
• $T_i$ cannot write, as it would cause inconsistencies.

2. If $TS(T_i)$ >= W-Timestamp(Q) and TS(Ti) >= R-Timestamp(Q) −

• $T_i$ is allowed to write.
• W-Timestamp(Q) is updated to $TS(T_i)$.

3. If $TS(T_i)$ < W-Timestamp(Q) −

• This means a newer transaction already updated Q.
• $T_i$'s write request is rejected.

Example of Timestamp-Ordering in Action

Consider two transactions −

• T1 − Timestamp $TS_1$ = 9:00 AM
• T2 − Timestamp $TS_2$ = 9:05 AM

T1 Writes to Data Item Q − The W-Timestamp(Q) becomes 9:00 AM.

T2 Reads Data Item Q − Since $TS_2 = 9:05$ AM is greater than W-Timestamp(Q) = 9:00 AM, T2 reads Q. The R-Timestamp(Q) updates to 9:05 AM.

T1 Tries to Write Again − If $TS_1 = 9:00$ AM and R-Timestamp(Q) = 9:05 AM, then T1's request fails because a newer transaction (T2) already read Q.

From the above rules, we can understand that these rules make sure that transactions happen in the right order, avoiding confusion.

Pros and Cons of Timestamp-Based Protocol

The following table highlights the pros and cons of using timestamp-based protocol −

Conclusion

Timestamp-based protocols are a type of concurrency control protocol. In this chapter, we learned what timestamps are, how they are created, and why they matter. We also explored the timestamp-ordering protocol with examples and real-world comparisons. With timestamping protocols, database transactions follow a clear and orderly process, ensuring reliable database management in complex systems.