MyISAM锁和InnoDB锁学习笔记

MYSQL锁机制--MysISAM的锁和InnoDB的锁

MyISAM锁

首先MyISAM锁是表锁，分为：

• 表共享读锁
• 表独占写锁

获取锁：

• 获取写锁:

    lock table table_name write;
  

当获取写锁后，其他sessio无法执行对当前表的读、写操作，当前session可以对当前表进行读、写操作。

• 获取读锁

    local table table_name read local;
  

获取读锁后，其他session可以读当前表，不能写，当前session也只能对当前表进行读操作，不能写。
并且当前session也不能对其他表进行读写操作。
上述获取读锁 加了一个'local'，指允许MyISAM进行并发插入。
read local能阻止当前session的delete、update、insert操作，也能阻止其他session对当前表的update、delete操作，但是却允许其他session的插入操作。

• 读操作会阻塞对同一表的请求
• 写操作会阻塞对同一表的读、写请求，所有读写，写写操作是串行的。

注意：
MyISAM在执行查询语句之前会自动提交给所涉表加读共享锁，执行更新操作之前会自动给所涉表加写独占锁，这个过程并不需要用户干涉。

InnoDB锁

1. 事务及其ACID属性

   InnoDB是支持事务的，事务是SQL语句组成的逻辑单元。
   事务具有四种属性：原子性（undo log实现），隔离性(锁机制实现)，持久性(redo log实现),一致性（以上三种特性共同实现）。

2. 并发事务带来的利弊

相对于串行处理来说，并发事务能大大增加数据库资源的利用率，提高数据库吞吐量，从而支持更多用户的并发操作。但同时也带来了以下问题：

• 脏读： 事务A读取了事务B的数据，事务B执行rollback，此时A读到的数据是脏数据
• 不可重复度：事务A在多次读取同一数据，再此过程中，事务B更改了事务A读取的数据，并commit，那么A多次读取的统一数据不一致。
• 幻读：有两个管理员，管理员A要将学生的成绩信息从百分之改为ABCDE等级制，在改完将要提交的时候，管理员B插入了一条百分之成绩，然后管理员A提交，此时管理员A会发现还有一条数据没改过来(管理员A:我是做梦了吗)，就像产生幻觉一样。

3. InnoDB锁模式

• 共享锁(S)：即读锁，允许一个事务读一行，阻止其他事务获取相同结果集的排它锁。若事务T对数据对象A加了S锁,则事务T可以读A但不能修改A。其他事物只能对A加S锁，不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能修改A。
• 排他锁(X)：即写锁，允许获取数据对象的排它锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排它锁。若事务T对数据对象A加了排它锁，则允许当前事务读A,更新A。但其他事务在A的排它锁释放之前不能对A加其他锁。

InnoDB默认在delete、uppdate、insert语句前对所涉及数据都加了排他锁。
排他锁语法：

select ... for update;

共享锁语法：

select ... lock in share mode;

select语句默认不添加任何锁，即使加了锁，也可以用select ... from 查询数据，因为普通查询没有任何锁机制。

• InnoDB行锁实现方式：
  InnoDB行锁是通过给索引的索引项加锁来实现的，这种行锁意味着只有当通过索引来检索数据时，InnoDB才会使用行级锁，否则InnoDB使用表级锁。

总结

• MyISAM

  1. 共享读锁之间是相互兼容的，共享读锁和独占写锁是互相排斥的。
  2. 一定条件下，MyISAM允许查询和并发插入，可以利用这一点解决应用中对同一表争用所带来的问题。
  3. MyISAM默认的锁调度机制是写优先，并不一定适合所有应用场景，用户可以通过设置low_priority，或在delete、update、insert语句前指定low_priority选项来调节读写锁的争用。
  4. 表锁的粒度大，读写之间是串行的，因此若更新操作较多，MyISAM会出现严重的锁等待问题，可以考虑InnoDB锁。

• InnoDB

  1. InnoDB行锁是基于索引实现的，如果不使用索引操作数据，InnoDB使用的是行锁。
  2. 在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。
  3. 再了解锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：
     • 尽量使用较低的隔离级别：精心设计索引，并尽量使用索引访问数据，使锁更加准确，从而减少锁冲突的机会；
     • 选择合理的事务大小，小事务发生锁冲突的几率也很小；
     • 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排它锁，这样容易产生死锁；
     • 不同的程序访问同一组表时，应尽量以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会。
     • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响；不要申请超过实际需要的锁级别，除非必须，查询时不要显示加锁。
     • 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能；