mysql事务与隔离

MySQL 8.0支持的存储引擎

可以使用SHOW ENGINES语句查看系统支持的引擎类型。

1、InnoDB

• 每个InnoDB表在数据库目录中以.frm格式文件表示
• InnoDB表空间 tablespace 被用来存储表的内容
• 提供一组用来记录事务性活动的日志文件
• 用COMMIT(提交)，SAVEPOINT及ROLLBACK(回滚)支持事物处理
• 提供全ACID兼容
• 在mysql服务器崩溃后提供自动恢复
• 多版本（MVCC）和行级锁定
• 支持外键及引用的完整性，包括级联更新和删除

2、 MyISAM

3、 Memory

4、 Merge

5、 Archive

6、 Federated

7、 CSV

8、 BLACKHOLE

1. 事务的特性（ACID）

特性	说明
原子性(A)	一个事务中的所有操作，要么全部完成，要么全部不完成不会结束在中间某个环节
一致性(C)	在事务开始之前和事务结束以后，数据库的完整性没有被破坏
隔离性(I)	事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见
持久性(D)	事务一旦提交了，其结果就是永久性的，就算发生了宕机等事故数据库也能将数据恢复。

2. 事务的隔离主要是解决并发带来的问题，并发是用来提升数据的处理效率

隔离级别	脏读	不可重复读	幻读	隔离性	并发性
顺序读( SERIALIZABLE)	N	N	N	最高	最低
可重复读(REPEATABLE READ)	N	N	N
读以提交(READ COMMITTED)	N	Y	Y
读未提交(READ UNCOMMITTED)	Y	Y	Y	最低	最高

2.1 脏读：一个事务 读取了 另一个事务未提交的数据

事务1	事务2
start transaction;	-
select score from t_course where course_id=59;/* score = 9.2*/
	start transaction;
	Update t_course set score=9.6 where course_id=59;
select score from t_course where ourse_id=59;/* score = 9.6*/
	rollback;

2.2 不可重复读：一个事务前后两次读取的同一数据不一致

事务1	事务2
start transaction;	-
select course idscore from t_course where course id=56/* 56 9.6*/
	start transaction;
	update t_course set score=9.7 where course_id=56; commit;
select course_id,score from t_coursewhere course_id=56/* 56，9.7*/
commit;

2.3 幻读：指一个事务两次查询的结果集记录数不一致

事务1	事务2
start transaction;	-
select course_id,score from t_course where score > 9.5 and score < 9.8 /* 56,9.6 73,9.7 */
	start transaction;
	update t_course set score=9.7 where course_id=43; commit;
select course_id,score from t_course where score > 9.5 and score < /* 56,9.6 73,9.7 43,9.7*/
commit;

3. 设置事务隔离级别

/* 查看事务隔离级别 */
SHOW VARIABLES LIKE '%iso%'

/* 设置事务隔离级别 */
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE:

4. 查看阻塞的sql

  SELECT
    waiting_pid AS '被阻塞的线程',
    waiting_query AS '被阻塞的SQL' ,
    blocking_pid AS '阻塞线程' ,
    blocking_query AS '阻塞SQL' ,
    wait_age AS '阻塞时间',
    sql_kill_blocking_query AS '建议操作'
  FROM
    sys.innodb_lock_waits
  WHERE (
      UNIX_TIMESTAMP () - UNIX_TIMESTAMP (wait_started)
    ) > 30	/* 查询阻塞大于30s的语句 */

5.发现死锁

1、配置记录锁的配置
set global innodb_print_all_deadlocks=on;

2、通过日志分析死锁的原因