Mysql锁机制

• MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定：

  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。如：MyISAM和MEMORY存储引擎；
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。如：BDB存储引擎；
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。如：InnoDB存储引擎。

• MySQL中两种使用最为频繁的存储引擎MyISAM和Innodb各自的锁定机制：

  • MyISAM表级锁：

    加写锁:lock tables table_name read;   // 其他事务只能读，不能加写锁，要等待更新。加读锁:lock tables table_name write;   // 其他事务不能读

  • Innodb行级锁（行锁是对索引加锁）：

    当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说Innodb的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：

    锁 共享锁（S） 排他锁（X） 意向共享锁（IS） 意向排他锁（IX） 共享锁（S） 兼容 冲突 兼容 冲突 排他锁（X） 冲突 冲突 冲突 冲突 意向共享锁（IS） 兼容 冲突 兼容 兼容 意向排他锁（IX） 冲突 冲突 兼容 兼容

    
    

    • 共享锁（S）：又称读锁（Share lock,简记为S锁），其他用户可以并发读取数据，但任何事务都不能获取数据上的排他锁，直到已释放所有共享锁。
    • 排他锁（X）：又称写锁（(eXclusive lock,简记为X锁)），若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。

    Innodb的锁定则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。Innodb的这种锁定实现方式被称为“NEXT-KEYlocking”（间隙锁），因为Query执行过程中通过过范围查找的华，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。而Innodb给出的解释是为了组织幻读的出现，所以他们选择的间隙锁来实现锁定。
    除了间隙锁给Innodb带来性能的负面影响之外，通过索引实现锁定的方式还存在其他几个较大的性能隐患：

    1. 当Query无法利用索引的时候，Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；

    2. 当Query使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所只想的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；

    3. 当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定。

• 行锁是对索引加锁（egg）：

  • 例子1：

    事务１：set autocommit = off;       update test set num = 200 where id = 5; 事务２：select * from test where id=5;   // 可读

  • 例子2：

    事务１：set autocommit = off;       update test set num = 200 where id = 5;事务２：set autocommit = off;       update test set num = 200 where id = 5;   // 事务２等待事务１提交了才能更新。

  • 例子3：

    事务１：set autocommit = off;       update test set num = 200 where id = 5; 事务２：set autocommit = off;       update test set num = 200 where id = 6;   // 事务２不用等待

  • 例子4：

    事务１：set autocommit = off;       update test set num = 200 where id = 5; 事务２：insert test (id) value (5);   // 事务２等待，事务１提交后，事务２报错Duplicate entry '5' for key 'PRIMARY'

  • 例子5：

    事务１：set autocommit = off;       update test set num = 200 where id = 5; 事务２：insert test (id) value (６);   // 事务２不用等待；

  • 例子6：

    事务１：insert test (id) value (８); 事务２：insert test (id) value (９);   // 事务２不用等待；