时间戳

首页 > 技术文章 > Mysql锁

Mysql锁

will-xz 2021-02-08 09:56 原文

Mysql锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。传统的计算机资源（CPU、RAM、I/O）的争用，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题。锁冲突也是影响数据库并发访问性能的一个重要因素。 MySIAM和MEMORY存储引擎采用的是表级锁；BDB存储引擎采用的是页面锁，但也支持表级锁；Innodb存储引擎既支持行级锁，也支持表级锁，默认情况下是采用行级锁。 Mysql的三种锁特性大致如下：

  1. 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率高，并发度最低
  2. 行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突概率最低，并发度也高
  3. 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁粒度界于表锁和行锁之间，并发度一般 应用场景
  4. 表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如web应用
  5. 行级锁则更适合于大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

MyISAM锁

MyISAM存储引擎只支持表锁

MyISAM表锁模式

  1. 对于MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
  2. 对于MyISAM表的写操作，则会阻塞其他用户对同一表的读写操作
  3. MyISAM表的读与写操作之间，以及写与写操作之间是串行的

如何加表锁

MyISAM在执行查询语句前，会自动给涉及的所有表加表读锁；在执行更新（Insert、Update、Delete等）前，会自动给涉及的表加写锁，这个过程中不需用户干预

并发插入

一定条件下，MyISAM表也支持查询和插入操作的并发进行（Read、Insert），不允许Update、Delete MyISAM存储引擎有一个系统变量concurrent_insert，专门用于控制其并发插入的行为：

  1. concurrent_insert = 0，不允许并发插入
  2. concurrent_insert = 1（默认设置），如果MyISAM表中没有空洞（表的中间没有被删除的行，空缺行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录
  3. concurrent_insert = 2，无论表有没有空洞，都允许在尾部并发插入

利用并发插入来解决应用中同一表查询和插入的锁争用。同时通过定期在系统空闲时间执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空间

MyISAM锁调度

当一个进程请求MySIAM表的读锁，同时另一个进程也请求同一个表的写锁，Mysql会如何处理？MyISAM会让写锁进程先执行，不仅如此，即使读请求先到锁队列，写请求后到，写锁也会插入到读锁请求之前。这是因为Mysql认为写请求一般比读请求更重要。这也是MySIAM表不太适合大量更新操作和查询操作的原因，因为大量更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。 可以通过设置MySIAM来调节行为：

  1. 通过指定启动参数low-priority-updates，使MySIAM引擎默认给予读请求优先写请求
  2. 通过执行命令set low_priority_updates=1，使该链接发出的更新请求优先度降低
  3. 通过指定INSERT、UPDATE、DELETE语句的low_priority属性，降低该语句的优先级

Innodb锁

并发事务带来的问题

更新丢失：

多个事务选择同一行，然后对这一行数据进行更新，由于每个事务不知道彼此的存在，所以存发生更新丢失问题（覆盖）。例如腾讯文档的多人编辑，但是不知道彼此存在

解决办法：程序加锁

脏读：

多个事务读取同一行，其中一个事务对这个事务的变动后为提交，另一个事务读取的这一行数据不是最新数据

解决办法：读前加锁、多版本并发控制 本质：读取数据的一致性，数据被变动

不可重复读

同一个事务读取同一波数据，不同时间内，数据发生变化，即数据可能被修改或删除。例如：where id = 2，这次读取 status=1，第二次读取 status = 0 或数据已被删除

解决办法：读前加锁、多版本并发控制 本质：读的数据被改动

幻读

同一个事务以相同查询条件读取一波数据后，第二次读取发现数据被其他数据插入了满足该查询条件的数据。例如：查询status = 0的数据，这次读取数据只有10条，下次读取数据有11条。（幻读：事务A读取某范围数据进行中，事务B在这个范围内插入一条记录并提交，事务A再次读取范围数据时，会多出一条数据）

本质：读的数据范围内新增了其他数据

更新丢失的解决办法：程序加锁 本质：并发串行化

脏读、不可重复读、幻读的解决办法：读前加锁、多版本并发控制 本质：读取数据的一致性

事务

事务提供一种机制，可以将一个活动涉及的所有操作纳入一个不可分割的执行单位，组成事务的所有操作只有在所有操作均能正常执行的情况下方能提交，只要其中任一操作执行失败，都将导致整个事务的回滚。

分布式事务指在多节点、多机器、多服务的环境中的事务问题，通常会采用消息中间件来处理相关问题，消息中间件在分布式系统中主要用于异步通信、解耦、并发缓存。通过引入消息中间件来解耦应用间（服务间）的直接调用，同时也会起到异步通信和缓冲并发的作用

事务的特性（ACID）：

原子性：事务是一个不可分割的执行单位，事务中的所有操作要么全部执行，要么全部不执行 一致性：事务在开始前和结束后，数据库的完整性约束没有被破坏 隔离性：事务的执行是相互独立的，它们不会相互干扰，一个事务不会看到另一个正在运行过程中的事务的数据 持久性：一个事务完成之后，事务的执行结果必须是持久化保存的。即使数据库发生崩溃，在数据库恢复后事务提交的结果仍然不会丢失

数据库四种隔离级别

Read uncommitted（读未提交）：一个事务对一行数据修改的过程中，不允许另一个事务对该行数据进行修改，但允许另一个事务对该行数据读。因此本级别下，不会出现更新丢失，但会出现脏读、不可重复读

Read commited（读提交）：未提交的写事务不允许其他事务访问该行，因此不会出现脏读，但是读取数据的事务允许其他事务访问该行，因此会出现不可重复读的情况

Repeatable read（重复读）：读事务禁止写事务，但允许读事务，因此不会出现同一事务两次读到不同数据的情况（不可重复读），且写事务禁止其他一切事务

Serializable（序列化）：所有事务都必须串行执行，因此能避免一切因并发引起的问题，但效率很低

事务并发执行，会出现什么问题

  1. 更新丢失（写写冲突）：在数据库没有加任何锁操作的情况下，当有两个并发执行的事务，更新同一行数据，有可能有一个事务会把另一个事务的更新覆盖掉
  2. 脏读（读写冲突）：一个事务读到另一个尚未提交的事务中的数据，该数据可能会被回滚从而失效，如果第一个事务获取失效的数据区处理那就发生错误了
  3. 不可重复读（读写冲突）：一个事务对同一行数据读了2次，却得到不同的结果。具体分两种情况：虚读，在事务1两次读取同一记录的过程中，事务2对该记录进行修改，从而事务1第二次读到了不一样的记录；幻读，事务1在两次查询的过程中，事务2对该表进行了插入、删除操作，从而事务1第二次查询的结果发生变化
不可重复读和脏读的区别？

脏读读到的是尚未提交的数据，而不可重复读读到的是已提交的数据，只不过在两次读的过程中数据被另一个事务改过了

可重复读与提交读有什么区别？

幻读行为是在事务在进行中，其他事务对数据进行删除、新增提交引起的。提交读与可重复读，是针对数据的修改时锁产生的行为差异

Innodb的行锁模式及加锁方法

Innodb实现了一下两种类型行锁

共享锁（S）：允许一个事务读一行，阻止其他事务获得相同数据集的排他锁

排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务获得相同数据的共享锁或排他锁

Innodb为了实现行锁和表锁共存，实现了多粒度（表锁行锁）锁机制，Innodb内部还实现了意向锁，这两种锁都是表锁

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在此之前必须获得该表的IS锁

意向排他锁（IX）：事务打算给数据行更新数据加排他锁，事务在此之前必须获得该表的IX锁

Innodb行锁模式兼容性列表
X        IX        S        IS
X        冲突    冲突        冲突    冲突
IX        冲突    兼容        冲突    兼容
S        冲突    冲突        兼容    兼容
IS        冲突    兼容        兼容    兼容

 

  1. 兼容锁会授权给该事务，冲突则需等待
  2. 意向锁是Innodb自动加的，不需用户干预
  3. 对于Insert、Update、Delete，mysql会自动给数据集加上排他锁， 对于Select，mysql不会加任何锁
  4. mysql8.0对显示加锁发生调整，S锁，where...FOR SHARE、X锁，where...FOR UPDATE[NOWAIT|SKIP]。nowait则表示如果有其他事务正在使用加排他锁，则返回错误，skip表示如果有其他事务正在加排他锁，则跳过，更新其他数据。（往往不符合应用场景）

Innodb行锁实现方式

Inndob行锁是通过给索引上的索引项加锁来实现的，如果没有索引，Inndob将通过隐藏的聚簇索引来记录加锁。 （聚簇索引，如果没有主键，则内部会选择非null列作为聚簇索引列，如果没有，则内部会选择隐藏的索引列）

Innodb行锁分为三种情况
  1. Record lock：对索引项加锁
  2. Gap lock：对索引项之间的“间隙”。第一条记录前的“间隙”或最后一条记录后的“间隙”加锁
  3. Next-key lock：前两种的组合，对记录及前面的间隙加锁

Innodb这种行锁实现特点意味着：如果不通过索引条件检索数据，那么Innodb将表中的所有记录，实际效果跟表锁一样。实际应用中要注意Innodb行数的这一特性，否则可能导致大量的锁冲突，从而影响并发性能。

  1. 在不通过索引条件查询时候，Innodb会索引表中的所有记录
  2. 由于Mysql的行锁是针对索引加的锁， 不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的
  3. 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同行，不论是使用主键索引、唯一索引或普通索引，Innodb都会使用行锁来对数据加锁
  4. 即便在条件中使用了索引字段，但是否使用索引来检索数据是由Mysql通过判断不同执行计划的代价来决定的。如果Mysql认为全表扫描效率高，比如小表，它就不会使用索引，会对所有记录加锁
  5. Mysql能够进行类型转换，却不会使用索引，从而导致Innodb对所有记录加锁。 where name = 1。name 是varchar类型，1是整型，则不会使用到索引，会对整记录加锁

什么时候使用表锁

  1. 事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度
  2. 事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。 注意：
  3. 使用LOCK TABLES虽然可以给Innodb加表级锁，但是表锁不是Innodb引擎管理的，而是MysqlServer负责的，仅当autocommit=0、innodb_table_locks=1（默认），innodb层才能知道Mysql加的表锁，MysqlServer才能感知Innodb加的行锁，这种情况下Innodb才能自动识别涉及表锁的死锁，否则，Innodb将无法自动检测并处理这种死锁
  4. 将autocommit=0，否则Mysql不会给表加锁，事务结束前，不要使用Unlock tables释放表，因为unlock tables会隐含地提交事务，commit或rollback并不能释放lock tables加的表级锁，必须用unlock tables释放表锁，如果遇到异常需要释放，则在异常出现回滚事务rollback，再释放锁，unlock tables；

关于MySIAM锁

  1. 共享锁S之间是兼容的，但共享锁与排他锁是互斥的，读写是串行的
  2. 一定条件下，MySIAM允许查询和插入并发执行，可以利用这一点解决应用中对同一表查询和插入的锁争用问题，但不能更新或删除
  3. MySIAM默认的锁调度机制是写有限，这并不是适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用
  4. 由于表锁的粒度大，读写之间又是串行的，因此，如果更新操作很多，MySIAM可能会出现严重的锁等待，可以考虑用Innodb表来减少锁冲突

关于Innodb锁

  1. Innodb的行锁是基于索引实现的，如果不通过索引访问数据，Innodb会对所有数据加锁
  2. 介绍Inndob Next-key锁机制，Innodb的锁机制和一致性读策略不同
  3. 在不同隔离级别下，Innodb的锁机制和一致性读策略不同
  4. 锁冲突甚至死锁很难完全避免

推荐阅读