mysql锁机制

最新推荐文章于 2024-05-09 13:23:55 发布

haoxin963

最新推荐文章于 2024-05-09 13:23:55 发布

阅读量195

点赞数

分类专栏：计算机基础 mysql 文章标签： mysql锁机制

本文链接：https://blog.csdn.net/haoxin963/article/details/84578527

版权

计算机基础同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

mysql

3 篇文章 0 订阅

订阅专栏

一、MySQL 锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源(如CPU、RAM、I/O等)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL 两种锁特性归纳：

表级锁：开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度也最高。
页级锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁。开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

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

myisam 和 memory 存储引擎采用的是表级锁；
innodb 存储引擎既支持行级锁，也支持表级锁，但默认情况下采用行级锁。
BDB 存储引擎支持页级锁但已经逐渐被InnoDB替代了，这里暂不讨论

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 web 应用；
而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。

二、 myisam 表锁

1. 查询表级锁争用情况

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定争夺：

MySQL [sakila]> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 100   |
| Table_locks_waited    | 0     |
+-----------------------+-------+

如果 table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况。

2. MySQL 表级锁的锁模式

MySQL 的表级锁有两种模式，表共享读锁(table read lock)和表独占写锁（table write lock）。

对 myisam 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 myisam 表的写操作，则会阻塞其他用户对同一表的读和写操作；myisam 表的读操作和写操作之间，以及写操作之间时串行的。

当一个线程获得对一个表的写锁户，只有持有锁的线程可以对表进行更新操作，其他线程的读、写操作都会等待，直到锁被释放。

3. 加锁

myisam 在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行更新操作（update、delete、insert等）前，会自动给涉及的表加写锁，这个过程并不需要直接用 lock table 命令给 myisam 表显示加锁。给 myisam 表显式加锁，一般是为了在一定程度模拟事务操作
myisam 在自动加锁的情况下，总是一次获得 sql 语句所需要的全部锁，所以显示锁表的时候，必须同时取得所有涉及表的锁，这也正是 myisam 表不会出现死锁（deadlock）的原因。

注意：在使用 lock tables 时，不仅需要一次锁定用到的所有表，而且，同一个表在 sql 语句中出现多少次，就要通过与 sql 语句中相同的别名锁定多少次，否则会报错。

4. 并发插入（concurrent inserts）

myisam 表的读和写是串行的，但这是就总体而言的。在一定条件下，myisam 表也支持查询和插入操作的并发进行。
myisam 存储引擎有一个系统变量 concurrent_insert , 专门用以控制其并发插入的行为，其值分别可以为0,1,2。

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

5. myisam 的锁调度

myisam 存储引擎的读锁和写锁是互斥的，读写操作时串行的。当一个进程请求某个 myisam 表的读锁，同时另一个进程也请求同一表的写锁时，写进程会先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前，这是因为 mysql 认为写请求一般比读请求重要。这也正是 myisam 表不太适合有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。

通过一些参数设置可以调节 MySQL 的默认调度行为：

通过指定启动参数 low-priority-updates, 使 myisam 引擎默认给予读请求以优先的权利。
通过执行命令 set low_priority_updates = 1, 使该连接发出的更新请求优先级降低。
通过指定 insert、update、delete 语句的 low_priority 属性，降低该语句的优先级。

上述方式都是要么更新优先，要么查询优先，MySQL 也提供了一种折中的办法调节读写冲突：

给系统参数 max_write_lock_count 设置一个合适的值，当一个表的读锁达到这个值后，MySQL 就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

三、 InnoDB 锁

innodb 与 myisam 的最大不同有两点，一是支持事务（transaction），二是采用了行级锁。

1. 并发事务处理存在的问题

相对于串行处理来说，并发事务处理能力大大增加数据库资源的利用率，提高数据库系统事务吞吐量，从而可以支持更多的用户，但并发事务处理也会带来一些问题：

更新丢失（lost update）

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，最后的更新覆盖了由其他事务所做的更新。

脏读（dirty reads）

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。

不可重复读（non-repeatable reads）

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变或某些记录已经被删除了！这种现象就是“不可重复读”。

幻读（phantom reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象称为“幻读”。

2. 事务隔离级别

并发事务处理带来的问题中，“更新丢失”，只通过mysql事务是避免不了的，需要应用程序对要更新的数据加必要的锁来解决。
“脏读”，“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大。这是因为事务隔离实质上是将事务在一定程度上"串行"进行，这显然与"并发"是矛盾的。根据自己的业务逻辑，权衡能接受的最大副作用。从而平衡了"隔离" 和 "并发"的问题。MySQL默认隔离级别是可重复读。脏读，不可重复读，幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

+------------------------------+---------------------+--------------+--------------+--------------+
| 隔离级别                      | 读数据一致性         | 脏读         | 不可重复 读   | 幻读         |
+------------------------------+---------------------+--------------+--------------+--------------+
| 未提交读(Read uncommitted)    | 最低级别            | 是            | 是           | 是           | 
+------------------------------+---------------------+--------------+--------------+--------------+
| 已提交读(Read committed)      | 语句级              | 否           | 是           | 是           |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可重复读(Repeatable read)     | 事务级              | 否           | 否           | 是           |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可序列化(Serializable)        | 最高级别，事务级     | 否           | 否           | 否           |
+------------------------------+---------------------+--------------+--------------+--------------+

查看当前数据库的事务隔离级别：show variables like 'tx_isolation';

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

3. 查看 Innodb 行锁争用情况

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

MySQL [sakila]> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

如果发现锁争用比较严重，如 Innodb_row_lock_waits 和 Innodb_row_lock_time_avg 的值比较高，可以通过查询 information_schema 数据库中相关的表来查看锁情况，或者通过设置 innodb monitors 来进一步观察。

(1)查询 information_schema 数据库中的表了解锁等待

MySQL [sakila]> use information_schema
Database changed
MySQL [information_schema]> select * from innodb_locks \G;

(2) 通过设置 innodb monitors 观察锁冲突情况

MySQL [sakila]> create table innodb_monitor (a int) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

show engine innodb status \G;

4. innodb 的行锁模式及加锁方法

Innodb 实现了两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务获取相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁共存，事项多粒度锁机制，innodb 还有两种内部使用的意向锁，这两种意向锁都是表锁：

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

如果一个事务请求的锁模式与当前的锁兼容，innodb 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
意向锁是 innodb 自动加的，不需要用户干预。对于 update、delete 和 insert 语句，innodb 会自动给涉及数据集加排它锁（X）；对于普通 select 语句，innodb 不会加任何锁。

事务可以通过以下语句显式给记录集加共享锁或排它锁。

共享锁（S）：select * from table_name where ... lock in share mode.
排它锁（X）: select * from table_name where ... for update.

用 select... in share mode 获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行 update 或者 delete 操作。但是如果当前事务也需要对该记录进行更新操作,则有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 select... for update 方式获得排他锁。

5. next-key 锁

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

举例来说，假如 emp 表中只有 101 条记录，其 id 的值分别是1、2、...、100、101，下面的 sql:

# 这是一个范围条件的检索，innodb 不仅会对符合条件的 id 值为 101 的记录加锁，也会对 id 大于 101（这些记录并不存在）的“间隙”加锁。
select * from emp where id > 100 for update;

为什么使用 next-key 锁

innodb 使用 next-key 锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了 id 大于 100 的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另一方面，是为了满足其恢复和复制的需要。

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

innodb 除了通过范围条件加锁时使用 next-key 锁外，如果使用相等条件请求给一个不存在的记录加锁，innodb 也会使用 next-key 锁！

6.死锁

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。