Locking vs Optimistic Concurrency Control: Strategies for Concurrent Edits in SQL Server

• Collision: If two users try to update the same record simultaneously, their changes might conflict.

Solutions:

Additional Techniques:

• Timestamps: Adding a timestamp column to tables allows tracking when a record was last updated. This helps identify conflicts during OCC.
• Audit Trails: Logging changes made to records helps trace edits and identify potential conflicts.

Important Considerations:

• Security: Granting appropriate permissions to users ensures they can only edit authorized data.
• Application Logic: The application interacting with the database should handle potential conflicts gracefully, informing users and allowing them to resolve issues.

SQL Server Locking Example with Transactions

CREATE TABLE Users (
  ID int PRIMARY KEY,
  Username nvarchar(50) NOT NULL UNIQUE,
  Email nvarchar(100) NOT NULL UNIQUE
);

GO

-- Simulate user A updating a record
BEGIN TRANSACTION;
UPDATE Users SET Email = '[email protected]' WHERE Username = 'userA';

-- Simulate user B trying to update the same record concurrently (causing a lock wait)
SELECT * FROM Users WITH (XLOCK, ROWLOCK) WHERE Username = 'userA';

-- User A commits their update, releasing the lock
COMMIT TRANSACTION;

-- Now user B's update can proceed
UPDATE Users SET Username = 'updatedUsername' WHERE Username = 'userA';

GO

Explanation:

1. We create a simple Users table.
2. User A starts a transaction and updates the email for 'userA'.
3. User B tries to select the same user with an XLOCK (exclusive lock) and ROWLOCK (locks specific row) hint, causing them to wait until User A finishes.
4. User A commits their update, releasing the lock.
5. User B's update can now proceed successfully.

This is a basic example. In practice, error handling and conflict resolution logic would be implemented in the application layer.

CREATE TABLE Products (
  ID int PRIMARY KEY,
  Name nvarchar(50) NOT NULL UNIQUE,
  Price decimal(10,2) NOT NULL,
  LastUpdated datetime NOT NULL DEFAULT(GETUTCDATE()) -- Timestamp for OCC
);

GO

-- User A retrieves product data with timestamp
DECLARE @productID int = 1;
DECLARE @initialTimestamp datetime;

SELECT @productID, Name, Price, LastUpdated
INTO @productID, @initialTimestamp
FROM Products WITH (NOLOCK) WHERE ID = @productID;

-- Simulate user A modifying the price
UPDATE Products
SET Price = Price + 5.00
WHERE ID = @productID AND LastUpdated = @initialTimestamp;

-- Simulate user B updating the same product concurrently
UPDATE Products
SET Name = 'New Name'
WHERE ID = @productID;

GO

-- User A might encounter an update conflict 
IF @@ERROR <> 0 
BEGIN
  -- Handle the conflict (e.g., inform user and prompt to retry)
  DECLARE @errorMessage nvarchar(max) = 'Update conflict detected!';
  RAISERROR (@errorMessage, 10, 1);
END;

1. We create a Products table with a LastUpdated timestamp column.
2. User A retrieves product details with NOLOCK hint (avoiding unnecessary locks) and stores the timestamp.
3. User A updates the price, but includes a WHERE clause checking if the LastUpdated timestamp matches the retrieved value.
4. User B concurrently updates the product name.
5. If User B's update happens first, User A's update will fail due to a mismatch in the LastUpdated timestamp, indicating a conflict.
6. The application logic (IF statement) can handle the error by informing the user and potentially allowing them to retry the update.

Benefits of OCC:

• Reduced locking overhead compared to traditional locking methods.
• Improved concurrency and performance in scenarios with low conflict rates.

Drawbacks of OCC:

• Requires additional logic in the application layer to handle conflicts.
• Might lead to lost updates if conflicts are frequent.