Optimistic Locking vs. Pessimistic Locking

Both optimistic and pessimistic locking are mechanisms to ensure data integrity when multiple transactions are trying to access or modify the same data concurrently. They differ in how they handle potential conflicts.

1. Optimistic Locking

• Assumption: The optimistic approach assumes that conflicts (i.e., multiple transactions trying to modify the same data at the same time) are rare. Instead of locking data during a transaction, it allows transactions to proceed without locking and detects conflicts when changes are being committed.

How It Works:

• The data being updated includes a version number or timestamp field.
• When a transaction reads the data, it also reads the version number.
• When the transaction attempts to update the data, it checks if the version number has changed since the data was first read.
• If the version number is unchanged, the update proceeds, and the version number is incremented.
• If the version number has changed (indicating another transaction modified the data), the update is rejected, and the transaction can retry or fail gracefully.

Use Case:

• Best suited for scenarios where conflicts are infrequent and locking data upfront is expensive.
• Example: In e-commerce, where multiple users may view and attempt to purchase a product, optimistic locking can help avoid unnecessary locking of the product while still ensuring that conflicting changes are detected.

Example:

Let’s say we are using a version column to track changes:

// Assume an optimistic locking strategy in a Sequelize model
await GameSettingModel.update(
  { is_active: true }, 
  { 
    where: { id: 42, version: currentVersion }, // check current version
  }
);

• If the version is not the same as when the row was read, the transaction fails, indicating a conflict occurred.

Pros:

• No need to lock data during the read, allowing for more concurrency.
• Avoids unnecessary blocking of rows unless there’s an actual conflict.

Cons:

• A failed update can require retry logic, leading to potentially more complex code to handle retries.

2. Pessimistic Locking

• Assumption: The pessimistic approach assumes that conflicts are likely, so it locks data immediately when it is being read or updated to prevent any other transactions from modifying it until the lock is released.

How It Works:

• A transaction reads data with a lock (e.g., FOR UPDATE).
• The lock prevents other transactions from modifying the data until the transaction holding the lock is finished (committed or rolled back).
• This ensures that once the transaction starts, no other transaction can modify the same data, avoiding conflicts upfront.

Use Case:

• Best suited for scenarios where conflicts are likely or where data integrity is critical, and you want to avoid complex conflict resolution later.
• Example: In a financial system, where multiple transactions might try to withdraw from the same account, pessimistic locking ensures that only one transaction can proceed at a time, avoiding overdrafts or other issues.

Example:

Using Sequelize’s FOR UPDATE lock for pessimistic locking:

const transaction = await sequelize.transaction();
try {
  const jackpot = await GameSettingModel.findOne({
    where: { id: 42 },
    lock: transaction.LOCK.UPDATE, // pessimistic lock
    transaction
  });

  jackpot.is_active = true;
  await jackpot.save({ transaction });

  await transaction.commit();
} catch (error) {
  await transaction.rollback();
}

• Here, the FOR UPDATE lock prevents any other transactions from updating the row until this transaction is finished.

Pros:

• Prevents conflicts upfront by locking data immediately.
• Ensures no other transaction can modify the locked rows until the transaction completes.

Cons:

• Reduces concurrency because other transactions that want to modify the locked rows will have to wait.
• Can lead to deadlocks if two transactions are trying to lock rows in different orders.

Conclusion:

• Optimistic Locking is ideal when you expect fewer conflicts and want to maximize concurrency.
• Pessimistic Locking is ideal for high-contention scenarios where preventing conflicts upfront is critical