Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

03 | 事务隔离:为什么你改了我还看不见? #8

Open
git-zjx opened this issue Jul 16, 2019 · 2 comments
Open

03 | 事务隔离:为什么你改了我还看不见? #8

git-zjx opened this issue Jul 16, 2019 · 2 comments
Assignees
Projects

Comments

@git-zjx
Copy link
Owner

@git-zjx git-zjx commented Jul 16, 2019

简介

事务用于保证一组操作要么全部成功,要么全部失败。
在 MySQL 中事务实现于存储引擎层。

隔离性与隔离级别

当数据库有多个事务同时执行时,就可能出现 脏读(dirty read)、不可重复读(not-repeatable read)、幻读(phantom read)的问题,为了解决这些问题,引入了隔离级别的概念。

SQL 标准的隔离级别包括:

  1. 读未提交(read uncommitted),一个事务还未提交时,它做的变更别的事务就能看到
  2. 读提交(read committed),一个事务提交之后,它做的变更别的事务才能看到
  3. 可重复读(repeatable read),一个事务执行过程中看到的数据,始终和事务启动时看到的数据一致
  4. 串行化(serializable),对于同一行记录,写时会加写锁,读时会加读锁,后访问的事务必须等待前一个事务执行完成之后才能继续执行

事务隔离的实现

在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准。
在“可重复读”隔离级别下,这个视图是在事务启动时创建的,整个事务存在期间都用这个视图。
在“读提交”隔离级别下,这个视图是在每个 SQL 语句开始执行的时候创建的。
这里需要注意的是,“读未提交”隔离级别下直接返回记录上的最新值,没有视图概念;而“串行化”隔离级别下直接用加锁的方式来避免并行访问

d9c313809e5ac148fc39feff532f0fee

在 MySQL 中,每条记录的更新都会同时记录一条回滚操作,记录上的最新值通过回滚操作都可以得到前一个状态的值。同一条记录可以在数据库中有多个版本,这就是数据库的多版本并发控制(MVCC)
回滚日志不会一直保留,当系统里没有比这个回滚日志更早的 read-view 时会被删除,因为这时已经没有事务需要使用这个回滚日志了。

为什么尽量不使用长事务?

长事务就意味着数据库中存在很老的事务视图,而由于这些事务可能随时访问数据库的任何数据,所以这些事务提交前,数据库里它们可能用到的回滚日志都必须保留,会占用大量空间。

在 MySQL 5.5 及以前的版本,回滚日志是跟数据字典一起放在 ibdata 文件里的,即使长事务最终提交,回滚段被清理,文件也不会变小。我见过数据只有 20GB,而回滚段有 200GB 的库。最终只好为了清理回滚段,重建整个库

除了对回滚段的影响,长事务还占用锁资源

事务的启动方式

  1. 显式启动,beginstart transactioncommit 提交,rollback 回滚
  2. set autocommit=0,关闭自动提交,意味着执行一个语句就会开启事务,且这个事务不会自动提交,直到执行 commit rollback,或者断开连接

set autocommit=0 可能会意外的导致长事务,建议总是使用 set autocommit=1

set auotcommit=1 的情况下,使用 begin 启动事务,如果执行 commit 则提交事务,如果执行 commit work and chain 则提交事务并自动启动下一事务,适用于需要频繁使用事务的业务

@git-zjx git-zjx added this to MySQL实战45讲 in MySQL Jul 16, 2019
@git-zjx git-zjx self-assigned this Dec 16, 2019
@git-zjx
Copy link
Owner Author

@git-zjx git-zjx commented Feb 25, 2020

查询长事务的方式

可以在 information_schema 库的 innodb_trx 表中查询长事务,以下语句查询持续时间超过 60s 的事务:

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

@git-zjx
Copy link
Owner Author

@git-zjx git-zjx commented Feb 25, 2020

如何避免长事务对业务的影响?

从开发端看:

  • 确认是否使用了 set auotcommit=0
  • 确认是否有不必要的只读事务
  • 业务连接数据库时,根据业务本身的执行时间评估,通过 SET MAX_EXECUTION_TIME 命令控制每个语句的最长执行时间,避免单个语句执行时间过长

从数据库看:

  • 监控 information_schema 的 innodb_trx 表
  • pt-kill
  • 测试阶段输出 general log,分析日志提前发现问题
  • innodb_undo_tablespaces 参数设置成 2 或者更大

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
MySQL
MySQL实战45讲
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant