MySQL 表锁、行锁_表锁与行锁的sql语句

一、表级锁定：

读锁定：使用 LOCK TABLES 语句可以锁定一个或多个表格，直到当前会话结束或使用 UNLOCK TABLES 解锁。例如：

LOCK TABLES table_name READ;

写锁定：使用 LOCK TABLES 语句并指定 WRITE 关键字可以锁定一个或多个表格，直到当前会话结束或使用 UNLOCK TABLES 解锁。例如：

LOCK TABLES table_name WRITE;

二、行级锁定：

在事务中使用 SELECT ... FOR UPDATE 语句可以锁定选定行，直到事务结束为止。例如：

START TRANSACTION;
SELECT * FROM table_name WHERE condition FOR UPDATE;
-- 对选定行进行操作
COMMIT;

或者使用 SELECT ... FOR SHARE 语句可以锁定选定行，但是允许其他会话也可以读取这些行。例如：

START TRANSACTION;
SELECT * FROM table_name WHERE condition FOR SHARE;
-- 对选定行进行操作
COMMIT;

三、自动锁定：

MySQL 在执行某些类型的操作时会自动进行锁定，例如在使用 INSERT, UPDATE, DELETE 等语句时会锁定受影响的行，直到事务结束。 

无论使用哪种方法，一定要小心使用锁定，因为它可能会导致并发性能问题。建议只在必要时才使用锁定，并在锁定范围内尽量减少操作的时间。同时，一定要在合适的时机释放锁定，以避免出现死锁或其他并发问题。

四、转账操作

在处理转账等涉及到资金安全的操作时，确保数据的一致性和完整性非常重要。可以通过以下方法来实现转账过程中的锁定：

1、使用事务：在开始转账操作前，启动一个事务，并在事务中执行转账的相关操作。在事务中的所有操作要么全部成功，要么全部失败，这可以确保转账的原子性。例如：

START TRANSACTION;
UPDATE accounts SET balance = balance - amount WHERE account_id = sender_id;
UPDATE accounts SET balance = balance + amount WHERE account_id = receiver_id;
COMMIT;

2、行级锁定：在转账过程中，使用行级锁定来锁定涉及到的账户行，以防止其他会话同时修改这些行。例如，在开始事务后，可以使用 SELECT ... FOR UPDATE 来锁定相关账户行：

START TRANSACTION;
SELECT * FROM accounts WHERE account_id = sender_id FOR UPDATE;
SELECT * FROM accounts WHERE account_id = receiver_id FOR UPDATE;
-- 执行转账操作
COMMIT;

3、悲观锁（行锁）：在开始转账操作前，使用 SELECT ... FOR UPDATE 来悲观地锁定涉及到的账户行，以确保在转账过程中不会有其他会话干扰。例如：

START TRANSACTION;
SELECT * FROM accounts WHERE account_id = sender_id FOR UPDATE;
SELECT * FROM accounts WHERE account_id = receiver_id FOR UPDATE;
-- 执行转账操作
COMMIT;

4、乐观锁（版本锁）：在每个账户记录中添加一个版本号或时间戳，并在更新账户余额时检查该版本号或时间戳，以确保没有其他会话同时修改了账户信息。如果发现冲突，可以选择回滚操作或者重试。例如：

START TRANSACTION;
SELECT * FROM accounts WHERE account_id = sender_id;
-- 检查版本号或时间戳
UPDATE accounts SET balance = balance - amount WHERE account_id = sender_id;
SELECT * FROM accounts WHERE account_id = receiver_id;
-- 检查版本号或时间戳
UPDATE accounts SET balance = balance + amount WHERE account_id = receiver_id;
COMMIT;

四、Mybatis-jdbc

在使用 MyBatis 进行数据库操作时，你可以结合 MyBatis 的事务管理和数据库的行级锁定来实现转账过程中的数据锁定。

配置事务管理器：首先，确保你的 MyBatis 配置中配置了transactionManager type="JDBC"事务管理器，以便启用事务支持。

<configuration>
    <!-- 配置数据源等 -->
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!-- 数据源配置 -->
            </dataSource>
        </environment>
    </environments>
 
    <!-- 映射器配置等 -->
 
</configuration>

编写转账操作的 Mapper 接口和 XML 映射文件：在 Mapper 接口中定义转账操作的方法，然后在 XML 映射文件中编写相应的 SQL 语句，包括行级锁定。

public interface AccountMapper {
    void transfer(@Param("senderId") Long senderId, @Param("receiverId") Long receiverId, @Param("amount") BigDecimal amount);
}

<mapper namespace="com.example.mapper.AccountMapper">
 
    <update id="transfer" parameterType="map">
        START TRANSACTION;
        SELECT * FROM accounts WHERE account_id = #{senderId} FOR UPDATE;
        SELECT * FROM accounts WHERE account_id = #{receiverId} FOR UPDATE;
        UPDATE accounts SET balance = balance - #{amount} WHERE account_id = #{senderId};
        UPDATE accounts SET balance = balance + #{amount} WHERE account_id = #{receiverId};
        COMMIT;
    </update>
 
</mapper>

 

调用 Mapper 方法：在代码中调用 Mapper 接口中定义的方法执行转账操作。

@Autowired
private AccountMapper accountMapper;
 
public void transfer(Long senderId, Long receiverId, BigDecimal amount) {
    accountMapper.transfer(senderId, receiverId, amount);
}

MyBatis 中实现转账过程中的数据锁定。在执行转账操作时，MyBatis 会将相应的 SQL 语句发送到数据库，并在事务中进行处理，同时使用行级锁定来确保数据的一致性和完整性。