MySQL数据库：锁机制

当数据库中多个事务并发存取同一数据的时候，若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。MySQL锁机制的基本工作原理就是，事务在修改数据库之前，需要先获得相应的锁，获得锁的事务才可以修改数据；在该事务操作期间，这部分的数据是锁定，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

按照不同的分类方式，锁的种类可以分为以下几种：

（1）按锁的粒度划分：表级锁、行级锁、页级锁； 

（2）按锁的类型划分：共享锁（S锁）、排他锁（X锁）；

（3）按锁的使用策略划分：乐观锁、悲观锁；

一、Mysql中的表级锁、行级锁、页级锁：

（1）表级锁：最大粒度的锁级别，发生锁冲突的概率最高，并发度最低，但开销小，加锁快，不会出现死锁；

（2）行级锁：最小粒度的所级别，发生锁冲突的概率最小，并发度最高，但开销大，加锁慢，会发生死锁，行锁是作用在索引的；

（3）页级锁：锁粒度界于表级锁和行级锁之间，对表级锁和行级锁的折中，并发度一般。开销和加锁时间也界于表锁和行锁之间，会出现死锁；

不同的存储引擎支持不同的锁机制：

（1）InnoDB存储引擎支持行级锁和表级锁，默认情况下使用行级锁，但只有通过索引进行查询数据，才使用行级锁，否就使用表级锁。

（2）MyISAM和MEMORY存储引擎采用的是表级锁；

（3）BDB存储引擎使用的是页面锁，但也支持表级锁；

二、InnoDB的锁机制：

1、InnoDb行锁的类型：

InnoDB实现了以下两种类型的行锁：

• 共享锁（S锁、读锁）：多个事务可以对同一数据行共享一把S锁，但只能进行读不能修改；
• 排它锁（X锁、写锁）：一个事务获取排它锁之后，可以对锁定范围内的数据行执行写操作，在锁定期间，其他事务不能再获取这部分数据行的锁（共享锁、排它锁），只允许获取到排它锁的事务进行更新数据。

对于update，delete，insert 操作，InnoDB会自动给涉及的数据行加排他锁；对于普通SELECT语句，InnoDB不会加任何锁。

2、InnoDB的表锁：意向锁

而且因为InnoDB引擎允许行锁和表锁共存，实现多粒度的锁机制，但是表锁和行锁虽然锁定范围不同，但是会相互冲突。当你要加表锁时，势必要先遍历该表的所有记录，判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突。

意向锁也是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁）。当事务要在记录上加上行锁时，则先在表上加上对应的意向锁。之后事务如果想进行锁表，只要先判断是否有意向锁存在，存在时则可快速返回该表不能启用表锁，否则就需要等待，提高效率。

意向锁是InnoDB自动加的，不需要用户干预。

其中，四种锁的兼容性如下：

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者锁不兼容，该事务就要等待锁释放。

3、InnoDB的加锁方法：

上面说过，对于普通的select语句，InnoDB不会加任何锁，但是事务可以通过以下语句显示给记录集添加共享锁或排他锁：

（1）select …… for update：排它锁

select * from table for update 语句：目的是在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），并且查到后的数据只允许自己来修改。

使用场景：为了让确保自己查找到的数据一定是最新数据，并且查找到后的数据值允许自己来修改，此时就需要用到select for update语句；

性能分析：select for update语句相当于一个update语句。在业务繁忙的情况下，如果事务没有及时地commit或者rollback可能会造成事务长时间的等待，从而影响数据库的并发使用效率。

（2）select …… lock in share mode：共享锁：

select * from table lock in share mode 语句：给查找的数据加一个共享锁（S 锁）的功能，允许其他的事务也对该数据上 S锁，但是不能够允许对该数据进行修改。

使用场景：为了确保自己查询的数据不会被其他事务正在修改，也就是确保自己查询到的数据是最新的数据，并且不允许其他事务来修改数据。与select for update不同的是，本事务在查找完之后不一定能去更新数据，因为有可能其他事务也对同数据集使用了 in share mode 的方式加上了S锁；

性能分析：select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

4、InnoDB行锁的实现与临键锁：

InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引检索数据，才能使用行锁，否则将使用表锁。

在InnoDB中，为了解决幻读的现象，引入了临键锁(next-key)。根据索引，划分为一个个左开右闭的区间。当进行范围查询的时候，若命中索引且能够检索到数据，则锁住记录所在的区间和它的下一个区间。

其实，临键锁(Next-Key) = 记录锁(Record Locks) + 间隙锁(Gap Locks)

• 间隙锁：当使用范围查询而不是精准查询进行检索数据，并请求共享或排它锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙(GAP)。
• 记录锁：当使用唯一索引，且记录存在的精准查询时，使用记录锁

具体的使用体现在哪里呢？如下图所示：

（1）范围查询，记录存在，使用临键锁：

（2）当记录不存在时（不论是等值查询，还是范围查询），临建锁将退化成间隙锁：

（3）当条件是精准匹配（即为等值查询时）且记录存在时，并且是唯一索引，临键锁退化成记录锁：

（4）当条件是精准匹配（即为等值查询时）且记录存在，但不是唯一索引时，使用临键锁，有精准值的数据会增加记录锁 和 精准值前后的区间的数据会增加间隙锁

3、利用锁机制解决并发问题：

通过对InnoDB不同锁类型的特性分析，可以利用锁解决脏读、不可重复读、幻读：

• X锁解决脏读

• S锁解决不可重复读

• 临键锁解决幻读

4、分析数据库中行锁情况的命令：

mysql> show status like ‘innodb_row_lock%’;

+——————————-+——-+

| Variable_name | Value |

| Innodb_row_lock_current_waits | 0 |

| Innodb_row_lock_time | 76100 |

| Innodb_row_lock_time_avg | 10871 |

| Innodb_row_lock_time_max | 20552 |

| Innodb_row_lock_waits | 7 |

+——————————-+——-+

Innodb_row_lock_current_waits：当前等待锁的数量

Innodb_row_lock_time：系统启动到现在锁定的总时间长度(重要)

Innodb_row_lock_time_avg：每次等待所花平均时间(重要)

Innodb_row_lock_time_max：系统启动到现在 等待最长的一次所花的时间

Innodb_row_lock_waits：系统启动到现在 总共等待的次数(重要)

当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划

三、MyISAM的锁机制：

MyISAM存储引擎使用表级锁，表级锁两种模式：表共享读锁、表独占写锁。MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改查操作前，会自动给涉及的表加写锁。读锁会阻塞写锁，但不会阻塞读锁，而写锁则会把写锁和读锁都阻塞。

1、MyISAM的锁调度:

MyISAM默认情况下，认为写请求一般比读请求要重要，如果有读写请求同时进行的话，MyISAM将会优先执行写操作，即使读请求比写请求先到达锁等待队列，写锁请求也会插到读锁请求之前！这也是MyISAM不适合做写为主的引擎的原因。这样MyISAM表在进行大量的更新操作时，会造成查询操作很难获得读锁，从而导致查询阻塞。

我们可以通过一些设置来调节MyISAM的调度行为：

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

上面3种方法都是要么更新优先，要么查询优先的方法。这里要说明的就是，不要盲目的给mysql设置为读优先，因为一些需要长时间运行的查询操作，也会使写进程“饿死”。只有根据你的实际情况，来决定设置哪种操作优先。但这些方法还是没有从根本上同时解决查询和更新的问题。

在一个有大数据量高并发的mysql里，我们还可采用另一种策略来进行优化，那就是通过mysql读写分离来实现负载均衡，这样可避免优先哪一种操作从而可能导致另一种操作的堵塞。

2、并发插入：

一般情况下，当数据库表有一个读锁时，其它进程无法对此表进行更新操作，但在一定条件下，MyISAM表也支持查询和插入操作的并发进行。MyISAM存储引擎有一个系统变量concurrent_insert，可以用来控制其并发插入的行为，其值分别可以为0、1或2。

当concurrent_insert设置为0时，不允许并发插入。

当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MyISAM的默认设置。

当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

3、表级锁情况分析命令：

【查看哪些表被加锁了】mysql > show open tables;

【查询表级锁争用情况分析】mysql> show status like ‘tables%’;

mysql> show open tables;

+———-+——-+——–+————-+

| DATABASE | TABLE | In_use | Name_locked |

| dbtest | book | 1 | 0 |

| dbtest | mylock| 1 | 0 |

+———-+——-+——–+————-+

参数说明：

（1）Database：含有该表的数据库。

（2）Table：表名称。

（3）In_use：表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。

（4）Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

mysql> show status like ‘tables%’;

+————————+——-+

| Variable_name | TABLE |

| Table_locks_immediate | 105 |

| Table_locks_waited | 1 |

+————————+——-+

这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：

（1）Table_locks_immediate: 产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1

（2）Table_locks_waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁定争用情况。