一、事务及其AICD属性
事务是由一组sql组成的逻辑处理单元,具有其四种属性:
1.原子性
事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
2.一致性
在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。
3.隔离性
数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
4.持久性
事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
二、并发事务处理带来的问题及事务隔离级别
事务隔离级别总体简介
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交 | 可能 | 可能 | 可能 |
读已提交 | 不可能 | 可能 | 可能 |
可重复读 | 不可能 | 不可能 | 可能 |
可串行化 | 不可能 | 不可能 | 不可能 |
准备示例表:
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 ('aaa', '450');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('bbb', '160');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('ccc', '240');
1.脏读
a.介绍
一个事务对一条数据进行修改,当前事务未提交时,另一个事务读取了这条数据修改前后的值,导致读取的值不同。现象如下:
分别开启客户端A
由于mysq事务隔离级别l默认开启的是可重复读,所以为了演示脏读现象,则需要开启读未提交
#开启读未提交的事务隔离级别
set tx_isolation='read-uncommitted';
begin;
select * from account;
结果如上图,客户端A不要关闭。
开启客户端B
set tx_isolation='read-uncommitted';
begin;
update account set name='a1' where id =1;
select * from account;
如上图所示:客户端B将id为1的name值修改为a1,当前事务未提交,也就是数据库中存的值还是aaa。
我们再打开客户端A,继续查询表中的值:
由上图可看出,客户端B开启事务进行修改数据,未提交事务时,客户端A可以读取客户端B修改后的值。这明显有问题。
b.解决方式
mysql数据针对脏读问题,提供读已提交的事务隔离级别。
客户端A
#读已提交
set tx_isolation='read-committed';
begin;
客户端B
#读已提交
set tx_isolation='read-committed';
begin;
update account set name='a1' where id =1;
修改值后,未提交事务:
客户端A:查询表信息,看是否更改。从下图可以看出,在客户端B未提交事务时,客户端A这里查询不到变化。
而当客户端B提交事务后,客户端A可以查询到变化(在读已提交的事务隔离级别下,读取的是数据库的实时数据)。而这又导致另一个问题:不可重复读。
2.不可重复读
a.介绍
当一个事务修改了数据,提交事务后,导致另一个事务再读数据时,前后读取的值不一致。这种现象违反事务彼此的隔离性。
b. 解决方式
mysql为了解决可重复读,提供可重复读的事务隔离级别
#开启可重复读
set tx_isolation='repeatable-read';
打开客户端A、B按上面方式进行验证。
3.幻读
a.介绍
同时打开客户端A、B,分别开启事务;当客户端A事务新增一条数据,提交事务后,客户端B查询整个表记录是查询不到客户端A新增的那条记录的。但是,当在客户端B执行更新新增的那条记录时,居然成功。这种现象就是幻读。
b. 解决方式
可启动可串行化隔离级别,解决幻读问题
#开启可串行化
set tx_isolation='serializable';
打开客户端A、B按上面方式进行验证。
验证后,会发现:当客户端A执行查询时未提交事务的情况下,客户端B不能对客户端A事务查询的数据做操作。
当开启可串行化后,事务中对数据操作时,会对操作行进行加锁,再提交事务,释放锁后,其他事务才能对该数据进行操作。
三、锁
- 从性能上分为乐观锁(用版本对比来实现)和悲观锁
- 从对数据库操作的类型分,分为读锁和写锁(都属于悲观锁)
- 读锁(共享锁,S锁(Shared)):针对同一份数据,多个读操作可以同时进行而不会互相影响
写锁(排它锁,X锁(eXclusive)):当前写操作没有完成前,它会阻断其他写锁和读锁 - 从对数据操作的粒度分,分为表锁和行锁
1.行锁
每次操作锁住一行数据。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。
2.表锁
每次操作锁住整张表。开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;一般用在整表数据迁移的场景。
- 手动增加表锁
lock table 表名称 read(write),表名称2 read(write); - 查看表上加过的锁
show open tables; - 删除表锁
unlock tables;
3.间隙锁
客户端A、B开启可重复读隔离级别,同时开始事务,在客户端B执行范围更新操作,在客户端A执行插入范围内的数据,需要等待客户端B的事务提交后才能执行成功。
客户端A
客户端B
由上图看出,表中记录id间隙有(4,7),9,(10,正无穷),客户端B执行id范围(0,8)的数据更新,涉及到的间隙范围有(4,7),则会将其范围内的记录加锁,这就是所谓的间隙锁。
如果记录id的间隙为(5,9),(12,30),(40,正无穷),而客户端执行id范围(8,20)的数据更新,涉及到的间隙范围有(5,9),(12,30),会将这些范围内的记录都加锁,其他事务无法对其修改、新增。
4.临键锁(Next-key Locks)
Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(4,7]的整个区间可以叫做临键锁。
5.无索引行锁会升级为表锁
- 锁主要是加在索引上,如果对非索引字段更新,行锁可能会变表锁
session1 执行:update account set balance = 800 where name = ‘lilei’;
session2 对该表任一行操作都会阻塞住
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。并且该索引不能失效,否则都会从行锁升级为表锁。
四、查看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;