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，这样这些事务都是按相同的顺序占有资源，后面的事务不会占有前面事务后续要修改的资源，只会产生阻塞，不会产生死锁，只要前面的事务执行够快，就只会阻塞很短的时间
