MySQL学习-第三节-数据库锁机制

一、锁的简单分类

1. 对数据操作类型可分为：

（1）读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而互不影响
（2）写锁（排他锁）：当前锁操作没有完成之前，他会阻断其它的读锁和写锁

2. 对数据操作粒度可分为：

（1）表锁
（2）行锁

二、表锁（偏读）

1.特点

（1）偏向MyISAM存储引擎，开销小，加锁快。
（2）无死锁。
（3）锁定粒度大，发生锁冲突概率最高，并发度最低。

2.操作指令

• show open tables：查看表是否加锁
• lock table 表名 read（write）：添加表读锁（写锁）
• unlock tables：解锁
• show status like ‘table%’：
  Table_Locks_immediate：表锁定次数
  Table_Locks_waited：出现表级锁定争用而发生等待的次数，该值越大，则说明存在较严重的表锁争用情况

3.阻塞案例

• 添加读锁（表共享锁）：
  （1）session1在表table1加读锁，session1只可以读取表table1数据，不可修改表table1数据，不可读取除表table1以外表的数据
  （2）session2可以读取表table1的数据，可以读取除表table1以外表的数据，但是session2向表table1发出的写操作会被阻塞，直到session1释放锁
• 添加写锁（表排他锁）
  （1）session1在表table1加写锁，session1可以读取表table1数据，也可以修改表table1数据，不可读取除表table1以外表的数据
  （2）session2可以读取除表table1以外表的数据，但是session2向表table1发出的所有请求都会被阻塞，直到session1释放锁

三、行锁（偏写）

1.特点

（1）偏向InnoDB存储引擎，开销大，加锁慢。
（2）会出现死锁。
（3）锁定粒度小，发生锁冲突概率最低，并发度最高。

2.操作指令

• show status like ‘innodb_row_lock%’：
  Innodb_row_lock_current_waits：当前正在等待锁定的数量
  Innodb_row_lock_time：从系统启动到现在锁定的时间长度
  Innodb_row_lock_time_avg：每次等待花费的平均时间
  Innodb_row_lock_time_max：从系统启动到现在等待的最长一次所花费的时间
  Innodb_row_lock_waits：从系统启动到现在总共的等待次数

2.索引失效-行锁变表锁

因sql写操作语句中参数的类型不匹配，如varchar类型字段未加单引号，mysql底层类型自动转换时，会导致索引失效，最终使行锁变为表锁

3.间隙锁危害

当在sql的检索条件中使用范围条件时，对该范围内已有数据记录的索引项加锁，并且对于该范围不存在数据的“间隙”也会加锁，导致在插入该范围内不存的数据时，被阻塞。

4.优化建议

（1）尽可能让所有的数据检索都通过索引Index来完成,从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定。
（2）合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他查询的执行。
（3）尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
（4）尽量控制MySQL事务的大小，减小锁定的资源量和锁定的时间长度。
（5）在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。