MySQL事务及ACID特性详解
事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。
- 原子性(Atomicity) :事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
- 一致性(Consistent) :在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。
- 隔离性(Isolation) :数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
- 持久性(Durable) :事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
并发事务带来的问题
脏写:
当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。脏读:
一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致的状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此作进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。
一句话:事务A读取到了事务B已经修改但尚未提交的数据,还在这个数据基础上做了操作。此时,如果B事务回滚,A读取的数据无效,不符合一致性要求。事务A读取到了事务B已经修改但尚未提交的数据,还在这个数据基础上做了操作。此时,如果B事务回滚,A读取的数据无效,不符合一致性要求。不可重读:
一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”。
一句话:事务A内部的相同查询语句在不同时刻读出的结果不一致,不符合隔离性。幻读:
一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。
一句话:事务A读取到了事务B提交的新增数据,不符合隔离性 。MySQL事务隔离级别详解
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | | —- | —- | —- | —- | | 读未提交(Read uncommitted) | 可能 | 可能 | 可能 | | 读已提交(Read committed) | 不可能 | 可能 | 可能 | | 可重复读取(Repeatable read) | 不可能 | 不可能 | 可能 | | 序列化(Serializable) | 不可能 | 不可能 | 不可能 |
show variables like 'tx_isolation';
set tx_isolation='REPEATABLE-READ';
MySQL锁机制详解
锁分类
- 从性能上分乐观锁(用版本对比来实现)和悲观锁
- 从数据库操作分读锁和写锁(都属于悲观锁)
- 读锁(共享锁):针对同一份数据,多个读锁操作可以同时进行而不会互相影响
- 写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁
-
表锁
每次操作锁住整张表。开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;一般用在整表数据迁移的场景。
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);
lock table mylodk write;
查看表上加过的锁
show open tables;
删除表锁
unlock tables;
行锁
每次操作锁住一行数据。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。
InnoDB与MYISAM的最大不同有两点: InnoDB支持事务(TRANSACTION)
- InnoDB支持行级锁
总结:
MyISAM在执行查询语句SELECT前,会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别),不会加锁。但是update、insert、delete操作会加行锁。
简而言之,就是读锁会阻塞写,但是不会阻塞读。而写锁则会把读和写都阻塞。
行锁与事务隔离级别案例分析
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`balance` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lilei', '450');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei', '16000');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '2400');
读未提交
set tx_isolation='read-uncommitted';
脏读
读已提交
解决脏读
不可重读
可重复读
set tx_isolation='repeatable-read';
解决不可重复读
在客户端A,接着执行update account set balance = balance-50 where id = 1,balance没有变成400-50=350,lilei的balance值用的是步骤2中的350来算的,所以是300,数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制,select操作不会更新版本号,是快照读(历史版本);insert、update和delete会更新版本号,是当前读(当前版本)。
幻读
串行化
set tx_isolation='serializable';
解决幻读
间隙锁
间隙锁,锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read,有办法解决幻读问题吗?间隙锁在某些情况下可以解决幻读问题。
范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据都无法修改数据。
间隙锁是在可重复读隔离级别下才会生效。
临建锁
Next-Key Locks是行锁与间隙锁的组合。
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。并且该索引不能失效,否则都会从行锁升级为表锁。
行锁分析
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
show status like 'innodb_row_lock%';
对各个状态量的说明如下:
- Innodb_row_lock_current_waits: 当前正在等待锁定的数量
- Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
- Innodb_row_lock_time_avg: 每次等待所花平均时间
- Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间Innodb_row_lock_waits:系统启动后到现在总共等待的次数
对于这5个状态变量,比较重要的主要是:
- Innodb_row_lock_time_avg (等待平均时长)
- Innodb_row_lock_waits (等待总次数)
- Innodb_row_lock_time(等待总时长)
查看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;
死锁
set tx_isolation='repeatable-read';
Session_1执行:select from account where id=1 for update;
Session_2执行:select from account where id=2 for update;
Session_1执行:select from account where id=2 for update;
Session_2执行:select from account where id=1 for update;
查看近期死锁日志信息:show engine innodb status\G;
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务,但是有些情况mysql没法自动检测死锁
MySQL锁优化建议
- 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
- 合理设计索引,尽量缩小锁的范围
- 尽可能减少检索条件范围,避免间隙锁
- 尽量控制事务大小,减少锁定资源量和时间长度,涉及事务加锁的sql尽量放在事务最后执行
- 尽可能低级别事务隔离