0%

mysql---事务

Posted on In

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的。而MyISAM 引擎就不支持事务.

事物操作

  • 显示启动

    1
    2
    3
    4
    begin / start transaction
    rollback
    commit 
    commit work and chain  // 提交事务后自动启动下一个事务, 省去了再次被begin的开销; 适用于频繁使用事务的业务

  • 配置启动

    1
    set autocommit=0

    注意: 这种方法下, 任何语句都会开启事务, 直到显示commit / rollback

  • 查询系统长事务 

    1
    2
    // 查询时间超过60s的长事务
    select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

  • 查询事务隔离级别

    1
    show variables like 'transaction_isolation';

实现原理 (回滚日志, MVCC)

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值.

在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的 多版本并发控制（MVCC） 。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

当系统里没有比这个回滚日志更早的 read-view 的时候, 即不再有事务需要这些日志, 它就会被删除.
因此, 尽量不要使用长事务, 因为他会导致存在很多老的事务视图, 而在该事务提交之前, 所要用到的回滚日志都需要保留着.

事务实现

  • 读未提交: 直接返回记录上的最新值, 不使用视图
  • 读提交: 执行每条sql语句时创建视图
  • 可重复读: 事务启动时创建视图, 并在整个事务期间都使用这张图
  • 串行化: 使用加锁控制, 不使用视图

如何避免长事务的影响

开发端手段

  • 确认是否使用了set autocommit=0
    因为有些框架会自动设置, 可以通过开启 general_long, 随便跑一个业务逻辑, 通过日志来确认.
  • 确认是否有只读事务
    某些框架会会习惯将所有语句包裹在 begin/commit中, 或者将好几个select语句放到事务中, 打包执行.
  • 通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间

    服务端手段

  • 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill
  • 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

    关于 innodb_undo_tablespaces 参数:
    http://blog.itpub.net/31493717/viewspace-2154774/

锁

全局读锁

让全库处于只读的状态, 所有的数据更新语句, 数据定义语句都会被阻塞;

命令

Flush tables with read lock

全库逻辑备份

全库逻辑备份, 是全局读锁的典型使用场景. 如果不加锁, 有可能会造成多个表逻辑的不一致

  • 逻辑备份 mysqldump
    把表中的数据select出来进行存储, 无关存储引擎等. 但是恢复速度慢.
  • 物理备份 xtrabackup
    基于实际文件的备份

对与Innodb这种支持事务的存储引擎, 可以使用 可重复读 隔离级别来解决表逻辑不一致的问题, 且相比于全局读锁的优点是: 由于MVCC
的支持, 数据库依旧可以更新.
但是对于像MyISAM这种不支持事务的, 只能使用全局读锁的方式.

为什么不用全局配置?

set global readonly=true也可以设置库为只读状态, 但不建议使用.
因为如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

mysqldump阅读资料

https://www.cnblogs.com/yanjieli/p/9807011.html

表锁

表锁

粒度太大, 一般不用; 但是对于不支持行锁的存储引擎, 只能使用表锁. eg.MyISAM

1
2
3
lock tables … read/write // 加锁

unlock tables //释放锁, 客户端断开连接也会释放锁
注意

lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
例如: lock tables t1 read, t2 write;, 则该线程接下来只能对t1进行读操作, 对t2进行读写操作, 而不能访问其他的表.

元数据锁

为了防止DDL和DML并发的冲突

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。且MDL锁要等事务提交才会释放.

  • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
  • 如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。行锁就是针对数据表中行记录的锁, 比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

两阶段锁

需要的时候加锁(update), 事务结束时释放(而不是不需要的时候), 即所有的操作需要的行锁都是在事务提交的时候才释放的
因此, 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。