本文是mysql实战专栏第三篇
事务隔离,为什么你改了我还看不见?
- 事务是要保证一组数据库操作,要么全部成功,要么全部失败,在mysql中,事务支持是在存储引擎层实现
隔离性与隔离级别
隔离性
- Atomicity:原子性
- Consistency:一致性
- Isolation:隔离性
- Durability:持久性
隔离级别
当数据库上有多个事务同时执行的时候,就会出现以下问题,为解决以下问题,就有了隔离级别的概念
dirty read: 脏读
non-repeatable read: 不可重复读
phantom read:幻读
隔离级别:
- read uncommitted:读未提交,一个事务还没提交时,它做的变更就能被别的事务看到;别人改数据的事务尚未提交,我在我的事务中也能读到
- read committed:读提交,一个事务提交之后,它做的变更才会被其他事务看到;别人改数据的事务已经提交,我在我的事务中才能读到
- repeatable read:可重复读,一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的,当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的,mysql默认是此隔离级别;别人改数据的事务已经提交,我在我的事务中也不去读
- serializable: 串行化,对于同一行记录,写会加写锁,读会加读锁,当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行,我的事务尚未提交,别人休想改数据
- 以上四种隔离级别,性能依次降低,安全性依次提高
- 查看数据库目前隔离级别:
mysql> show variables like 'transaction_isolation'; 5.7版本 mysql> show variables like 'tx_isolation'; 5.6或更早版本
- 设置隔离级别,就将启动参数transaction_isolation设置为要设置的隔离级别
- 在mysql中,每条记录在更新时都会同时记录一条回滚记录,记录上的最新值,通过回滚操作,都可以得到前一个状态的值,同一条记录在系统中可以存在多个版本,这就是数据库的多版本并发控制MVCC,系统会判断,当没有事务再需要用到回滚日志时,回滚日志会被删除
- 5.5版本及之前版本,回滚日志和数据字典一起放在ibdata文件里,即使长事务最终提交,回滚段被清理,文件也不会变小
事务启动方式
mysql事务启动方式有以下几种:
显式启动,begin或start transaction,提交语句是commit,回滚使用rollback
set autocommit=0,关闭自动提交,执行select语句,事务就启动,并不会自动提交,当执行commit或rollback,或断开连接,事务才结束,有些客户端连接成功会默认执行set autocommit=1,如果是长连接,就会导致意外的长事务,autocommit=1情况下,用begin显式启动事务,执行commit则提交事务,如果执行commit work and chain,则是提交事务并自动启动下一个事务,这样省去了在此执行begin的开销
查询超过60s的长事务
mysql> mysql> SELECT * from information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(),trx_started))>60;
- 如何应对数据库长事务问题?
此问题要从应用开发端和数据库端来看
- 应用开发端:
- 确认是否设置autocommit=0,此确认工作可以在测试环境中开展,把mysql的general_log开启,然后跑一个业务逻辑,通过general_log日志来确认,目标是设置autocommit=1
- 确认是否有不必要的只读事务,有些会把好几个select语句放入事务中,这种只读事务可以去掉
- 业务连接数据库时,根据业务本身的预估,通过set max_execution_time命令,来控制每个语句执行的最长时间,避免单个语句意外执行长时间
- 数据库端:
- 监控 information_schema.innodb_trx表,设置长事务阈值,超过就报警或者kill
- 使用pt-kill工具
- 在业务功能测试阶段要输出所有general_log,分析日志,提前发现问题
- 如使用mysql5.6或更高版本,把innodb_undo_tablespaces设置为2,或更大值,如果真出现大事务导致回滚段过大,这样设置后清理起来更方便
- 应用开发端: