【SQL】MySQL 的乐观锁和悲观锁

在MySQL中，乐观锁和悲观锁是两种常见的并发控制机制，用于确保在多事务并发环境下数据的一致性。下面是对这两种锁的详细说明。

悲观锁 (Pessimistic Locking)

悲观锁假定数据在一段时间内会被多个事务同时修改，因此在访问数据时会直接加锁，以防止其他事务对数据进行修改。悲观锁通常依赖于数据库的锁机制来实现。

实现方式

在MySQL中，可以使用

SELECT ... FOR UPDATE

或

SELECT ... LOCK IN SHARE MODE

来实现悲观锁。

• FOR UPDATE：对选中的行加排他锁（X锁），其他事务不能读取和修改。
• LOCK IN SHARE MODE：对选中的行加共享锁（S锁），其他事务可以读取但不能修改。

示例

假设有一个

employees

表：

CREATETABLE employees (
    id INTPRIMARYKEY,
    name VARCHAR(100),
    salary DECIMAL(10,2))ENGINE=InnoDB;

事务1：

BEGIN;SELECT*FROM employees WHERE id =1FORUPDATE;

此时，事务1对

id = 1

的记录加了排他锁 (X锁)。

事务2：

BEGIN;UPDATE employees SET salary =6000WHERE id =1;

此时，事务2会被阻塞，直到事务1提交或回滚。

乐观锁 (Optimistic Locking)

乐观锁假定数据在大部分时间内不会发生冲突，因此在读取数据时不加锁，而是在提交更新时检查数据是否被其他事务修改。如果数据被修改，则回滚事务并重新尝试。

实现方式

乐观锁通常通过在表中添加一个版本号字段来实现，每次更新数据时检查版本号是否变化。

示例

假设有一个

employees

表，包含一个

version

字段：

CREATETABLE employees (
    id INTPRIMARYKEY,
    name VARCHAR(100),
    salary DECIMAL(10,2),
    version INT)ENGINE=InnoDB;

读取数据时记下版本号：

SELECT id, name, salary, version FROM employees WHERE id =1;

更新数据时检查版本号：

UPDATE employees
SET salary =6000, version = version +1WHERE id =1AND version =1;

如果版本号匹配，则更新成功；如果版本号不匹配，则更新失败，需要重新读取数据并重试。

Java 代码示例

悲观锁

importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;publicclassPessimisticLockExample{publicstaticvoidmain(String[] args){String url ="jdbc:mysql://localhost:3306/yourdatabase";String user ="yourusername";String password ="yourpassword";try(Connection conn =DriverManager.getConnection(url, user, password)){
            conn.setAutoCommit(false);String selectQuery ="SELECT * FROM employees WHERE id = ? FOR UPDATE";try(PreparedStatement selectStmt = conn.prepareStatement(selectQuery)){
                selectStmt.setInt(1,1);try(ResultSet rs = selectStmt.executeQuery()){if(rs.next()){System.out.println("ID: "+ rs.getInt("id")+", Name: "+ rs.getString("name"));}}}String updateQuery ="UPDATE employees SET salary = ? WHERE id = ?";try(PreparedStatement updateStmt = conn.prepareStatement(updateQuery)){
                updateStmt.setBigDecimal(1,newBigDecimal("6000.00"));
                updateStmt.setInt(2,1);
                updateStmt.executeUpdate();}

            conn.commit();}catch(SQLException e){
            e.printStackTrace();}}}

乐观锁

importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;publicclassOptimisticLockExample{publicstaticvoidmain(String[] args){String url ="jdbc:mysql://localhost:3306/yourdatabase";String user ="yourusername";String password ="yourpassword";try(Connection conn =DriverManager.getConnection(url, user, password)){
            conn.setAutoCommit(false);String selectQuery ="SELECT id, name, salary, version FROM employees WHERE id = ?";int id =1;int currentVersion =-1;try(PreparedStatement selectStmt = conn.prepareStatement(selectQuery)){
                selectStmt.setInt(1, id);try(ResultSet rs = selectStmt.executeQuery()){if(rs.next()){
                        currentVersion = rs.getInt("version");System.out.println("ID: "+ rs.getInt("id")+", Name: "+ rs.getString("name")+", Version: "+ currentVersion);}}}String updateQuery ="UPDATE employees SET salary = ?, version = version + 1 WHERE id = ? AND version = ?";try(PreparedStatement updateStmt = conn.prepareStatement(updateQuery)){
                updateStmt.setBigDecimal(1,newBigDecimal("6000.00"));
                updateStmt.setInt(2, id);
                updateStmt.setInt(3, currentVersion);int rowsAffected = updateStmt.executeUpdate();if(rowsAffected ==0){System.out.println("Update failed due to concurrent modification.");}else{System.out.println("Update successful.");}}

            conn.commit();}catch(SQLException e){
            e.printStackTrace();}}}

这些代码示例展示了如何在Java中使用JDBC实现悲观锁和乐观锁。悲观锁通过

FOR UPDATE

语句实现，而乐观锁则依赖于版本号的检查。