Mysql在并发下的几个问题及解决

794 阅读5分钟
  1. 表锁导致的慢查询的问题

  2. 线上修改表结构有哪些风险?

  3. 3类数据读问题(脏读,不可重复读,幻读),2类数据更新问题(第一类丢失更新,第二类丢失更新)

  4. 锁等待问题的分析


1.表锁导致的慢查询的问题

果然user表使用了MyISAM存储引擎,MyISAM在执行操作前会产生表锁,操作完成再自动解锁。如果操作是写操作,则表锁类型为写锁,如果操作是读操作则表锁类型为读锁。正如和你理解的一样写锁将阻塞其他操作(包括读和写),这使得所有操作变为串行;而读锁情况下读-读操作可以并行,但读-写操作仍然是串行

解决办法

1)、尽量不用MyISAM存储引擎,在MySQL8.0版本中已经去掉了所有的MyISAM存储引擎的表,推荐使用InnoDB存储引擎。

2)、如果一定要用MyISAM存储引擎,减少写操作的时间;

2.线上修改表结构有哪些风险

如果有一天业务系统需要增大一个字段长度,能否在线上直接修改呢?

在执行DML(select、update、delete、insert)操作时,会对表增加一个元数据锁,这个元数据锁是为了保证在查询期间表结构不会被修改,因此上面的alter语句会被阻塞。

那么如果执行顺序相反,先执行alter语句,再执行DML语句呢?DML语句会被阻塞吗?例如我正在线上环境修改表结构,线上的DML语句会被阻塞吗?答案是:不确定。

总结:如果我们在业务开发过程中有在线修改表结构的需求,可以参考以下方案:

1、尽量在业务量小的时间段进行;

2、查看官方文档,确认要做的表修改可以和DML并发,不会阻塞线上业务;

3、推荐使用percona公司的pt-online-schema-change工具,该工具被官方的online ddl更为强大,它的基本原理是:通过insert… select…语句进行一次全量拷贝,通过触发器记录表结构变更过程中产生的增量,从而达到表结构变更的目的。

例如要对A表进行变更,主要步骤为:

  • 创建目的表结构的空表,A_new;
  • 在A表上创建触发器,包括增、删、改触发器;
  • 通过insert…select…limit N 语句分片拷贝数据到目的表
  • Copy完成后,将A_new表rename到A表。

3. 三类数据读问题(脏读,不可重复读,幻读),2类数据更新问题(第一类丢失更新,第二类丢失更新)

脏读(dirty read)

A事务读取B事务尚未提交的更改数据,并在这个数据基础上操作。如果B事务回滚,那么A事务读到的数据根本不是合法的,称为脏读

理论库存应当有9个,但由于脏读(T4时查出库存9个即为脏读),现有8个

不可重复读(unrepeatable read)

A事务读取了B事务已经提交的更改(或删除)数据。比如A事务第一次读取数据,然后B事务更改该数据并提交,A事务再次读取数据,两次读取的数据不一样。

事务A在T2和T4读出的数据不同,即是不可重复度

幻读(phantom read)

A事务读取了B事务已经提交的新增数据。注意和不可重复读的区别,这里是新增,不可重复读是更改(或删除)

  • 这两种情况对策是不一样的,对于不可重复读,只需要采取行级锁防止该记录数据被更改或删除,
  • 然而对于幻读必须加表级锁,防止在这个表中新增一条数据

当然,也可以将数据库事务隔离级别设为serializable,但一般不这样做,因为该策略是完全阻塞的,将对数据库的访问完全序列化,并发性能最差。

T5时数据莫名其妙有了,就是幻读现象。如果A事务只进行读操作,不进行写操作,将数据库事务隔离级别设为repeatable read,并用start transaction with consistent snapshot开启事务,同时进行快照读,也可以防止幻读现象(因为在可重读策略下,不是开启事务就建立快照点,而是在第一次查询时建立快照点)。

丢失更新

  • 第一类丢失更新:A事务提交时,把已提交的B事务的数据覆盖掉。

理论库存是7个,但现在剩余8个;

  • 第二类丢失更新:A事务回滚时,把已提交的B事务的数据覆盖掉。

理论库存是9个,但现在剩余10个;

丢失更新完全靠事务是无法解决的,此时就需要结合数据库悲观锁来防止此类问题,操作如下:

//0.开始事务

begin;/begin work;/start transaction; (三者选一就可以)

//1.查询出商品信息

select status from t_goods where id=1 for update;(添加排他锁)

//2.根据商品信息生成订单

insert into t_orders (id,goods_id) values (null,1);

//3.修改商品status为2

update t_goods set status=2;

//4.提交事务

commit;/commit work;