What are concurrency issues in databases?

Concurrency issues occur when multiple transactions try to access or modify the same data simultaneously, leading to problems like race conditions, dirty reads, lost updates, and inconsistent states.

What is pessimistic locking?

Pessimistic locking prevents conflicts by locking the data until a transaction is completed. This ensures data consistency but may reduce performance due to blocking other transactions.

What is optimistic locking?

Optimistic locking assumes conflicts are rare. Instead of locking data, it checks for changes before committing a transaction. If a conflict is detected, the transaction is retried, making it more efficient in read-heavy systems.

Concurrency Issues Interactive Demo

⚡ Concurrency Problem Simulator

Problem: Two users trying to buy the same product simultaneously!

📱 iPhone 15 Pro
Stock Available: 1
Price: $1,499

Purchase Logs:

Ready to simulate purchases...

🛡️ Concurrency Solutions

Solutions: Techniques to handle concurrent access effectively.

-- Pessimistic Locking Approach
BEGIN TRANSACTION
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- Row is now locked, other users must wait
IF stock > 0 THEN
  UPDATE products SET stock = stock - 1 WHERE id = 1;
  INSERT INTO orders (user_id, product_id) VALUES (?, ?);
COMMIT;

Advantage: Guaranteed consistency
Disadvantage: Performance impact, potential deadlocks

-- Optimistic Concurrency Control
SELECT stock, version FROM products WHERE id = 1;
-- Client performs business logic
UPDATE products SET
  stock = stock - 1,
  version = version + 1
WHERE id = 1 AND version = @original_version;
-- If affected rows = 0, then conflict occurred

Advantage: Better performance, no blocking
Disadvantage: Retry logic needed

-- Atomic Transaction
CREATE PROCEDURE BuyProduct(@userId INT, @productId INT)
AS BEGIN
  BEGIN TRANSACTION
  DECLARE @stock INT
  SELECT @stock = stock FROM products WHERE id = @productId
  IF @stock > 0
    UPDATE products SET stock = stock - 1 WHERE id = @productId
    INSERT INTO orders VALUES(@userId, @productId, GETDATE())
    COMMIT
  ELSE
    ROLLBACK
END

// Frontend Concurrency Handling
let isProcessing = false;
function buyNow() {
  if (isProcessing) return;
  isProcessing = true;
  document.getElementById('buyBtn').disabled = true;
  fetch('/api/purchase', { method: 'POST' })
    .then(response => {
      isProcessing = false;
      document.getElementById('buyBtn').disabled = false;
    });
}

🔍 Concurrency Debugging & Monitoring

🕒 Timestamp Analysis

/* Simulate purchases to see debug logs */

📈 Concurrency Metrics

Metric	Without Protection	With Protection
Success Rate	60%	100%
Data Integrity	❌ Compromised	✅ Maintained
Response Time	150ms	280ms
Error Rate	40%	0%

Solution	✅ Pros	❌ Cons	Use Case
🔒 Pessimistic Locking	100% consistency guaranteed	Performance impact, deadlock risk	Critical operations, low concurrency
🎯 Optimistic Locking	Better performance, no blocking	Retry logic needed, conflict resolution	High read, low conflict scenarios
⚛️ Atomic Transactions	ACID compliance, data integrity	Resource intensive	Financial transactions, inventory
🖥️ UI Controls	User-friendly, immediate feedback	Client-side only, not foolproof	User experience enhancement