首页 > 分享 > MySQL之从锁的角度看update(二)

MySQL之从锁的角度看update(二)

花匠小妙招
2024-12-23 14:44

背景

笔者前段时间接了购物车的需求，其中用户有段逻辑更改购物车商品的数量后，然后更新到数据库。因为笔者项目有两台机子，nginx做负载均衡，所以更新DB的加了分布式的锁。后来笔者后知后觉，觉得完全没有必要。

问题

首先看笔者代码：

//1.根据用户ID以及商品编码定位数据 ShoppingCartWaresDO waresDOFromDb = shoppingCartWaresDAO.selectByUniqueIndex(waresDO); if (waresDOFromDb == null) { shoppingCartWaresMapper.insert(waresDO); } else if (waresDOFromDb.getId() != null) { waresDO.setId(waresDOFromDb.getId()); //2. 数量累加 int newNum = waresDOFromDb.getWaresNumber() + waresDO.getWaresNumber(); waresDO.setWaresNumber(newNum); try { //3. 加分布式锁，根据主键ID锁行 this.distributedLock(waresDO.getId()); //4. 更新数据库 shoppingCartWaresMapper.updateById(waresDO); } finally { redisLock.unlock(UPDATE_WARES_NUM_LOCK_KEY_PREFIX + waresDO.getId()); } }

12345678910111213141516171819 问题

Q1. 这里先查询，加锁再更新。假如有A、B两个线程，原始数量为1，A先查询(累加1，累加后为2)，B后查询(累加1，累加后为2)，但是B却先于A拿到锁，所以B先更新，数据库是2，由于A累加后的结果也是2，所以最终结果是2，而不是期望的3。

A1: 我们可以把加锁提前

ShoppingCartWaresDO waresDOFromDb = shoppingCartWaresDAO.selectByUniqueIndex(waresDO); try { this.distributedLock(waresDO.getId()); //这里再次查询是因为，获取到锁的这段时间，其他线程会对对应ID的记录进行写操作，改变数据 waresDOFromDb = shoppingCartWaresDAO.selectById(waresDO.getId()); if (waresDOFromDb == null) { shoppingCartWaresMapper.insert(waresDO); } else if (waresDOFromDb.getId() != null) { waresDO.setId(waresDOFromDb.getId()); int newNum = waresDOFromDb.getWaresNumber() + waresDO.getWaresNumber(); waresDO.setWaresNumber(newNum); shoppingCartWaresMapper.updateById(waresDO); } finally { redisLock.unlock(UPDATE_WARES_NUM_LOCK_KEY_PREFIX + waresDO.getId()); } }

12345678910111213141516

A2: 或者SQL这样写：update wares set wares_num = wares_num + #{rised_num} where id = #{id},连分布式锁都不用加。因为MySQL执行update时会对对应的行加行锁（排他锁，X锁），如果当前的update没有执行完，其他update会被阻塞（两个update更新数据有相同的行），不用担心高并发的问题。

验证A2

高并发方法验证：

我们数据库有如下数据：

mysql> select * from goods; +----+------------+--------------+-------+-------+ | id | code | name | price | stock | +----+------------+--------------+-------+-------+ | 1 | DRINK_0001 | 可口可乐 | 0.00 | 0 | | 2 | SNACK_0001 | 卫龙辣条 | 2.50 | 10 | +----+------------+--------------+-------+-------+ 2 rows in set (0.00 sec) 12345678

我们对id=1的可乐涨价，单次操作涨价一块。使用Java线程池执行，线程池submit 1000个线程。注意：线程过多会OOM，笔记的笔记本有12个线程，也就说在一个极短的时段，最多会有12个同样的操作。我们看看，1000次操作后是不是涨到了1000块。

@Override public void highConcurrencyUpdate(int threads) { if (threads <= 0) { threads = 2; } ExecutorService executorService = new ThreadPoolExecutor(threads, threads, 0L, TimeUnit.SECONDS, new ArrayBlockingQueue<>(threads)); HashMap<Integer, Future> map = new HashMap<>(); for (int i = 0; i < threads; i++) { Future future = executorService.submit(() -> { goodsMapper.risePriceById(1L, new BigDecimal("1")); }); map.put(i, future); } //等待线程中线程执行完，Junit main线程一旦退出，子线程也会被kill调，跟Java main函数执行还是有区别的 while (map.size() > 0) { List<Integer> finishThreadNoList = new ArrayList<>(map.size()); for (Entry<Integer, Future> e : map.entrySet()) { if (futureGet(e.getValue())) { finishThreadNoList.add(e.getKey()); } } for (Integer i : finishThreadNoList) { map.remove(i); } sleep(1L); } } public static boolean futureGet(Future future) { if (future != null) { try { future.get(); return true; } catch (Exception e) { } } return false; }

12345678910111213141516171819202122232425262728293031323334353637383940

执行后进行查询，结果如下：

mysql> select * from goods; +----+------------+--------------+---------+-------+ | id | code | name | price | stock | +----+------------+--------------+---------+-------+ | 1 | DRINK_0001 | 可口可乐 | 1000.00 | 0 | | 2 | SNACK_0001 | 卫龙辣条 | 2.50 | 10 | +----+------------+--------------+---------+-------+ 2 rows in set (0.00 sec) 12345678

由于单机的内存线程有限，在上述并发条件下是没有问题的。

百万数据验证

我们数据库有如下数据：

mysql> SELECT * FROM goods LIMIT 3; +----+--------+-------+-------+ | id | name | price | stock | +----+--------+-------+-------+ | 1 | 花生 | 5.00 | 1 | | 2 | 土豆 | 5.00 | 20 | | 3 | 牛肉 | 5.00 | 30 | +----+--------+-------+-------+ 3 rows in set (0.00 sec) 123456789

我们往数据库插入上百万个(| 花生 | 5.00 | 1 |)数据，这样根据name update上百万的数据，这样update就会非常慢，我们在两个MySQL终端上几乎同时执行update(手速的原因，差个1 、2秒也没关系，但不能上一个update执行完后，你下个update没执行)，然后我们观察update后的数据。

制造数据

蠕虫复制

insert into goods (name, price, stock) select name, price, stock from goods 1

数据是呈指数性增长，稍微执行几次，表里就百万数据了。

开始验证

UPDATE goods set price='7' WHERE name='花生'; UPDATE goods set price='6' WHERE name='花生'; 12

思路：百万数据的情况下，如果update没有加锁，应该是两个update同时执行，数据库里花生应该有6块也有7块的。

步骤1

A终端中执行：

mysql> select count（1）from goods; ERROR 1054 (42S22): Unknown column 'count（1）from' in 'field list' mysql> select count(1) from goods; +----------+ | count(1) | +----------+ | 19213799 | +----------+ 1 row in set (2.63 sec) mysql> SELECT name,price FROM goods GROUP BY name,price; +--------+-------+ | name | price | +--------+-------+ | 土豆 | 5.00 | | 牛肉 | 5.00 | | 花生 | 5.00 | +--------+-------+ 3 rows in set (9.53 sec) mysql> UPDATE goods set price='7' WHERE name='花生';

123456789101112131415161718192021

我们可以看到表有近2000万数据，并且所有花生的价格都是5块。

步骤2

A终端中执行update时，立即在B终端执行另外一个update，如下：

mysql> UPDATE goods set price='6' WHERE name='花生'; 1

过一会儿，两个update都执行好了，如下
A终端：

mysql> UPDATE goods set price='7' WHERE name='花生'; Query OK, 6404600 rows affected (17.77 sec) Rows matched: 6404600 Changed: 6404600 Warnings: 0 123

花了17秒

B终端：

mysql> UPDATE goods set price='6' WHERE name='花生'; Query OK, 6404600 rows affected (32.71 sec) Rows matched: 6404600 Changed: 6404600 Warnings: 0 123

花了32秒

步骤3

A终端执行查询语句：

mysql> SELECT name,price FROM goods GROUP BY name,price; +--------+-------+ | name | price | +--------+-------+ | 土豆 | 5.00 | | 牛肉 | 5.00 | | 花生 | 6.00 | +--------+-------+ 3 rows in set (9.45 sec) 123456789

我们看到，最终所有花生的价格都是6块。我们可以看出所有花生的价格先被更新为7块，最后又都被更新到6块。

结论：
MySQL update是加了排他锁的。