The difference between optimistic lock and pessimistic lock in MySQL and description

In MySQL,Optimistic lockandPessimistic lockIt is two different concurrency control mechanisms used to solve conflict problems when multiple users/transactions operate data simultaneously.

Their core concepts and implementation methods are significantly different:

1. Pessimistic Locking

Core idea

Assume that the data will be modified frequently, so lock the data in advance to prevent other transactions from accessing until the current transaction completes the operation and releases the lock.

Implementation method

Explicit locking: Actively apply for locks through SQL statements.

• Exclusive Lock (X Lock)：

SELECT ... FOR UPDATE

(Lock the selected row in the transaction, preventing other transactions from modifying or locking)

• Shared lock (S Lock)：

SELECT ... LOCK IN SHARE MODE

(Allow other transactions to read, but prevent write operations)

Implicit locking: Automatic database management (for example, MySQL's InnoDB engine uses row-level locks by default).

Applicable scenarios

• Write more and read lessscenarios (such as frequently updated data).
• needStrong consistencyAnd when the probability of conflict is high (such as financial transactions).

Example

START TRANSACTION;
-- Lock the user account balance（Exclusive lock）
SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE;
-- Perform deduction operations
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
COMMIT;

advantage:

• Ensure the atomicity and consistency of data operations.
• Suitable for high competition scenarios (higher efficiency when the probability of conflict is high).

shortcoming:

• May causeDeadlock(Requires application layer processing).
• Reduce concurrency performance (holding the lock for a long time will block other operations).

2. Optimistic Locking

Core idea

Assume that data conflicts occur less, so there is no locking, but check whether the data has been modified when updated. If modified, the operation is refused or retry is tried.

Implementation method

• Version number (Version): Add in the tableversionField, verify version number when updated.

UPDATE table 
SET 
  column = new_value, 
  version = version + 1 
WHERE 
  id = 1 AND version = old_version;

• Timestamp: Similar version number, but use timestamp to mark the data to modify the time.
• CAS（Compare-And-Swap）: Compare data consistency at the application layer before submitting.

Applicable scenarios

• Read more and write lessscenarios (such as flash sale when the product inventory is sufficient).
• When the probability of conflict is low (such as user likes operations).

Example

-- Initial query（Get the current version number）
SELECT balance, version FROM accounts WHERE user_id = 1;

-- Check version number when updating
UPDATE accounts 
SET 
  balance = balance - 100, 
  version = version + 1 
WHERE 
  user_id = 1 AND version = 1; -- Assume the old version number is1

-- If the number of affected rows=0，Explanation version has expired，Need to try again or report an error

advantage:

• Lock-free competition, high concurrency performance.
• Avoid deadlock problems.

shortcoming:

• Retry logic needs to be handled when a conflict occurs (such as loop retry or return an error).
• Strong consistency (final consistency) cannot be guaranteed.

3. Comparative summary

4. Actual selection in MySQL

• InnoDB Engine: Supports row-level locks, suitable for pessimistic locks (need to be explicitly usedFOR UPDATE）。
• MyISAM Engine: Only table-level locks are supported, pessimistic lock performance is poor, and is usually not recommended.
• Optimistic lock: It needs to be implemented at the application layer (such as through the version number field), and has nothing to do with the storage engine.

Select according to business scenario:

• Strong consistency scenarios such as financial transactions → Pessimistic lock
• High concurrency read more and write less scenarios → Optimistic lock

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.