锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。 锁保证数据并发访问的一致性、有效性;锁冲突也是影响数据库并发访问性能的一个重要因素。 锁是 MariaDB 在服务器层和存储引擎层的的并发控制。

加锁是消耗资源的,锁的各种操作,包括获得锁、检测锁是否是否已解除、释放锁等。

共享锁与排他锁

共享锁(读锁):其他事务可以读,但不能写。

排他锁(写锁) :其他事务不能读取,也不能写。

粒度锁

不同的存储引擎支持不同的锁机制,所有的存储引擎都以自己的方式显现了锁机制,服务器层完全不了解存储引擎中的锁实现:

  • MyISAM 和 MEMORY 存储引擎采用的是表锁
  • BDB 存储引擎采用的是页面锁,但也支持表锁
  • InnoDB 存储引擎既支持行锁,也支持表锁,但默认情况下是采用行级锁

默认情况下,表锁和行锁都是自动获得的, 不需要额外的命令。

但是在有的情况下,用户需要明确地进行锁表或者进行事务的控制,以便确保整个事务的完整性,这样就需要使用事务控制和锁定语句来完成。

不同粒度锁的比较:

表锁行锁页面锁
开销小️中等
速度中等
死锁
锁定粒度中等
锁冲突概率中等
并发度中等

表级锁

开销小,加锁快,不会出现死锁,锁定粒度大,发生锁冲突的概率最高,并发度最低。 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。

表级锁更适合于以查询为主,并发用户少,只有少量按索引条件更新数据的应用。

行级锁

开销大,加锁慢,会出现死锁,锁定粒度最小,发生锁冲突的概率最低,并发度也最高。 最大程度的支持并发,同时也带来了最大的锁开销。

在 InnoDB 中,除单个 SQL 组成的事务外,锁是逐步获得的,这就决定了在 InnoDB 中发生死锁是可能的。 行级锁只在存储引擎层实现,而服务器层没有实现。

行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用

页面锁

开销和加锁时间界于表锁和行锁之间,会出现死锁,锁定粒度界于表锁和行锁之间,并发度一般。

MyISAM 表锁

  • 表共享读锁:不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求
  • 表独占写锁:会阻塞其他用户对同一表的读和写操作

MyISAM 表的读操作与写操作之间,以及写操作之间是串行的。 当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作。 其他线程的读写操作都会等待,直到锁被释放为止。

默认情况下,写锁比读锁具有更高的优先级。 当一个锁释放时,这个锁会优先给写锁队列中等候的获取锁请求,然后再给读锁队列中等候的获取锁请求。

这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因。 因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。 同时,一些需要长时间运行的查询操作,也会使写线程堵塞,应用中应尽量避免出现长时间运行的查询操作。

在可能的情况下可以通过使用中间表等措施对 SQL 语句做一定程度的分解,使每一步查询都能在较短时间完成,从而减少锁冲突。 如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。

可以设置改变读锁和写锁的优先级:

  • 通过指定启动参数 low-priority-updates 使 MyISAM 引擎默认给予读请求以优先的权利
  • 通过执行命令 SET LOW_PRIORITY_UPDATES=1 使该连接发出的更新请求优先级降低
  • 通过指定 INSERT UPDATE DELETE 语句的 LOW_PRIORITY 属性降低该语句的优先级
  • 给系统参数 max_write_lock_count 设置一个合适的值,当读锁达到这个值后会将写请求的优先级降低,给读进程一定的机会

MyISAM 加表锁方法:

  • MyISAM 在 SELECT 前会自动给涉及的表加读锁
  • 在执行更新操作 UPDATE DELETE INSERT 等前会自动给涉及的表加写锁

这个过程并不需要用户干预,因此,用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

在自动加锁的情况下,MyISAM 总是一次获得 SQL 语句所需要的全部锁,这也正是 MyISAM 表不会出现死锁的原因。

MyISAM 存储引擎支持并发插入,以减少给定表的读和写操作之间的争用:

如果 MyISAM 表在数据文件中间没有空闲块,则行始终插入数据文件的末尾。 在这种情况下可以自由混合并发使用 MyISAM 表的 INSERT SELECT 语句而不需要加锁。

你可以在其他线程进行读操作的时候,同时将行插入到 MyISAM 表中。 文件中间的空闲快可能是从表格中间删除或更新的行而产生的。 如果文件中间有空闲快,则并发插入会被禁用,但是当所有空闲块都填充有新数据时,它又会自动重新启用。

要控制此行为,可以使用 concurrent_insert 变量。

如果使用 LOCK TABLES 显式获取表锁,则可以请求 READ LOCAL 锁而不是 READ 锁,以便在锁定表时,其他会话可以使用并发插入。

  • concurrent_insert 为 0 时不允许并发插入
  • concurrent_insert 为 1 且表中没有空洞时,允许在一个线程读的同时,另一个线程从表尾插入
  • concurrent_insert 为 2 时,无论表中有没有空洞,都允许在表尾并发插入记录

查询表级锁争用情况:

可以通过检查 table_locks_waitedtable_locks_immediate 状态变量来分析系统上的表锁的争夺 如果 Table_locks_waited 的值比较高,则说明存在着较严重的表级锁争用情况。

总结

  • MyISAM 引擎支持表锁
  • 表级锁分为两种:共享读锁、互斥写锁,这两种锁都是阻塞锁
  • 可以在读锁上增加读锁,不能在读锁上增加写锁,在写锁上不能增加写锁
  • 默认情况下,M执行查询语句之前会加读锁,在执行更新语句之前会执行写锁
  • 如果想要显示的加锁/解锁的花可以使用 LOCK TABLESUNLOCK
  • 在使用 LOCK TABLES 之后,在解锁之前不能操作未加锁的表
  • 在加锁时指明是要增加读锁,那么在解锁之前只能进行读操作,不能执行写操作
  • 如果一次语句要操作的表以别名的方式多次出现,那么就要在加锁时都指明要加锁的表的别名
  • concurrent_insert 专门用以控制其并发插入的行为,其值分别可以为 0、1、2
  • 由于读写锁互斥,在调度过程中,写锁优先的原则。可以通过 low-priority-updates 设置

InnoDB 行锁和表锁

  • 共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁
  • 排他锁:允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁
  • 意向共享锁:事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的意向共享锁
  • 意向排他锁:事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的意向排他锁

锁模式的兼容情况

共享锁排他锁意向共享锁意向排他锁
共享锁兼容冲突兼容冲突
排他锁冲突冲突冲突冲突
意向共享锁兼容冲突兼容兼容
意向排他锁冲突冲突兼容兼容

如果一个事务请求的锁模式与当前的锁兼容, InnoDB 就将请求的锁授予该事务;反之,该事务就要等待锁释放。

加锁方法

  • 意向锁是自动加的,不需用户干预
  • 对于 UPDATE DELETE INSERT 语句会自动给涉及数据集加排他锁
  • 对于普通 SELECT 语句不会加任何锁
  • 事务可以通过以下语句显式给记录集加共享锁或排他锁:
  • 共享锁:SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他会话可以查并也可以对该记录加 share mode 的共享锁,但是如果当前事务需要对该记录进行更新操作,则很有可能造成死锁。

  • 排他锁:SELECT * FROM table_name WHERE ... FOR UPDATE。 其他会话可以查询该记录,但是不能对该记录加共享锁或排他锁,而是等待获得锁。

  • 隐式锁定:

InnoDB 在事务执行过程中,使用两阶段锁协议: 随时都可以执行锁定,InnoDB 会根据隔离级别在需要的时候自动加锁, 锁只有在执行 commit 或者 rollback 的时候才会释放,并且所有的锁都是在同一时刻被释放。

  • 显式锁定 :
  • 共享锁:SELECT ... LOCK IN SHARE MODE。 一般情况下,为了确保自己查到的数据没有被其他的事务正在修改,也就是说确保查到的数据是最新的数据,并且不允许其他人来修改数据。 但是自己不一定能够修改数据,因为有可能其他的事务也对这些数据,使用这种方式上共享锁。 作用就是将查找到的数据加上一个共享锁,这个就是表示其他的事务只能对这些数据进行简单的 select 操作,并不能够进行 DML 操作。

  • 排他锁:SELECT ... FOR UPDATE。 一般情况下,为了让自己查到的数据确保是最新数据,并且查到后的数据只允许自己来修改的时候,需要用到这个语句。 在执行这个查询语句的时候,会将对应的索引访问条目进行上排他锁,也就是说这个语句对应的锁就相当于 update 带来的效果。

显示锁定在业务繁忙的情况下,如果不能及时 commit 或者 rollback 可能会造成其他事务长时间的等待,影响并发使用效率。

区别在于,前一个是共享锁,多个事务可以同时的对相同数据执行, 后一个上的是排他锁,一旦一个事务获取了这个锁,其他的事务是没法在这些数据上执行 FOR UPDATE

行锁实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的,只有通过索引条件检索数据,才使用行级锁,否则使用表锁. Oracle 是通过在数据块中对相应数据行加锁来实现的。

不论是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。

只有执行计划真正使用了索引,才能使用行锁。 即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的。 如果 MySQL 认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。 在分析锁冲突时,别忘了检查 SQL 的执行计划以确认是否真正使用了索引。

由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然多个会话是访问不同行的记录, 但是如果是使用相同的索引键,是会出现锁冲突的,后使用这些索引的会话需要等待先使用索引的会话释放锁后才能获取锁。

间隙锁

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

在使用范围条件检索并锁定记录时,InnoDB 这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。 在实际应用开发中,尤其是并发插入比较多的应用,要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。

使用间隙锁可以防止幻读,以满足相关隔离级别的要求,还能满足恢复和复制的需要:

MySQL 通过 Binlog 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句,并由此实现 MySQL 数据库的恢复和主从复制。

MySQL 的恢复机制(复制其实就是在 Slave MySQL 不断做基于 Binlog 的恢复)有以下特点:

  1. MySQL 的恢复是 SQL 语句级的,也就是重新执行 Binlog 中的 SQL 语句
  2. MySQL 的 Binlog 是按照事务提交的先后顺序记录的,恢复也是按这个顺序进行的

MySQL 的恢复机制要求:在一个事务未提交前,其他并发事务不能插入满足其锁定条件的任何记录,也就是不允许出现幻读。

事务隔离性与锁

一般来说,直接操作数据库中各种锁的几率相对比较少,更多的是利用数据库提供的四个隔离级别。

隔离级别脏读丢失更新不可重复读幻读
未提交读✔️✔️✔️✔️
已提交读✔️✔️
可重复读✔️
可串行读

隔离级别越高,锁就越严格,尤其是使用范围条件的时候,产生锁冲突的可能性也就越高,从而对并发性事务处理性能的影响也就越大。

在应用中应该尽量使用较低的隔离级别,以减少锁争用的机率。 实际上,通过优化事务逻辑,大部分应用使用 Read Commited 隔离级别就足够了。 对于一些确实需要更高隔离级别的事务,可以通过在程序中动态改变隔离级别的方式满足需求。

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

获取行锁争用情况

可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况。

show status like 'innodb_row_lock%';

LOCK TABLES 和 UNLOCK TABLES

LOCK TABLESUNLOCK TABLES 都是在服务器层实现的,和存储引擎无关,它们有自己的用途,并不能替代事务处理。 除了禁用了 autocommint 后可以使用,其他情况不建议使用。

LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定,则当前线程会等待,直到可以获取所有锁定为止。

UNLOCK TABLES 可以释放当前线程获得的任何锁定。 当前线程执行另一个 LOCK TABLES 时或当与服务器的连接被关闭时,所有由当前线程锁定的表被隐含地解锁。

使用LOCK TABLES的场景

显式加表级锁一般是为了在一定程度模拟事务操作,实现对某一时间点多个表的一致性读取。

给表显式加表锁时,必须同时取得所有涉及到表的锁。 MySQL 不支持锁升级,在执行 LOCK TABLES 后,只能访问显式加锁的这些表,不能访问未加锁的表。 同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。

其实,在 MyISAM 自动加锁的情况下也大致如此,MyISAM 总是一次获得 SQL 语句所需要的全部锁。 这也正是 MyISAM 表不会出现死锁的原因。

死锁

死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环。

当事务试图以不同的顺序锁定资源时,就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句,有些存储引擎会产生死锁有些不会。

死锁原因

真正的数据冲突和存储引擎的实现方式。

检测死锁

数据库系统实现了各种死锁检测和死锁超时的机制。 InnoDB 存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复

死锁发生以后,只有部分或完全回滚其中一个事务,才能打破死锁。 InnoDB 目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚。 所以事务型应用程序在设计时必须考虑如何处理死锁,多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测

发生死锁后,InnoDB 一般都能自动检测到,并使一个事务释放锁并回退,另一个事务获得锁,继续完成事务。 但在涉及外部锁或涉及表锁的情况下,InnoDB 并不能完全自动检测到死锁。 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。

影响性能影响

死锁会影响性能而不是会产生严重错误,因为 InnoDB 会自动检测死锁状况并回滚其中一个受影响的事务。 在高并发系统上,当许多线程等待同一个锁时,死锁检测可能导致速度变慢。 有时当发生死锁时,禁用死锁检测 innodb_deadlock_detect 可能会更有效。 这时可以依赖 innodb_lock_wait_timeout 设置进行事务回滚。

避免死锁

  • 在自动加锁的情况下,MyISAM 总是一次获得 SQL 语句所需要的全部锁,所以 MyISAM 表不会出现死锁。

  • 为了在单个 InnoDB 表上执行多个并发写入操作时避免死锁, 可以在事务开始时通过为预期要修改的每个元祖使用 SELECT ... FOR UPDATE 语句来获取必要的锁, 即使这些行的更改语句是在之后才执行的。

  • 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁、更新时再申请排他锁, 因为这时候当用户再申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。

  • 如果事务需要修改或锁定多个表,则应在每个事务中以相同的顺序使用加锁语句。 在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以降低产生死锁的机会。

  • 通过 SELECT ... LOCK IN SHARE MODE 获取行的写锁后,如果当前事务再需要对该记录进行更新操作,则很有可能造成死锁。

  • 改变事务隔离级别

如果出现死锁,可以用 SHOW INNODB STATUS 确定最后一个死锁产生的原因。 返回结果中包括死锁相关事务的详细信息,如引发死锁的 SQL 语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。 据此可以分析死锁产生的原因和改进措施。

乐观、悲观锁

  • 乐观锁:假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性,不能解决脏读的问题。

乐观锁每次去拿数据的时候都认为别人不会修改,所以不会上锁。 在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制。 乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库如果提供类似于 write_condition 机制的其实都是提供的乐观锁。

  • 悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作。

悲观锁每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁。 传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁。

优化锁性能

  • 尽量使用较低的隔离级别;
  • 精心设计索引,并尽量使用索引访问数据,使加锁更精确
  • 选择合理的事务大小,小事务发生锁冲突的几率也更小
  • 给记录集显示加锁时,最好一次性请求足够级别的锁
  • 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行
  • 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响
  • 不要申请超过实际需要的锁级别
  • 除非必须,查询时不要显示加锁。 MySQL 的 MVCC 可以实现事务中的查询不用加锁,优化事务性能
  • 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能
powered by Gitbook该文件修订时间: 2020-04-10 10:05:54

results matching ""

    No results matching ""

    results matching ""

      No results matching ""