面试题：MySQL行级锁在高并发写操作场景下如何优化应用

锁的粒度控制

精确锁定必要行：
- 在电商库存扣减场景中，查询库存和扣减库存的SQL语句应尽量精准定位到具体的商品库存行。例如，假设库存表结构为products (id, product_name, stock)，扣减库存的SQL语句可以这样写：
```
-- 使用商品id精确锁定对应的行
UPDATE products
SET stock = stock - 1
WHERE id = {specific_product_id}
AND stock >= 1
FOR UPDATE;
```
这样能避免锁定不必要的行，减少锁争用。
合理使用索引：
- 对用于锁定行的字段（如商品id）建立索引。索引可以加快查询速度，使MySQL能快速定位到需要锁定的行。例如，对于上述库存表，在id字段上创建索引：
```
CREATE INDEX idx_product_id ON products (id);
```
这样在执行UPDATE... FOR UPDATE语句时，MySQL能通过索引快速找到对应行并加锁，而不是全表扫描，从而减少锁等待时间。

事务处理

缩短事务时长：

只将必要的操作放在事务内。例如，在库存扣减场景中，事务内只包含查询库存和扣减库存的操作，避免在事务内进行一些耗时的业务逻辑处理，如复杂的计算、调用外部接口等。

// Java示例代码
try {
    Connection conn = DriverManager.getConnection(url, username, password);
    conn.setAutoCommit(false);
    PreparedStatement pstmt = conn.prepareStatement("UPDATE products SET stock = stock - 1 WHERE id =? AND stock >= 1 FOR UPDATE");
    pstmt.setInt(1, productId);
    int rowsUpdated = pstmt.executeUpdate();
    if (rowsUpdated == 0) {
        // 库存不足，回滚事务
        conn.rollback();
    } else {
        // 库存扣减成功，提交事务
        conn.commit();
    }
} catch (SQLException e) {
    e.printStackTrace();
}

优化事务顺序：
- 如果存在多个库存扣减操作，尽量按照相同的顺序执行事务。例如，所有库存扣减事务都按照商品id从小到大的顺序处理，这样可以减少死锁的发生概率。因为如果事务按照不同顺序访问资源，可能会形成死锁环，而统一顺序可以避免这种情况。
使用乐观锁：
- 可以在库存表中添加一个版本号字段version。在扣减库存时，先读取当前库存和版本号，扣减库存后，更新库存并同时更新版本号，条件是版本号未发生变化。
```
-- 先查询当前库存和版本号
SELECT stock, version FROM products WHERE id = {specific_product_id};
-- 假设查询到的库存为stock_value，版本号为version_value
-- 扣减库存并更新版本号
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = {specific_product_id}
AND stock >= 1
AND version = {version_value};
```
如果更新成功，说明在读取库存和更新库存之间没有其他事务修改过库存；如果更新失败，说明有其他事务已经修改了库存，此时可以重试扣减操作。乐观锁在高并发场景下可以减少锁争用，因为大多数情况下不需要加锁，只有在更新库存时才进行版本号校验。

其他优化

缓存优化：

可以使用缓存（如Redis）来减轻MySQL的压力。在库存扣减操作前，先从缓存中查询库存数量，进行预判断。如果缓存中的库存足够，直接在缓存中扣减库存，并异步更新MySQL中的库存。这样可以减少直接对MySQL的高并发写操作，降低锁争用。例如，在Java中使用Jedis操作Redis：

Jedis jedis = new Jedis("localhost", 6379);
String stockStr = jedis.get("product:" + productId + ":stock");
if (stockStr!= null) {
    int stock = Integer.parseInt(stockStr);
    if (stock >= 1) {
        jedis.decrBy("product:" + productId + ":stock", 1);
        // 异步更新MySQL库存
        new Thread(() -> {
            // 执行更新MySQL库存的操作
        }).start();
    }
}

数据库连接池：
- 使用数据库连接池（如HikariCP）来管理数据库连接。连接池可以复用连接，减少创建和销毁连接的开销，提高系统的性能和并发处理能力。在高并发写操作场景下，频繁创建和销毁数据库连接会消耗大量资源，而连接池可以有效地解决这个问题。
```
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/yourdatabase");
config.setUsername("username");
config.setPassword("password");
HikariDataSource dataSource = new HikariDataSource(config);
Connection conn = dataSource.getConnection();
```

星途面试题库

面试题：MySQL行级锁在高并发写操作场景下如何优化应用

知识考点

面试题答案

锁的粒度控制

事务处理

其他优化