MySQL锁机制

#MySQL锁机制| 来源: 网络整理| 查看: 265

文章目录MySQL锁机制一、锁的粒度二、锁的类型三、InnoDB中的锁1、串行化怎么解决幻读2、意向共享锁和意向排他锁四、死锁五、锁的优化建议六、MVCC多版本并发控制MySQL锁机制一、锁的粒度表级锁：对整张表加锁开销小，加锁快，不会出现死锁锁粒度大，发生锁冲突的概率高，并发度低行级锁：对表中某行记录加锁开销大，加锁慢，会出现死锁锁定粒度最小，发生锁冲突的概率最低，并发度高二、锁的类型排它锁（Exclusive），又称为 X 锁，写锁，写锁和其他锁是不兼容的共享锁（Shared），又称为 S 锁，读锁，读锁之间是共享记录的 X和S锁之间有以下的关系：

SS可以兼容的，XS、SX、XX之间是互斥的，即读锁之间可以共享，读写和写写之间是不兼容的

加锁效果表现：一个事务对数据对象 O 加了 S 锁，只能对 O 进行读取操作，加锁期间其它事务能对O 加 S 锁但不能加 X 锁一个事务对数据对象 O 加了 X 锁，可以对 O 进行读取和更新，加锁期间其它事务不能对 O 加任何锁命令显示加锁：select ... lock in share mode#强制获取共享锁 select ... for update#获取排它锁三、InnoDB中的锁

InnoDB的一个特点就是行锁，除了行锁，InnoDB还有表锁和间隔锁

InnoDB通过锁和MVCC(多版本并发控制)实现了事物的隔离性，通过锁解决幻读，通过MVCC实现提交读和可重复读

1、串行化怎么解决幻读范围条件检索时：使用非索引项检索，InnoDB使用表锁：

非索引项没有建立索引树，使用行锁的话需要遍历，效率太慢，所以InnoDB直接用表共享锁，将整个表锁住，如果想进行数据插入删除，需要获取排它锁，这是不能兼容的

使用索引项检索，InnoDB会使用行锁+间隙锁：

使用二级索引，通过索引找到主键索引加上行锁，在对应条件的二级索引之间加上间隙锁，防止数据的插入

等值条件检索时：使用非索引项检索，InnoDB同样使用表锁使用索引项检索，InnoDB使用行锁：

通过索引记录加上行锁，加上行锁就行，主键和唯一索引的值无法重复

特性总结：InnoDB行锁是通过给索引上的索引项加锁来实现的，而不是给表的行记录加锁实现的，这就意味着只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁，并且当使用的是二级索引时，同样的根据二级索引找到主键索引上的行记录做加锁用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，做“间隙”加锁，以防止幻读

注意：即使SQL中使用了索引，但是经过MySQL的优化器后，如果认为全表扫描比使用索引效率更高，此时会放弃使用索引，因此也不会使用行锁，而是使用表锁，比如对一些很小的表，MySQL就不会去使用索引

2、意向共享锁和意向排他锁

在绝大部分情况下都应该使用行锁，因为事务和行锁往往是选择InnoDB的理由

但个别情况下也使用表级锁 :事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间等待和锁冲突事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚意向锁的本质：

用来加速获取表的表锁的，意向锁是表级锁，协调表锁和行锁的共存关系

意向共享锁（IS锁）：如果表中某个行共享锁被获取了，那么意向共享锁的状态是被获取的意向排他锁（IX锁）：如果表中某个行排他锁被获取了，那么意向共享锁的状态是被获取的如果要获取表共享锁，那么要检查表的表排他锁和意向排他锁如果要获取表排他锁，那么要检查表的表排他锁，表共享锁，意向排他锁和意向共享锁四、死锁

MyISAM 表锁是 deadlock free 的，这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁

但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，即锁的粒度比较小，这就决定了在 InnoDB 中发生死锁是可能的

死锁问题一般都是我们自己的应用造成的，和多线程编程的死锁情况相似，大部分都是由于我们多个线程在获取多个锁资源的时候，获取的顺序不同而导致的死锁问题

因此我们应用在对数据库的多个表做更新的时候，不同的代码段，应对这些表按相同的顺序进行更新操作，以防止锁冲突导致死锁问题

五、锁的优化建议

1.尽量使用较低的隔离级别

2.设计合理的索引并尽量使用索引访问数据，使加锁更加准确，减少锁冲突的机会提高并发能力

3.选择合理的事务大小，小事务发生锁冲突的概率小

4.不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行，这样可以大大减少死锁的机会

5.尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响

6.不要申请超过实际需要的锁级别

7.除非必须，查询时不要显示加锁

六、MVCC多版本并发控制

MVCC是多版本并发控制（Multi-Version Concurrency Control，简称MVCC），是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现已提交读和可重复读隔离级别，也经常称为多版本数据库

MVCC机制会生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本（系统版本号和事务版本号）

MVCC的本质是快照，生成多版本，其底层所依赖的机制是 undo log 回滚日志，保存了事务发生之前的数据的一个版本，用于事务执行时的回滚操作，同时也是实现多版本并发控制（MVCC）下读操作的关键技术

DB_TRX_ID:对记录做修改的事务ID DB_ROLL_PTR:回滚指针，储存旧版本记录数据的地址

实现已提交读：

可以解决脏读，但是无法解决不可重复读，MVCC对于每一次select都会生成新的快照，而快照中的数据都是经过事物正确commit后的数据

实现可重复读：

解决脏读，不可重复读，MVCC只会在第一次select才会生成当前事务全局性的快照，快照中的数据都是经过事物正确commit后的数据，后续select会沿用第一次的当前读快照

在这种情况下，可重读度在一定程度上解决了幻读，但是如果事物自己update其他事物提交insert数据，是会显示在快照中的

MVCC多版本并发控制中，读操作可以分为两类：

1、快照读（snapshot read）

读的是记录的可见版本，不用加锁，如select

mysql> select * from test_dead_lock where id=1 for update;

2、当前读（current read）

读取的是记录的最新版本，并且当前读返回的记录

如insert，delete，update，select…lock in share mode/for update

快照内容读取原则：版本未提交无法读取生成快照版本已提交，但是在快照创建后提交的，无法读取版本已提交，但是在快照创建前提交的，可以读取当前事务内自己的更新，可以读到

t，delete，update，select…lock in share mode/for update

【本文地址】

公司简介

联系我们