mysql共享锁和排它锁示例

<think>好的，我现在需要帮助用户解决如何在MySQL中使用共享锁（LOCK INSHAREMODE）防止丢失更新，并确保事务并发时的数据一致性的问题。首先，我得回顾一下用户的问题和相关引用，确保自己正确理解需求。 用户提到想了解如何使用共享锁更新数据以及解决丢失更新问题。根据提供的引用，特别是引用3和4，共享锁（LOCKIN SHARE MODE）允许其他事务读取数据，但阻止修改，直到锁释放。而排他锁（FORUPDATE）则更严格，不允许其他事务读写。用户需要的是在并发事务中防止丢失更新，即在多个事务同时读取同一数据后，更新导致其中一个覆盖另一个的情况。首先，我需要明确丢失更新的场景。例如，两个事务同时读取同一行数据，然后各自基于读取的值进行修改，后提交的事务会覆盖前一个的修改，导致前一个的更新丢失。这种情况下，如何用共享锁来避免呢？ 根据引用3，使用共享锁可以阻止其他事务修改数据，但允许读取。那如果两个事务都使用LOCKIN SHARE MODE，会发生什么？当一个事务获取共享锁后，另一个事务也可以获取共享锁，但都不能进行修改，直到锁释放。这时候，如果两个事务都试图在读取后更新，可能会出现死锁，因为每个事务都持有共享锁，无法升级为排他锁。这时候可能需要事务在更新时尝试获取排他锁，但如果有其他共享锁存在，就会等待，导致死锁的可能性。 那用户的问题是如何用共享锁防止丢失更新。可能需要结合共享锁和排他锁的使用。例如，在读取时使用共享锁确保数据不被修改，然后在更新时获取排他锁。但这样是否有效？或者是否有其他方法？ 根据引用4，共享锁的使用场景包括主从表的一致性，比如在插入从表记录前锁定主表数据，防止主表数据被删除。这可能与用户的问题不太直接相关，但说明共享锁用于需要保持数据不被修改但允许读取的情况。 回到丢失更新问题，通常的解决方案是使用悲观锁或乐观锁。悲观锁如SELECTFOR UPDATE，在读取时锁定行，防止其他事务读取或修改。而共享锁可能无法完全解决，因为多个事务可以同时持有共享锁，然后在更新时尝试获取排他锁，导致死锁。但用户特别询问的是如何使用共享锁来防止丢失更新。可能需要分步骤说明：首先用LOCK INSHAREMODE读取数据，这样其他事务可以读取但不能修改。然后在更新时，事务需要将共享锁升级为排他锁，但这在MySQL中可能无法自动实现，需要显式的更新语句获取排他锁。此时，如果有多个事务都尝试升级，可能会死锁，需要处理这种情况。例如，事务A和B都使用LOCK INSHAREMODE读取同一行，然后都尝试更新。此时，更新操作需要获取排他锁，但事务A和B都持有共享锁，导致互相等待，形成死锁。MySQL会检测到死锁并回滚其中一个事务。因此，使用共享锁可能不是防止丢失更新的最佳方法，而更常用的是SELECTFOR UPDATE。但用户可能希望了解共享锁的应用场景，或者如何在特定情况下使用共享锁来避免丢失更新。可能需要指出，虽然共享锁可以部分防止丢失更新，但在高并发场景下可能不如排他锁有效，并给出示例代码和步骤，同时说明潜在的问题，如死锁，以及如何处理。 另外，根据引用3中的示例，使用LOCKIN SHARE MODE后，执行更新操作，这可能在事务中自动获取排他锁。但需要确保在更新时能够成功，否则会阻塞直到锁释放。因此，正确的步骤可能是在事务中先使用共享锁读取，然后在更新时获取排他锁，这样其他事务无法在读取后同时更新，从而避免丢失更新。但需要验证这种方法的有效性。例如，事务A使用共享锁读取，事务B也可以使用共享锁读取。当A尝试更新时，需要排他锁，此时B仍持有共享锁，导致A等待。如果B在此时也尝试更新，同样需要排他锁，导致死锁。因此，这种方法可能导致死锁，需要事务重试机制或超时设置。 综上，回答应该包括使用共享锁的步骤，示例代码，以及可能的问题和解决方案。同时，比较共享锁和排他锁的适用场景，帮助用户理解何时使用哪种方法更合适。</think>### 如何在MySQL中使用共享锁防止丢失更新 #### 一、丢失更新问题场景 当多个事务同时读取同一数据并基于读取结果进行修改时，后提交的事务可能覆盖先提交事务的修改。例如： 1. 事务A和事务B同时读取账户余额为$1000 2. 事务A存入$200，更新为$1200 3. 事务B取出$100，更新为$900（覆盖了$1200） #### 二、共享锁的防御机制 使用`SELECT ... LOCK IN SHARE MODE`实现以下效果： 1. **读取阶段**：允许并发读取，但禁止数据修改 2. **更新阶段**：自动升级为排他锁，强制串行化更新 ```sql -- 事务1 START TRANSACTION; SELECT balance FROM accounts WHERE id=1 LOCK IN SHARE MODE; -- 获取共享锁 UPDATE accounts SET balance = 1200 WHERE id=1; -- 自动升级为排他锁 COMMIT; -- 事务2 START TRANSACTION; SELECT balance FROM accounts WHERE id=1 LOCK IN SHARE MODE; -- 等待事务1释放锁 UPDATE accounts SET balance = 900 WHERE id=1; COMMIT; ``` #### 三、实现步骤详解 1. **事务开启**：显式声明事务边界 2. **共享锁读取**：`SELECT ... LOCK IN SHARE MODE`锁定目标数据 3. **业务处理**：基于读取值进行业务计算 4. **排他锁更新**：UPDATE语句自动申请排他锁 5. **事务提交**：释放所有锁 #### 四、锁机制对比 | 锁类型 | 并发读取 | 并发修改 | 适用场景 | |-----------------|----------|----------|----------------------------------| | 共享锁 | ✅允许 | ❌禁止 | 读多写少，需要保持读取一致性 | | 排他锁(FOR UPDATE)| ❌禁止 | ❌禁止 | 写密集型操作，强数据一致性要求 | #### 五、潜在风险与解决方案 1. **死锁风险**： - 当两个事务互相等待锁释放时可能发生 - 解决方案：设置事务超时`SET innodb_lock_wait_timeout = 50;` 2. **性能影响**： - 长时间持有共享锁会降低并发吞吐量 - 优化建议：缩小事务范围，尽快提交事务 #### 六、生产环境最佳实践 1. 配合索引使用：确保WHERE条件命中索引，避免锁表 2. 重试机制：代码层实现事务失败自动重试 3. 监控工具：使用`SHOW ENGINE INNODB STATUS`监控锁状态 ```python # Python事务重试示例 import MySQLdb from retrying import retry @retry(stop_max_attempt_number=3, wait_fixed=2000) def update_balance(): conn = MySQLdb.connect(...) try: cursor = conn.cursor() cursor.execute("START TRANSACTION") cursor.execute("SELECT balance FROM accounts WHERE id=1 LOCK IN SHARE MODE") # 业务逻辑处理 cursor.execute("UPDATE accounts SET balance = ...") conn.commit() except Exception as e: conn.rollback() raise e ``` #### 七、适用场景验证 1. **订单库存校验**：多个系统同时校验库存时保证基准值一致性 2. **财务余额核对**：审计过程中锁定当前账户状态 3. **主从表关联操作**：插入从表记录时锁定主表数据[^4]

在 Java 项目中使用 MySQL 悲观锁主要是通过 SQL 语句的 `SELECT ... FOR UPDATE` 或 `SELECT ... LOCK IN SHARE MODE` 实现的。 `SELECT ... FOR UPDATE` 语句会对查询结果的每一行加上排它锁（Exclusive Lock），其它事务无法对这些行进行修改或者加排它锁，直到当前事务结束，这种方式适用于需要修改的场景。 `SELECT ... LOCK IN SHARE MODE` 语句会对查询结果的每一行加上共享锁（Shared Lock），其它事务可以对这些行进行查询但是无法修改或者加排它锁，直到当前事务结束，这种方式适用于只读场景。 下面是使用 `SELECT ... FOR UPDATE` 的示例代码： ```java Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = dataSource.getConnection(); pstmt = conn.prepareStatement("SELECT * FROM my_table WHERE id = ? FOR UPDATE"); pstmt.setLong(1, 1L); rs = pstmt.executeQuery(); // 对查询结果进行修改 } catch (SQLException e) { // 异常处理 } finally { // 关闭连接和释放资源 try { rs.close(); } catch (Exception e) {} try { pstmt.close(); } catch (Exception e) {} try { conn.close(); } catch (Exception e) {} } ``` 在以上示例代码中，通过 `SELECT ... FOR UPDATE` 查询 id 为 1 的记录并加上排它锁，然后对查询结果进行修改。 需要注意的是，使用悲观锁会对数据库性能有影响，因为在锁定期间其它事务无法访问被锁定的数据。因此，在使用悲观锁时需要谨慎权衡性能和数据一致性的需求。