SQL Optimistic Concurrency Control (OCC)

SQL Concurrency Optimistic Concurrency Control (OCC) is a concurrency control technique used in database management systems to handle simultaneous access to data without blocking transactions.

1. Overview

SQL Concurrency Optimistic Concurrency Control (OCC) is based on the optimistic assumption that conflicts between transactions are rare. Instead of locking data during transaction execution, OCC allows transactions to proceed without interference and checks for conflicts only when transactions are ready to commit.

In OCC, each transaction operates on a local copy of data and records the changes it intends to make. Before committing, the transaction verifies that no other transactions have modified the data it read or intends to modify. If conflicts are detected, the transaction is aborted and must be retried.

2. Implementation

The implementation of SQL Concurrency Optimistic Concurrency Control typically involves the following steps:

Read Phase: Transactions read data without acquiring locks, allowing other transactions to access the same data concurrently.
Write Phase: Transactions make changes to their local copy of data and record these changes for later validation.
Validation Phase: Before committing, transactions check if any other transactions have modified the data they read or intend to modify. If conflicts are detected, the transaction is aborted.
Commit Phase: If validation is successful, transactions commit their changes to the database. Otherwise, they are restarted or retried.

By allowing transactions to proceed without blocking, SQL Concurrency Optimistic Concurrency Control can improve concurrency and throughput in database systems.

3. Example

Consider a scenario where two transactions, T1 and T2, attempt to update the same bank account balance:

Example:

-- Transaction T1: Read balance and update
BEGIN TRANSACTION;
READ balance FROM accounts WHERE account_number = '123';
balance = balance - 100;
UPDATE accounts SET balance = balance WHERE account_number = '123';
COMMIT;

-- Transaction T2: Read balance and update
BEGIN TRANSACTION;
READ balance FROM accounts WHERE account_number = '123';
balance = balance - 50;
UPDATE accounts SET balance = balance WHERE account_number = '123';
COMMIT;

In SQL Concurrency Optimistic Concurrency Control, both transactions T1 and T2 proceed with their updates without blocking each other. However, during the validation phase, the system detects that T2's update conflicts with T1's update. As a result, one of the transactions is aborted and must be retried.

4. Conclusion

SQL Concurrency Optimistic Concurrency Control (OCC) is a concurrency control technique that allows transactions to proceed without blocking by assuming conflicts are rare and detecting them only during the validation phase. By minimizing locking and allowing concurrent access to data, OCC can improve the concurrency and performance of database systems.