Mysql锁机制（学习笔记）

读锁（共享锁）

针对同一份数据，多个读操作可以同时进行而不会互相影响。

• 表锁（偏读）
  偏向MyISAM存储引擎，开销小，加锁快：无死锁；锁定力度大，发生锁冲突的概率最高，并发度最低
• 手动增加表锁

lock table 表名字 read/(write), 表名字read/(wirite), 其他;

• 查看表结构

show open tables

• 解锁

unlock tables

写锁（排他锁）

当前写操作没有完成，它会阻断其他写锁和读锁
-行锁（偏写）
偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度也最高。

• 索引失效会导致行锁变表锁
• 间隙锁的危害

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁﹔对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，
InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁）。

• 如何锁定一行

select * from 表名 where 字段名=xx for update;

• 优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范围
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离

扩展

MySQL的锁模式

• 共享锁或读锁(S锁)：共享的，相互不阻塞的

• 排他锁或写锁(X锁)：会阻塞其他的写锁和读锁

• 读意向锁(IS锁)：意向锁也是表级锁，它只会阻塞表级读锁或表级写锁，不会和自增锁冲突，也不会和行锁冲突

• 写意向锁(IX锁)：意向锁也是表级锁，它只会阻塞表级读锁或表级写锁，不会和自增锁冲突，也不会和行锁冲突

• 自增锁(AI锁)：是一种表锁，当表中有自增（AUTOINCREMENT）时出现。

兼容性

锁的粒度和锁的策略

MySQL有三种锁的级别：页级、表级、行级。

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

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

行锁类型

• 记录锁（LOCK_REC_NOT_GAP）:锁住一条记录。
• 间隙锁（LOCK_GAP）: 间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。
• Next-key 锁（LOCK_ORNIDARY）: 记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。
• 插入意向锁（LOCK_INSERT_INTENTION）: 插入意向锁是一种特殊的间隙锁（简写成 II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。

兼容性

隔离级别对加锁的影响

• 读未提交(Read Uncommitted)：可以读到未提交的记录，不能防止脏读、幻读、不可重复读
• 读已提交(Read committed)：可以防止脏读，不能防止幻读和不可重复读，加记录锁即Record锁
• 可重复读(Repeatable Read)：可以防止脏读、幻读，不能防止不可重复读，加记录锁和间隙锁即Gap锁
• 序列化(Serializable)：可以防止脏读、幻读、不可重复读，但并发效率急剧下降

不同SQL语句对加锁的影响

• SELECT…语句正常情况下为快照读，不加锁；
• SELECT…LOCK IN SHARE MODE 语句为当前读，加S锁；
• SELECT…FOR UPDATE语句为当前读，加X锁；
• 常见的DML(INSERT、DELETE、UPDATE)为当前读，加X锁；
• 常见的DDL语句(ALTER、CREATE等)加表级锁，且这些语句为隐式提交，不能回滚。

当前数据对加锁的影响

SQL语句执行时数据库中的数据也会对加锁产生影响。

比如一条最简单的根据主键进行更新的SQL语句，如果主键存在，则只需要对其加记录锁，如果不存在，则需要加间隙锁。