mysql事务_读提交怎么实现

1、数据库事务的4个特点（ACID原则）

原子性（atomicity [ˌætəˈmɪsəti]）：当前事务要么同时成功，要么同时失败，原子性由undo log来保证
一致性（consistency [kənˈsɪstənsi]）：使用事务的最终目的，由业务代码的正确逻辑保证
隔离性：（Isolation [ˌaɪsəˈleɪʃn]）：在事务并发执行时（操作相同的数据），它们之间的操作不能相互干扰
持久性：（durablity）:一旦提交了事务，他对数据库的影响应该是永久的，持久性由redo log来保证

2、隔离的四个级别

隔离级别越高，事务隔离性越好，但性能越低，而隔离性是由各种锁以及MVCC机制来实现的
读未提交（read uncommit）:有脏读问题、可能有不可重复读和幻读问题
读已提交(read commit)：有不可重复读问题（事务操作过程中，不同时间段内读到的数据可能是不同的），可能有幻读问题
可重复读(repeatable read)：有幻读问题（一般使用这种）
串行(serializable)：解决所有问题（效率太低，基本不用）

脏读：当我们处于读未提交事务隔离级别时，一个事务可以读到另一个事务未提交的数据，但若这条未提交的数据进行了回滚，就会导致刚刚读到的数据有问题，也就是读到了脏数据，而这时，我们若是使用这条脏数据进行更新操作，就会导致脏写。
不可重复读：当我们处于读已提交的隔离级别，在同一个事务内，我们在不同时间内查询得到的数据结果是不同的，这就是不可重复读，导致的原因，可能是另一个事务在这个事务操作的过程中完成了对于数据的更新操作。
幻读：在可重复读的隔离级别内，若我们在一个事务中查询了这个表的数据，在接下来的查询过程中，就都是这个数据，无论另一个表是否做了更新操作，但是当我们在最前面的这个事务中执行了更新新数据的操作，还是可以操作成功，若这时再次查询，就会查到最新的数据，也就是我们说的幻读。

加锁机制
前3种隔离级别较低，在涉及到更新、查询操作的过程中，
若第一个事务正在查询数据，不会加读锁和写锁，不影响第二个事务的更新和查询，
若第一个事务正在修改一行数据，则会将这一行的数据加一个行锁，阻止其他事务对其进行更新操作，但还是可以进行查询
但第4种隔离级别很高，
一旦一个事务进行了某一个操作，就会阻塞其他事务的所有操作，就会导致效率较低

2.1、新建数据库表

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `account` decimal(65,10) DEFAULT NULL COMMENT '银行账户',
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES (1, 'li', 18, 'test1@baomidou.com', 1000.0000000000);
1
2
3
4
5
6
7
8
9
10

2.2、执行sql

读未提交示例

开两个窗口代表两个事务
窗口1
account现在为1000

BEGIN;

UPDATE `user` set account=account+1000 WHERE id = 1;
1
2
3

窗口2

set tx_isolation='READ-UNCOMMITTED';

BEGIN;

SELECT * FROM `user` WHERE id = 1;
1
2
3
4
5

发现读到的account数据是2000，但我们窗口1的事务还未提交，有脏读问题（可以读到未提交的数据）
脏读的影响：若读到一条未提交的数据，且该条数据在执行的过程中出现了问题会滚了，会导致查询的结果不准确。
实验结束，提交事务避免影响下面的实验
窗口1和窗口2都执行

COMMIT;
1

读已提交示例

开两个窗口代表两个事务
窗口1
account现在为1000

BEGIN;

UPDATE `user` set account=account+1000 WHERE id = 1;

1
2
3
4

窗口2

set tx_isolation='READ-COMMITTED';

BEGIN;

SELECT * FROM `user` WHERE id = 1;
1
2
3
4
5

发现读到的account数据还是1000，
窗口1提交

COMMIT;
1

窗口2执行

SELECT * FROM `user` WHERE id = 1;
1
2

这时account变为2000，只能读到已经提交的事务，
但会出现一个问题：若再次让account加1000，执行窗口1得查询语句，读到的结果变为3000，就会导致很迷茫，不知道到底该用什么时候的数据

最后提交所有事务

COMMIT;
1

可重复读示例

开两个窗口代表两个事务
account现在为1000

窗口1

set tx_isolation='REPEATABLE-READ';

BEGIN;

SELECT * FROM `user` WHERE id = 1;
1
2
3
4
5

account查询结果为1000

窗口2

BEGIN;

UPDATE `user` set account=account+1000 WHERE id = 1;

COMMIT;
1
2
3
4
5

account的数据实际变为2000

再次在窗口1执行查询
窗口1

SELECT * FROM `user` WHERE id = 1;
1

发现读到的account数据还是1000，无论怎么更新，我们查询得到的数据都是1000，这就是可重复读的问题

窗口1提交

COMMIT;
1

串行

开两个窗口代表两个事务
窗口1
account现在为1000

BEGIN;

UPDATE `user` set account=account+1000 WHERE id = 1;

1
2
3
4

窗口2

set tx_isolation='SERIALIZABLE';

BEGIN;

SELECT * FROM `user` WHERE id = 1;
1
2
3
4
5

发现无法获得结果，前一个事务未提交，这一个事务就会被阻塞，只有当前一个事务提交之后才能拿到这个事物的结果

窗口1提交

COMMIT;
1

窗口2得到了结果

3、可重复读解决办法

乐观锁、悲观锁（解决读到快照数据，导致的数据覆盖问题）

乐观锁：给更新的数据加一个版本号，每次更新数据给该数据的版本号加1，乐观锁一般写一个cas去实现
悲观锁：使用数据库字段计算（例如：account = account - 200），而不是使用java后台算的结果去更新

4、使用场景

读已提交：高并发的场景使用较多，如各种电商平台
可重复读：ERP，这种有多个报表，要求读到的数据是同一时间维度的，对并发要求没那么高的

5、数据库中的锁

从性能上分为乐观锁(用版本对比来实现)和悲观锁
从对数据库操作的类型分，分为读锁和写锁 (都属于悲观锁)
读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分，分为表锁和行锁
表锁
每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
一般用在整表数据迁移的场景。

间隙锁(Gap Lock)
间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，间隙锁在某些情况下可以解决幻读问题。

临键锁(Next-key Locks)
Next-Key Locks是行锁与间隙锁的组合。

无索引行锁会升级为表锁
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为
表锁。

6、MVCC机制（多版本并发控制机制）

事务的隔离性主要是通过MVCC机制来实现的，mysql中的读已提交和可重复读两种隔离级别实现了mvcc机制，MVCC机制保证了在并发条件下，不用频繁的加锁，使得mysql的性能有了巨大的优化。

6.1 MVCC

mysql主要通过undo日志和read view机制来实现mvcc机制，当我们执行一条sql语句时->就会帮我们生成一个对应的read view，并将事务过程中的数据写入到undo日志中，read view主要是根据多个事务影响这条数据过程中生成的事务id所组成的一个数组，readview机制主要包含最小事务id、最大事务id、已执行的事务id，然后根据mysql的比对规则，每次查询这条数据时，就会比对一次，然后undo日志中的这条数据是否是我们要展示的，直到遍历到我们需要的这条数据。

6.2 bufferPool

一条更新id为1的语句->执行mysql的公用service部分（优化器、执行器等）->进入到Innodb搜索引擎- >首先从.ibd文件将id为1的数据所在的page从磁盘中加载到bufferPool中->然后将原始的sql语句写入到undo日志中（版本控制链，用来记录所有在事务操作过程中的sql变化，一旦涉及到回滚操作，将会直接使用undo日志中的内容回滚到bufferPool中）->修改bufferPool中的这条数据->当我们要提交事务时，我们会把这条更新后的数据写入到redo日志中（加入事务正在执行，遇到突然宕机的情况，就会使用redo日志中的数据恢复bufferPool中的数据）->写入到binLog日志中（service层，undo日志和redo日志是innodb搜索引擎专属的，binLog日志主要用来处理一些诸如删库跑路的操作）->接下来才会将commit标记写入到redo日志中（保证binlog与redo日志数据的一致性）->最后将bufferPool中的数据写入到磁盘（通过io随机写入）