文章目录
提要
- MySQL事务及ACID特性详解
- MySQL事务隔离级别详解
- MySQL锁机制详解
- MySQL锁优化建议
思维导图
MySQL 事务概述
- 数据库一般都并发执行多个事务,事务及其ACID属性,多个事务可能会并发的对相同的一批数据进行增删改查操作,可能就会导致我们说的脏写、脏读、不可重复读、幻读问题
- 为了解决多事务并发问题,数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制
事务及其ACID属性
什么是事务ACID属性
事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性
- 原子性(Atomicity) :事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行
- 一致性(Consistent) :在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。
- 隔离性(Isolation) :数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
- 持久性(Durable) :事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
并发事务处理带来的问题
- 更新丢失(Lost Update)或脏写
- 最后的更新覆盖了由其他事务所做的更新
- 脏读(Dirty Reads)
- 事务A读取到了事务B已经修改但尚未提交的数据,不符合一致性要求
- 不可重读(Non-Repeatable Reads)
- 事务A内部的相同查询语句在不同时刻读出的结果不一致,不符合隔离性
- 幻读(Phantom Reads)
- 事务A读取到了事务B提交的新增数据,不符合隔离性
事务隔离级别
为什么要事务隔离?
“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决
隔离级别详情
事务隔离级别分析
数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大
Mysql默认的事务隔离级别是可重复读,用Spring开发程序时,如果不设置隔离级别默认用Mysql设置的隔离级别,如果Spring设置了就用已经设置的隔离级别
隔离分类
- 读未提交(read-uncommitted)
开启:set tx_isolation='read-uncommitted'
- 读已提交(read-committed)
开启:set tx_isolation='read-committed
- 可重复读(repeatable-read)
开启:set tx_isolation='repeatable-read';
- 串行化(serializable)
开启:set tx_isolation='serializable';
查看当前事务隔离级别
show variables like 'tx_isolation'
设置事务隔离级别
set tx_isolation='REPEATABLE-READ';
锁详解
锁是计算机协调多个进程或线程并发访问某一资源的机制。
锁的分类
- 从性能上分为乐观锁(用版本对比来实现)和悲观锁
- 从对数据库操作的类型分,分为读锁和写锁(都属于悲观锁)
- 读锁(共享锁,S锁(Shared)):针对同一份数据,多个读操作可以同时进行而不会互相影响
- 写锁(排它锁,X锁(eXclusive)):当前写操作没有完成前,它会阻断其他写锁和读锁
- 从对数据操作的粒度分,分为表锁和行锁
锁的具体分析
表锁
每次操作锁住整张表。开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;一般用在整表数据迁移的场景。
建表:
‐‐建表SQL
CREATE TABLE `mylock` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR (20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
‐‐插入数据
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');
手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
查看表上加过的锁
show open tables;
删除表锁
unlock tables;
加读锁和加写锁
加读锁
- 当前session和其他session都可以读该表
- 当前session中插入或者更新锁定的表都会报错,其他session插入或更新则会等待
加写锁
- 当前session对该表的增删改查都没有问题,其他session对该表的所有操作被阻塞
总结:
- 对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作
- 对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作
行锁
每次操作锁住一行数据。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高
InnoDB与MYISAM的最大不同有两点:
- InnoDB支持事务(TRANSACTION
- InnoDB支持行级锁
行锁演示:
一个session开启事务更新不提交,另一个session更新同一条记录会阻塞,更新不同记录不会阻塞
行锁分析
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
#SQL语句:
show status like 'innodb_row_lock%';
查看INFORMATION_SCHEMA系统库锁相关数据表
‐‐ 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
‐‐ 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
‐‐ 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
‐‐ 释放锁,trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id
‐‐ 查看锁等待详细信息
show engine innodb status\G;
状态量说明
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度(等待总时长)
Innodb_row_lock_time_avg: 每次等待所花平均时间(等待平均时长)
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间Innodb_row_lock_waits:系统启动后到现在总共等待的次数(等待总次数)
当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手制定优化计划。
总结:
- MyISAM在执行查询语句SELECT前,会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。
- InnoDB在执行查询语句SELECT时(非串行隔离级别),不会加锁。但是update、insert、delete操作会加行锁。
- 读锁会阻塞写,但是不会阻塞读。而写锁则会把读和写都阻塞
无索引行锁会升级为表锁
- 锁主要是加在索引上,如果对非索引字段更新,行锁可能会变表锁
- InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。并且该索引不能失效,否则都会从行锁升级为表锁。
间隙锁
间隙锁,锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read,某些情况下可以解决幻读问题
临建锁
Next-Key Locks是行锁与间隙锁的组合
死锁
思考: 什么是死锁?
死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方的资源,从而导致恶性循环的现象,需要四个条件,互斥条件,不剥夺条件,请求和保持条件,循环等待条件
死锁怎么处理?
如何解决:
- 将陷入死锁的进程全部撤销。
- 逐个作废死锁进程,直至死锁不再存在。
- 从死锁进程中逐个地强迫抢占其所占资源,直至死锁不存在
预防死锁:
- 资源一次性分配:一次性分配所有资源,这样就不会再有请求了:(破坏请求条件)
- 只要有一个资源得不到分配,也不给这个进程分配其他的资源:(破坏请保持条件)
- 可剥夺资源:即当某进程获得了部分资源,但得不到其它资源,则释放已占有的资源(破坏不可剥夺条件)
- 资源有序分配法:系统给每类资源赋予一个编号,每一个进程按编号递增的顺序请求资源,释放则相反(破坏环路等待条件)
- 以确定的顺序获得锁
- 超时放弃
避免死锁:
预防死锁的几种策略,会严重地损害系统性能。因此在避免死锁时,要施加较弱的限制,从而获得 较满意的系统性能。由于在避免死锁的策略中,允许进程动态地申请资源。因而,系统在进行资源分配之前预先计算资源分配的安全性。若此次分配不会导致系统进入不安全的状态,则将资源分配给进程;否则,进程等待。其中最具有代表性的避免死锁算法是银行家算法。
检测死锁
- 首先为每个进程和每个资源指定一个唯一的号码;
- 然后建立资源分配表和进程等待表。
解除死锁:
- 剥夺资源:从其它进程剥夺足够数量的资源给死锁进程,以解除死锁状态
- 撤消进程:可以直接撤消死锁进程或撤消代价最小的进程,直至有足够的资源可用,死锁状态.消除为止;所谓代价是指优先级、运行代价、进程的重要性和价值等
锁优化建议
- 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
- 合理设计索引,尽量缩小锁的范围
- 尽可能减少检索条件范围,避免间隙锁
- 尽量控制事务大小,减少锁定资源量和时间长度,涉及事务加锁的sql尽量放在事务最后执行
- 尽可能低级别事务隔离