13.MySQL锁机制

锁的分类

　　从对数据的类型 （读\写）分：

　　　　1.读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响

　　　　2.写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

　　从对数据操作的粒度分：

　　　　1.表锁

　　　　2.行锁

 

 

表锁（偏读）

　　1.偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁的粒度大，发生锁冲突的概率最高，并发度最低

　　2.MyISAM 在执行查询（SELECT）语句前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE/DELETE/INSERT等）前，

　　会自动给涉及的表加写锁，这个过程并不需要用户参与，我们试验中使用 lock table 是为了模拟并发环境

 

　　加读锁　　　

　　　　1.会话一中A表加读锁（lock table tablename read），会话二不锁任何表

　　　　2.会话一中 可以查询A表，也只能查询A表，对A表的写操作会报错，对其他的表的读写操作也报错

　　　　3.会话二中，可以读A表，也可以对其他表进行读写，但只对A表的写操作会阻塞，必须等会话一释放 A表的读锁（unlock tables），会话二才能完成写操作

　　加写锁　

　　　　1.连接一，给一张表加 写锁，这个连接可以对这张表进行 读和写，但是不能访问其他表

　　　　2.连接二 可以访问其他未加锁的表，但是对加了写锁的表 的读 和 写 会阻塞（即排他），等待连接一中锁的释放

简而言之，就是读锁会阻塞写，但是不会阻塞读，而写锁则会把 读和写 都阻塞（对于其他连接而言）

表锁分析    show status like ‘table%’

 

行锁（偏写）（行锁出现的前提是在事务中）

　　1.偏向 Innodb 存储引擎，开销大，加锁慢；会出现死锁；锁的粒度最小，发生锁冲突的概率最低，并发度也最高

　　2.Innodb 和 Myisam最大不同的有两点：一是只是事务（TRANSACTION）；二是采用了行级锁

　　

　　3.由于行锁支持事务，我们复习一下关于事务的知识

　　　　1.事务及其ACID属性

　　　　2.并发事务 带来的问题

　　　　　　1.更新丢失（Lost  Update）

　　　　　　2.脏读（Dirty Reads）

　　　　　　3.不可重复读（Non-Repeatable Reads）

　　　　　　在事务A的执行过程中，事务B进行了开启，修改和提交，导致事务A在事务B执行前读取的数据和事务B执行后读取的数据不一致

　　　　　　4.幻读（Phantom  Reads）

　　　　　　和 不可重复读 类似，不过不可重复读针对的列的修改，而幻读针对的是列的增删

　　　　3.事务隔离级别

　　

　　4.行锁定基本演示：事务中对数据的更新才会触发行锁，

　　　　如：连接一开启了事务，在事务中，对表的某一行数据进行更新（此时就把这一行锁定了）（查询不会锁定），

　　　　在该事务提交之前， 连接二发出了update语句，想对被锁的这一行数据进行更新，此时 连接二的语句就会发生阻塞，

　　　　需要到等待连接一中 事提交，行锁释放，才能继续执行

　　　　但是因为是行锁，所以连接二对其他行的数据更新是不会受影响的

 

　　5.无索引行锁升级为表锁（索引失效）

　　　　当出现行锁时，如果行锁锁定的这一行上面 有字段（如name）建立了索引，如果这个索引失效了（比如字符型不加‘’ where name = 1234），

　　　　那么行锁 会自动升级为表锁，其他行数据的 更新 也会被锁定，其他连接无法对这个表进行更新

　　6.间隙锁（宁可错杀，不可错放）

　　　　当我们使用范围条件而不是相等相等条件检索数据时，它会锁定整个范围内所有的索引键值，即使这个键值并不存在

　　　　如：有这样一段数据，id = 1，id = 3，id = 4...中间少了一个 id = 2，如果此时连接一 开启一个事务，更新数据当 1<id <4,

　　　　此时Mysql本着宁可错杀，不可错放的原则，即使id=2 不存在也会被无情的锁定，

　　　　连接二想要插入 一条id=2的数据是不行的，会阻塞，等待连接一事务提交，行锁释放

　　7.如何锁定一行

　　　　for update可以锁定某一行，也可以锁定整张表

　　　　主要是看 select语句后面是否有where，where限定的范围 即锁的范围， select xxx... for update

　　　　如果没有 where，则锁定整张表，锁定之后，其他连接 就无法对锁定的范围进行 更新操作

　　8.行锁分析：show status like 'innodb_row_lock%'

 

为什么说行锁偏写，表锁偏读，即Myisam偏读，Innodb偏写

　　Myisam 读的性能高

　　Myisam 的读写锁调用 是读优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞

　　Innodb行锁是单比查询比不过，但是innodb支持高并发，支持事务

　　在写多读少的应用中还是Innodb插入性能更稳定，在并发情况下也能基本保证性能

可以根据系统的读写情况 来选择合适的 Mysql存储引擎

 

优化建议

　　1.尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

　　2.合理设计索引，尽量缩小锁的范围

　　3.尽可能较少检索条件，避免间隙锁

　　4.尽量控制事务大小，减少锁定资源量和时间长度

　　5.尽可能低级别事务隔离

 

 

页锁（了解一下即可）

　　1.开销和加锁时间介于表锁和行锁之间

　　2.会出现死锁

　　3.锁定粒度介于表锁和行锁之间，并发度一般