-
表锁导致的慢查询的问题
-
线上修改表结构有哪些风险?
-
3类数据读问题(脏读,不可重复读,幻读),2类数据更新问题(第一类丢失更新,第二类丢失更新)
-
锁等待问题的分析
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事务的数据覆盖掉。
- 第二类丢失更新:A事务回滚时,把已提交的B事务的数据覆盖掉。
丢失更新完全靠事务是无法解决的,此时就需要结合数据库悲观锁来防止此类问题,操作如下:
begin;/begin work;/start transaction; (三者选一就可以)
select status from t_goods where id=1 for update;(添加排他锁)
insert into t_orders (id,goods_id) values (null,1);
update t_goods set status=2;
commit;/commit work;