MySQL supports two lock mechanisms: pessimistic lock and optimistic lock.
Pessimistic lock
Pessimistic locks refer to acquiring the lock before performing a read and write operation, ensuring that other threads cannot modify the data before the operation is completed.
For example:
Suppose there is a business scenario for bank transfer, which involves the amount operation of two accounts. In order to avoid data conflicts and concurrency problems, pessimistic locks can be used to achieve it.
There are two main ways to implement pessimistic locks in MySQL:
1. Implemented through SELECT ... FOR UPDATE statement
This statement can lock the line to be updated to ensure that other transactions cannot modify the line before it is locked.
For example:
Suppose there is a user table where each user has a balance field. Now you need to reduce the balance of a certain user by 100 yuan. You can use the following SQL statement:
BEGIN; SELECT balance FROM user WHERE id = 1 FOR UPDATE; UPDATE user SET balance = balance - 100 WHERE id = 1; COMMIT;
In this example:
The SELECT ... FOR UPDATE statement locks the updated user row first to ensure that other transactions cannot modify it before performing the update operation.
2. Implementation through LOCK TABLES statement
This statement can lock the entire table or some rows in the table, ensuring that other transactions cannot modify them.
For example, suppose there is an order table where each order has a status field, and now you need to change all unpaid order status to paid, you can use the following SQL statement:
LOCK TABLES order WRITE; UPDATE order SET status = 1 WHERE status = 0; UNLOCK TABLES;
In this example:
Lock the entire order table through the LOCK TABLES statement to ensure that other transactions cannot modify it, then perform an update operation, and finally release the lock using the UNLOCK TABLES statement.
It should be noted that the pessimistic locking mechanism usually requires the use of the database locking mechanism in implementation, so it may lead to performance bottlenecks and deadlock problems in high concurrency scenarios, so it needs to be used with caution.
Optimistic lock
The optimistic locking mechanism in MySQL refers to not adding a lock before performing a read and write operation, but rather using a version number or a timestamp to determine whether the data has been modified by other transactions, thereby determining whether to perform a write operation. Compared with pessimistic locks, the advantage of optimistic locks is that they reduce problems such as lock waiting and deadlock, and improve concurrency performance.
The following is an example of the optimistic locking mechanism in MySQL database.
Suppose there is a user table where each user has a balance field.
Now you need to modify the balance of a certain user. If the balance value remains unchanged, you can update it directly; otherwise, you need to re-read the latest balance value and determine whether it can be updated.
The following SQL statements can be implemented:
UPDATE user SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
In this example:
By adding the AND balance >= 100 condition in the update statement, ensure that only users with balance greater than or equal to 100 can be updated.
If another transaction modified the balance value of the user before performing the update operation, no data is updated when the update statement is executed.
This avoids dirty data and concurrency problems, and also avoids performance problems caused by using pessimistic locks.
What should be noted is:
- When using optimistic locking mechanisms, special attention should be paid to the issue of concurrent conflicts.
- If concurrent conflicts are frequent, optimistic locks may lead to higher retry rates and performance losses. Therefore, optimistic locking is more suitable for scenarios with fewer concurrent conflicts.
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.