Example Codes (SQL Server)

In SQL Server, transactions are isolated units of work that ensure data consistency. Isolation levels determine how transactions interact with each other and prevent data inconsistencies. Here's a breakdown of "read committed" and "repeatable read":

READ COMMITTED

• Default isolation level in SQL Server.
• Prevents dirty reads: A transaction cannot read data that is being modified by another uncommitted transaction.
• Allows non-repeatable reads: If another transaction commits changes to data that a "read committed" transaction has already read, subsequent reads within the same "read committed" transaction might see the updated data, leading to inconsistencies.
• May allow phantom reads: If another transaction inserts new rows that meet the criteria of a "read committed" transaction's query after the initial read, subsequent reads might see these new rows, although they weren't there initially.
• Uses minimal locking: This improves concurrency (multiple transactions can access data concurrently) but can lead to the issues mentioned above.

REPEATABLE READ

• Provides stronger consistency: Ensures that a transaction always sees the same data on subsequent reads within the transaction, even if other transactions commit changes in the meantime.
• Prevents non-repeatable reads: This is achieved by using read locks on the data accessed by the transaction. These locks are held until the transaction commits or rolls back, preventing other transactions from modifying the data.
• May still allow phantom reads: Similar to "read committed," new rows inserted by other transactions might be visible in subsequent reads.
• Uses more locking: This can improve data consistency but can also reduce concurrency by blocking other transactions from accessing locked data.

Choosing the Right Level

• READ COMMITTED: Use this for scenarios where data consistency is less critical, and high concurrency is desired (e.g., reporting applications).
• REPEATABLE READ: Use this when data consistency is more important, and you want to ensure that a transaction always sees the same data throughout its execution (e.g., financial transactions).

READ_COMMITTED_SNAPSHOT (Optional)

• This is a database-level option in SQL Server that can change the behavior of the "read committed" isolation level.
• When enabled, it uses row versioning instead of locking to achieve read consistency within a "read committed" transaction. This can improve concurrency compared to traditional "read committed" with locking.

Key Points

• "READ COMMITTED" offers better concurrency but can lead to inconsistencies like non-repeatable reads.
• "REPEATABLE READ" provides stronger consistency but can reduce concurrency due to locking.
• Choose the level that balances your needs for data consistency and concurrency.
• Consider using READ_COMMITTED_SNAPSHOT with "read committed" for improved concurrency within that level.

Example Codes (SQL Server)

We have a table Products with columns ProductID (primary key) and Price. We'll simulate two transactions, one updating a product price and another reading the price within different isolation levels.

Transaction 1 (Update Price)

BEGIN TRANSACTION;

UPDATE Products
SET Price = 129.99
WHERE ProductID = 1;

COMMIT;

Transaction 2 (Read Price - READ COMMITTED)

BEGIN TRANSACTION;  -- Set READ COMMITTED (default)

SELECT Price
FROM Products
WHERE ProductID = 1;

-- Here, Transaction 2 might see either the original price or the updated price
-- depending on the timing of Transaction 1's commit.

COMMIT;

BEGIN TRANSACTION;

UPDATE Products
SET Price = 129.99
WHERE ProductID = 1;

COMMIT;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- Switch to READ UNCOMMITTED for demonstration

BEGIN TRANSACTION;

SELECT Price
FROM Products
WHERE ProductID = 1;

-- Here, Transaction 2 will always see the original price because READ UNCOMMITTED
-- doesn't prevent non-repeatable reads. We use this to demonstrate the difference.

COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- Switch back to default

BEGIN TRANSACTION;  -- New transaction with REPEATABLE READ

SELECT Price
FROM Products
WHERE ProductID = 1;

-- Here, Transaction 2 will always see the updated price (129.99) because REPEATABLE READ
-- maintains read locks until the transaction commits, ensuring consistent reads.

COMMIT;

Explanation

• In the "READ COMMITTED" example, the second transaction might see either the original or updated price depending on the timing of the update commit. This demonstrates non-repeatable reads.
• In the "REPEATABLE READ" example, the second transaction always sees the updated price (129.99) due to read locks held until the transaction commits, ensuring consistent reads within the transaction.

1. Increase Isolation Level:

2. Application-Level Logic:

3. Optimistic Locking:

4. Stored Procedures:

The best method depends on your specific requirements:

• If high concurrency is crucial, "READ COMMITTED" with application-level logic or optimistic locking might be suitable.
• If stricter consistency is required without sacrificing significant concurrency, "REPEATABLE READ" might be a good choice.
• For situations where absolute consistency is paramount, regardless of concurrency impact, "SERIALIZABLE" might be necessary.