Can you explain the difference between pessimistic and optimistic concurrency control in MySQL?

Concurrency control is the process used to manage the access and modification of shared resources among multiple users in a database system. Optimistic and pessimistic concurrency control are two approaches used to manage concurrent transactions in a database.

Pessimistic concurrency control (PCC) is based on the assumption that conflicts are likely to occur during concurrent transactions. Therefore, PCC requires that transactions lock resources in the database (e.g., tables, rows, or columns) during their processing time to avoid conflicts. This locking mechanism prevents other transactions from accessing or modifying locked resources, which may result in blocking and deadlocks. PCC is suitable for database systems with high contention workloads, where transactions often require exclusive access to the same resource(s) and are therefore prone to conflicts.

Here is an example of a pessimistic concurrency control approach in MySQL using the ‘SELECT ... FOR UPDATE‘ statement. The ‘SELECT ... FOR UPDATE‘ statement is used to retrieve rows from a table while locking them to prevent other transactions from accessing them until the transaction that holds the lock completes.

START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

Optimistic concurrency control (OCC) is based on the assumption that conflicts between transactions are infrequent. Therefore, OCC does not require locks to manage concurrency. Instead, each transaction reads the current state of a resource and applies changes to it in memory, without updating the resource in the database. Before committing changes, the transaction verifies that the resource’s state has not changed since the transaction started. If the state has changed, the transaction rolls back and can retry the operation. OCC is suitable for database systems with low contention workloads, where conflicts are less likely to occur.

Here is an example of optimistic concurrency control approach in MySQL using the ‘SELECT ... FOR UPDATE‘ syntax. In this example, ‘@version‘ variable holds the current value, and if the value is the same at the time we execute the update query, then the row is updated with a new value, and the version is incremented.

START TRANSACTION;
SELECT balance INTO @balance, version INTO @version FROM accounts WHERE id = 1;
SET @balance = @balance + 100, @version = @version + 1;
UPDATE accounts SET balance = @balance, version = @version WHERE id = 1 AND version = @version - 1;
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

In summary, Pessimistic concurrency control uses locks to prevent conflicts between transactions, while optimistic concurrency control uses in-memory processing without locks and checks for conflicts after the transaction is complete. The choice between the two approaches depends on the workload and requirements of the database system.