1.2.1 Timestamp Ordering Protocol

This protocol ensures that transactions are executed in a timestamp order to maintain serializability. Each transaction is assigned a timestamp, and the system orders transactions based on these timestamps.

• Read Timestamp (RTS): The largest timestamp of any transaction that successfully read the item.
• Write Timestamp (WTS): The largest timestamp of any transaction that successfully wrote the item.

If a transaction's timestamp is less than the write timestamp of the item it wants to read/write, the transaction is aborted and restarted with a new timestamp.

1.2.2 Thomas’s Write Rule

This rule is an optimization of the timestamp ordering protocol. It allows some write operations to be ignored if they do not affect the final outcome, enhancing performance.

The write operation can be ignored if the timestamp of the write is less than the current write timestamp of the data item, indicating that the write is outdated and irrelevant.

1.3.1 Creating a Timestamp Column

To create a timestamp column in an SQL table:

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    data VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This command creates a table with a timestamp column that records the creation time of each row.

1.3.2 Updating a Timestamp Column

To update a timestamp column whenever a row is modified:

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    data VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

This command ensures that the updated_at column reflects the last modification time.

1.3.3 Retrieving Records Based on Timestamps

To query records based on their timestamps:

SELECT * FROM example_table
WHERE created_at >= '2023-01-01 00:00:00' AND created_at < '2024-01-01 00:00:00';

This query retrieves all records created in the year 2023.

1.4.1 Basic Timestamp Ordering

Basic timestamp ordering is a concurrency control method where each transaction is assigned a unique timestamp. Transactions are executed in the order of their timestamps to avoid conflicts.

• Read Operation: A transaction $T_i$ can read a data item only if its timestamp is greater than the write timestamp of the last transaction that wrote the item. Otherwise, $T_i$ is aborted and restarted.
• Write Operation: A transaction $T_i$ can write a data item only if its timestamp is greater than the read and write timestamps of the last transactions that accessed the item. If not, $T_i$ is aborted and restarted.

1.4.2 Multiversion Concurrency Control (MVCC)

MVCC uses multiple versions of data items to handle concurrent transactions, leveraging timestamps for version control.

• Read Operation: Each transaction reads the latest version of the data item that is committed before the transaction's timestamp.
• Write Operation: Each transaction creates a new version of the data item with its timestamp, making it visible to subsequent transactions.

This method improves performance by allowing read operations to proceed without waiting for write operations.

1.4.3 Two-Phase Locking (2PL) with Timestamps

Two-Phase Locking (2PL) can be combined with timestamps to enhance concurrency control. The protocol ensures that all locks are acquired before any are released, divided into two phases:

• Growing Phase: A transaction can obtain locks but cannot release any.
• Shrinking Phase: A transaction can release locks but cannot obtain any new ones.

Timestamps can be used to resolve deadlocks by assigning priorities to transactions based on their timestamps. Older transactions (with smaller timestamps) are given higher priority.

1.4.4 Optimistic Concurrency Control (OCC)

OCC assumes that conflicts are rare and checks for conflicts only at the end of the transaction. It involves three phases:

• Read Phase: The transaction reads data items and stores them in a local workspace.
• Validation Phase: Before committing, the transaction is validated to ensure no conflicts occurred during its execution. Timestamps are used to check for conflicts with other transactions.
• Write Phase: If validated, the transaction updates the database; otherwise, it is aborted and restarted.