14)mysql事务

• 什么是事务
  事务是数据库执行操作的最小单元
  事务可以有一个sql组成，也可以由多个sql组成
  组成事务的sql要么全执行成功，要么全执行失败

事务的语法

START TRANSACTION / BEGIN
      SELECT ...
      UPDATE ...
      INSERT ...

COMMIT / ROLLBACK

用start transaction 或者 begin 开始一个事务
中间只能是dml语句，增删改查
commit：提交1个事务
rollback：回滚1个事务

---

---

• 为什么要有事务

允许多个人同时操作相同得数据，是为了增强数据库的并发性，并发能带给更大的吞吐量，更优的资源利用率和更好的性能
但并发也会带来问题，比如多个用户同时修改数据时，容易破坏数据的一致性，为了解决并发带来的资源争用和一致性问题，mysql提供了事务功能和基于锁的多版本控制机制

• persist：修改后对当前的session和所有连接到mysql中的新连接都有效，且在mysql重启后也不会丢失

• global：修改后只会对新的连接到mysql的连接有效，且mysql重启后，做的修改会丢失

• session：（对开发人员来说是最常使用的）只会影响到当前的连接，当连接断开后，所做的修改就会失效

将事务设置为SERIALIZABLE的示例

serializable
如上图示，在串行化事务隔离级别中，只有第一个事务提交或回滚后，第二个事务才能执行。
事务2修改后满足事务1的查询条件，只有等待事务1先执行完。
如果事务2修改后不满足事务1的查询条件，则不会被卡住（如操作的是不同表）

其他的不一一测试

锁的类型
共享锁：其他事务只能对他读取和加共享锁，而无法修改
排他锁：只能由加排他锁的事务读取和修改，其他数据无法读取和修改，也无法加锁

加锁就有可能造成阻塞和死锁

• 什么是阻塞
  由于不同锁之间的兼容关系，造成的一事务需要等待另一事务释放所占资源的现象

'如何发现阻塞，该表记录了所有innodb所有等待事件'
SELECT waiting_pid AS 'block pid',  -- 被阻塞的进程，进程ID
    waiting_query AS 'block SQL',  -- 被阻塞的命令
    blocking_pid AS 'running pid',  -- 谁阻塞了后面，在运行的进程，进程ID
    blocking_query AS 'running SQL',  -- 在运行id的sql
    wait_age AS 'blocked time',       -- 阻塞多久了
    sql_kill_blocking_query AS 'info'  -- 如何解决阻塞的建议
FROM sys.innodb_lock_waits
WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait_started))>30  -- 单位秒

• 如何处理阻塞
  1.终止占用资源的事务
  2.优化占用资源的sql，使其尽快释放资源

• 什么是死锁
  死锁：并行执行的多个事务之间占有了对方所需要的资源。
  mysql内部会自动监控死锁，主动回滚某个事务，回滚的原则是回滚占用资源少的事务来解死锁，这过程不需要人为干预
  死锁虽然不会卡住，但有时会对业务造成影响，如某个sql，有时执行成功，有时失败，可能就是死锁造成的

set global innodb_print_all_deadlocks=on;
死锁信息记录在mysql的错误日志中的开关

如有2个mysql事务，事务1要修改 id 为10的title,score，事务2要修改 id 为10的 score,title
事务1执行完修改title，score还没执行，此时title资源被事务1占用
事务2执行完修改score，title还没执行，此时score资源被事务2占用
下次事务1和事务要修改的资源被彼此占用，就会形成死锁

如前面的事务1和事务2，让双方修改数据的顺序相同，比如都是先修改title，再修改score，这样这些事务都是按相同的顺序占有资源，后面的事务不会占有前面事务后续要修改的资源，只会产生阻塞，不会产生死锁，只要前面的事务执行够快，就只会阻塞很短的时间