一篇文章弄懂MySQL锁机制

一、锁的分类

1、按锁的粒度划分，可分为表级锁、行级锁、页级锁（mysql） 2、按锁级别划分，可分为共享锁、排他锁 3、按使用方式划分，可分为乐观锁、悲观锁

（一）、按粒度划分的锁

1、表级锁（偏向于读）

• 优缺点

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

• 支持引擎：MyISAM、MEMORY、InNoDB
• 表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

2、行级锁

• 优缺点

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 支持引擎：InnoDB
• 行级锁定分为行共享读锁（共享锁）与行独占写锁（排他锁）

3、页级锁

对于行级锁与表级锁的折中，开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

表锁定与行锁定实际操作数据库：https://blog.csdn.net/why15732625998/article/details/80439315#commentBox

（二）、按锁的级别划分

1、共享锁（读锁）

共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。（只能读不能改） 用法：SELECT … LOCK IN SHARE MODE;前边必须使用begin

2、排他锁（写锁）

一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。 可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。 用法：SELECT … FOR UPDATE;前边必须使用begin

三、MyISAM存储引擎的锁

1、支持表锁（偏向于读）

1、 MyISAM在执行SQL语句时，会自动为SELECT语句加上共享锁，为UDI操作加上排它锁。 2、MyISAM读写、写写之间是串行的，读读之间是并行的 3、由于表锁的锁定粒度大,读写又是串行的,因此如果更新操作较多,ＭyISAM表可能会出现严重的锁等待

2、并发锁

在存储引擎中有一个系统变量concurrent_insert，专门控制其并发插入的行为 concurrent_insert=0时，不允许并发插入 concurrent_insert=1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），其允许在一个进程读表的同事，另一个进程从表插入记录，这也是MySQL的默认设置 concurrent_insert=2时，如果MyISAM表中没有空洞，允许在表尾并发插入记录

2、锁调度

MySQL认为写请求一般比读请求要重要，所以如果有读写请求同时进行的话，MYSQL将会优先执行写操作。这样MyISAM表在进行大量的更新操作时（特别是更新的字段中存在索引的情况下），会造成查询操作很难获得读锁，从而导致查询阻塞。

设置MyISAM调度行为： a、通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。b、通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。 c、通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。 d、系统参数max_write_lock_count设置一个合适的值;当一个表的读锁达到这个值后，MySQL便暂时将写请求的优先级降低，给读进程一定获得锁的机会

四、InNoDB存储引擎的锁

与InnoDB与MyISAM的最大不同有两点1、支持事务2、采用行锁

（一）支持事务

1、需要提到的事务ACID（原子性、一致性。隔离性和持久性） 2、事务的并发处理导致的问题 （1）更新丢失：读取数据后，被其他事务覆盖数据 （2）读取脏数据：读取数据后，更新数据的事务回滚了，也就是读取的数据不正确 （3）不可重复读：由于其他事物的插手，在同一事务中两次相同的查询数据是不同的（由于修改导致） （4）幻读：返回记录数不同（由于新增或者删除导致） 3、事务隔离级别 更新数据丢失不仅仅是数据库事务控制器解决，主要由应用解决。本来是为了实现事务的并发，以下对于操作对于并发的副作用越来越小，但付出的代价越来越大 （1）读未提交的数据（Read uncommitted）：可能有有脏读、不可重复度、幻读的问题 （2）读提交的数据（Read committed），没有脏读的问题，可能有不可重复度、幻读的问题 （3）可重复读（Repeatable read）：没有脏读、不可重复度的问题，可能有幻读的问题 （4）可序列化（Serializable）：没有脏读、不可重复度、幻读的问题

（二）行锁

行级锁分为： 记录锁（Record lock）：对索引项加锁，即锁定一条记录。 间隙锁（Gap lock）：对索引项之间的‘间隙’、对第一条记录前的间隙或最后一条记录后的间隙加锁，即锁定一个范围的记录，不包含记录本身 Next-key Lock：锁定一个范围的记录并包含记录本身（上面两者的结合）。

1、共享锁与排他锁

（1）共享锁：事务对数据添加了读锁，事务只能读而不能修改，其他事务也只能加读锁，期间不能修改，直到事务释放读锁 （2）排他锁：事务获取写锁后，只有自己可以操作（读取或者修改），而其他事务不能操作

2、意向共享锁与意向排他锁

• 意向共享锁（IS）：事务打算给数据行共享锁;，事务在给一个数据行加共享锁前必须先取得该表的IS锁
• 意向排他锁（IX）事务打算给数据行加排他锁;事务在给一个数据行加排他锁前必须先取得该表的IX锁

注意：普通select操作不会添加任何锁

如果一个事务请求的锁模式与当前锁兼容，InnoDB就请求的锁授予该事务;

3、行级锁（Record lock）导致的死锁

为什么会产生死锁？ 1、产生死锁原理：在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。 2、死锁导致原因：当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。 3、如何避免死锁： 用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施 （1）如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。 （2）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率； （3）对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率； （4）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

4、行级锁的间隙锁（Next-Key lock）

间隙锁实例博客：https://blog.csdn.net/why15732625998/article/details/80439315#commentBox

（1）什么时候会出现间隙锁？ 用法：select * from 表名 where 字段名>参数**（在一个范围内）** for update； 使用范围条件而不是相等条件检索数据，InnoDB除了给索引记录加锁，还会给不存在的记录（间隙）加锁，其他事务不能操作当前事务锁定的索引与间隙 （2）目的 (a)防止幻读,避免其他事务插入数据 (b)满足其恢复和复制的需要，MySQL的恢复机制是通过BINLOG记录来执行IUD操作来同步Slave的，这就要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，为了恢复不能插入其他事务

5、什么时候使用表锁？

绝大部分情况使用行锁，但在个别特殊事务中,也可以考虑使用表锁

1、事务需要更新大部分数据，表又较大 若使用默认的行锁,不仅该事务执行效率低(因为需要对较多行加锁,加锁是需要耗时的); 而且可能造成其他事务长时间锁等待和锁冲突; 这种情况下可以考虑使用表锁来提高该事务的执行速度 2、事务涉及多个表，较复杂，很可能引起死锁，造成大量事务回滚 这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ

• lock tables 加表锁，需要设置set autocommite=0来关闭自动提交，否则ＭySQL不会给表加锁，最后使用UNLOCK TABLES释放表锁，以下是定义写锁

五、乐观锁与悲观锁

1、悲观锁

行锁、表锁、读锁、写锁都是在操作之前先上锁

（1）悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

流程： （1）在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。 如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。 （2）其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

（2）优缺点： 优点：悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。 缺点： （a）在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会； （b） 在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

2、乐观锁

乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。 如果系统并发量非常大，悲观锁会带来非常大的性能问题，选择使用乐观锁，现在大部分应用属于乐观锁

版本控制机制： 每一行数据多一个字段version，每次更新数据对应版本号+1， 原理：读出数据，将版本号一同读出，之后更新，版本号+1，提交数据版本号大于数据库当前版本号，则予以更新，否则认为是过期数据，重新读取数据

使用时间戳实现： 每一行数据多一个字段time 原理：读出数据，将时间戳一同读出，之后更新，提交数据时间戳等于数据库当前时间戳，则予以更新，否则认为是过期数据，重新读取数据