1. Introduction
In scenarios where multi-user concurrent access to databases, data consistency and integrity are crucial. In order to avoid data conflict problems caused by concurrent operations, MySQL provides two concurrency control mechanisms: optimistic lock and pessimistic lock. Understanding how they work, applicable scenarios, and how they can be applied in real code is of critical significance for developing efficient and reliable database applications.
2. Optimistic lock
(I) Principle
Optimistic locks are based on the optimistic assumption that in most cases there will be no conflict between concurrent transactions. It does not lock the data at the beginning of the transaction, but checks whether the data has been modified by other transactions during the transaction execution before submitting the data update. If it is not modified, updates are allowed; if the data has been modified, it is decided based on the specific business logic whether to try the update operation or throw an exception.
(II) Application scenarios
Optimistic locks are suitable for scenarios where more reads and fewer writes, because in this case, the probability of concurrent conflict is relatively low. For example, in an e-commerce system, the number of views of a product is a typical operation of more reads, less writes. Multiple users can view product details pages at the same time, and the number of views is relatively few.
(III) Sample code
Suppose we have oneproductstable, which containsid(Primary key),name、quantityandversionField.versionThe field will be used as the version number of the optimistic lock.
-- create products surface CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), quantity INT, version INT );
Here is an example of updating product quantity using optimistic locks in Java code:
import ;
import ;
import ;
import ;
import ;
public class OptimisticLockingExample {
public static void main(String[] args) {
// Database connection information String url = "jdbc:mysql://localhost:3306/mydb";
String username = "root";
String password = "password";
try (Connection connection = (url, username, password)) {
// Start transactions (false);
// Query product information and obtain the current version number String selectSql = "SELECT quantity, version FROM products WHERE id =?";
try (PreparedStatement selectStmt = (selectSql)) {
(1, 1); // Suppose you want to update the product with id 1 try (ResultSet resultSet = ()) {
if (()) {
int currentQuantity = ("quantity");
int currentVersion = ("version");
// Simulate business logic to reduce the quantity of goods int newQuantity = currentQuantity - 1;
// Update product quantity and version number and use an optimistic lock mechanism String updateSql = "UPDATE products SET quantity =?, version = version + 1 WHERE id =? AND version =?";
try (PreparedStatement updateStmt = (updateSql)) {
(1, newQuantity);
(2, 1); // Product id (3, currentVersion);
int rowsUpdated = ();
if (rowsUpdated == 0) {
// Instructions that the data is modified by other transactions before updating, and the optimistic lock conflicts are expected. ("Optimistic lock conflict, update failed");
();
} else {
// Submit transaction ();
("Product quantity updated successfully");
}
}
}
}
}
} catch (SQLException e) {
();
}
}
}
In the above code, first query the current quantity and version number of the product, and then calculate the new quantity according to the business needs. When updated,WHEREThe version number condition in the clause to check whether the data has been modified by other transactions. If the number of updated rows is 0, it means that an optimistic lock conflict has occurred.
3. Pessimistic lock
(I) Principle
In contrast to optimistic locks, pessimistic locks take a pessimistic attitude, believing that in a concurrent environment, the data is likely to be modified by other transactions. Therefore, at the beginning of the transaction, the data to be operated is locked and the lock is not released until the transaction is completed and committed or rolled back. This ensures that other transactions cannot modify the locked data during transaction execution.
(II) Application scenarios
Pessimistic locks are suitable for scenarios where more writes and fewer reads and extremely high requirements for data consistency. For example, in the transfer operation of the banking system, it involves modifying the account balance, and it is necessary to ensure that the account balance data will not be interfered by other concurrent transactions during the entire transfer transaction. At this time, using pessimistic locks can effectively avoid the problem of data inconsistency.
(III) Sample code
Also withproductsTables can be used in MySQLSELECT... FOR UPDATESentences to achieve pessimistic lock.
-- Start a transaction START TRANSACTION; -- Use pessimistic lock to query product information and lock rows SELECT quantity FROM products WHERE id = 1 FOR UPDATE; -- Simulate business logic，Reduce the quantity of goods UPDATE products SET quantity = quantity - 1 WHERE id = 1; -- Submit transactions COMMIT;
In the above SQL code,SELECT quantity FROM products WHERE id = 1 FOR UPDATEThe statement will be correctidLock the product line of 1 until the transaction is committed or rolled back. During locking, other transactions will be blocked if they try to modify the row of data until the lock is released.
4. Summary
Optimistic locking and pessimistic locking are two important concurrency control mechanisms in MySQL, and they are each suitable for different application scenarios. In actual development, it is necessary to reasonably choose to use optimistic locks or pessimistic locks based on factors such as business needs, data read and write ratios, and requirements for data consistency to ensure that the database system can operate stably and efficiently in a high concurrency environment, while ensuring data integrity and consistency.
This is the article about optimistic locks and pessimistic locks in MySQL, which are essential for database interviews. For more related content on MySQL optimistic locks and pessimistic locks, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!