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