数据库锁与隔离等级（MySQL）

目录

 

一、数据库引擎

二、事务的四大特性

三、InnoDB的锁

四、事务并发引起的问题

五、隔离级别

---

一、数据库引擎

主流的引擎主要有InnoDB和MyISAM两种，在MySQL中都支持，他们的主要区别为：

1. InnoDB支持事务，而MyIASM不支持；
2. InnoDB支持外键，而MyIASM不支持；
3. InnoDB支持表锁，行锁，而MyIASM只支持表锁；（InnoDB的行锁是在索引上实现的，日过访问没有命中索引，就无法使用行锁，并退化为表锁）
4. InnoDB表中必须有唯一索引，而MyIASM可以没有；
5. InnoDB不保存表的具体行数，会全表扫描，而MyISAM使用一个变量保存整表的行数；
6. InnoDB不支持全文索引，MyISAM支持全文索引；
7. InnoDB和MyISAM存储的文件不同：
   • InnoDB：frm表的定义文件，ibd数据文件
   • MyISAM:frm表的定义文件，myd数据文件，myi索引文件

8. InnoDB支持事务，一般用于多并发的场景，适合大量的UPDATE，MyISAM查询效率高，一般用于大量的SELECT，不适用高并发。

二、事务的四大特性

1. 原子性（Atomicity）
2. 一致性（Consistency）
3. 隔离性（Isolation）
4. 持久性（Durability）

三、InnoDB的锁

• 共享锁：S锁，读锁（可用来防止脏读）其他事务可以继续加共享锁，但是不能继续加排他锁；
• 排他锁：X锁，写锁（可用来防止不可重复读）加锁后其他事务不可以在加锁；
• 意向锁：包括意向读锁和意向写锁，是一种表级锁，主要用于提高数据库加锁的效率；
• 自增所：是通过表锁机制来完成的（AUTO-INC LOCKING），锁不是在每次事务完成后释放，而是在完成对自增长值插入的SQL语句后释放，若大量的并发插入，自增所不会引起SQL堵塞；
• 临键锁：next-key锁，主要用于范围（>,<）查询时，在范围之外的的相邻两个节点（根据B+树，区间时左闭又开）；
• 间隙锁：gap锁（可用防止幻读），临建锁的范围查询没有记录时，退化为间隙锁；
• 记录锁：Record-Lock，锁的记录时索引记录，若非主键索引，会在自己索引上加锁，再去主键上加锁，若该列没有索引，则退化为表锁；

四、事务并发引起的问题

1. 脏读：
   1. 指一个事务读取到其他事务执行过程中未提交的数据。
   2. 
2. 不可重复读：
   1. 指一个事务中多次查询某条数据的结果不同，该数据被其他事务修改；‘
   2. 与脏读的不同在于不可重复读是读取另一个个事务已提交的的结果。
   3. 
3. 幻读：
   1. 指一个事务读取到另一个事务已提交了insert的数据；
   2. 与不可重复读一样是读取其他事务已提交的数据，不同的是幻读强调不同的数据项。
   3. 

五、隔离级别

READ UNCOMMITTED 读未提交 

级别最低，可以读到其他事务未提交的数据，会出现脏读。

READ COMMITTED 读已提交

大多数数据库的默认隔离级别（MySQL除外），只能读取到其他事务已提交的数据，支持不可重复读

REPEATABLE READ 可重复读

MySQL默认的隔离级别，保证并发时看到同样的数据，但是会产生幻读的现象，InnoDB通过多版本并发机制解决幻读问题

SERIALIZABLE 串行化

隔离级别最高，原理时是在每个读数据上加锁，回到是大量的操作超时和锁竞争现象。

隔离级别	脏读	不可重复读	幻读
读未提交（Read uncommitted）	可能	可能	可能
读有提交（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
串行化（Serializable）	不可能	不可能	不可能

查看数据库隔离等级：select @@tx_isolation;

修改数据库隔离等级：set session transaction isolation level read committed;

一般数据库是自动提交事务，查看通过：show variables like 'autocommit';