本文转载自https://www.dazhuanlan.com/hsun0/topics/980417

锁用来保证数据并发访问的一致性、有效性

MySQL 的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

• MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
• BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁
• InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁

MySQL 这 3 种锁的特性可大致归纳如下：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MYISAM 存储引擎的锁机制

MyISAM 存储引擎只支持表锁

查询表级锁争用情况

show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979  |
| Table_locks_waited    | 0     |
+-----------------------+-------+

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

表级锁的锁模式

MySQL 的表级锁有两种模式：

1. 表共享读锁（Table Read Lock）
2. 表独占写锁（Table Write Lock）

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁

简单来说就是 ：

• 读锁可以被读操作共享,不影响读操作,但会影响写操作
• 写锁会影响所有的读写操作

并发插入

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

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

a. 可以利用 MyISAM 存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如，将 concurrent_insert 系统变量设为 2，总是允许并发插入； b. 同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE 语句来整理空间碎片，收回因删除记录而产生的中间空洞。

主动加表锁

Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;

1. 上面的例子在 LOCK TABLES 时加了 “local” 选项，其作用就是在满足 MyISAM 表并发插入条件的情况下，允许其他用户在表尾并发插入记录，
2. 在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表
3. 同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作
4. 在自动加锁的情况下也基本如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁
5. 这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

MyISAM 的锁调度

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

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

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

INNODB 存储引擎的锁机制

InnoDB 与 MyISAM 的最大不同有两点：

• 一是支持事务（TRANSACTION）；
• 二是采用了行级锁。

获取 InnoDB 行锁争用情况

mysql> 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     |
+-------------------------------+-------+

如果InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg的值比较高,说明锁争用比较严重

InnoDB 行锁模式

行锁不影响读操作,只影响写操作

• update ,delete 会自动给涉及到的数据集加上排他锁
• 普通的 select 则不加任何锁

insert 会加什么锁呢? MySQL5.1.22 之后的版本，出现了一个新的配置选项：

• innodb_autoinc_lock_mode，它是专门用来在使用 auto_increment 的情况下调整锁策略的，目前有三种选择：
• innodb_autoinc_lock_mode = 0 (“traditional” lock mode) # 仍然是表锁
• innodb_autoinc_lock_mode = 1 (“consecutive” lock mode) #默认方式, 只锁分配 A_I 的过程,可一次分配多个
• innodb_autoinc_lock_mode = 2 (“interleaved” lock mode) # 只锁分配 A_I 的过程,来一个分配一个

主动加行锁

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

1. 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
2. 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

用 SELECT … IN SHARE MODE 获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行 UPDATE 或者 DELETE 操作 但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 SELECT… FOR UPDATE 方式获得排他锁

InnoDB 行锁实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

乐观锁与悲观锁

在多用户环境中，在同一时间可能会有多个用户更新相同的记录，这会产生冲突。这就是著名的并发性问题。

典型的冲突有：

• 丢失更新：一个事务的更新覆盖了其它事务的更新结果，就是所谓的更新丢失。例如：用户 A 把值从 6 改为 2，用户 B 把值从 2 改为 6，则用户 A 丢失了他的更新。

• 脏读：当一个事务读取其它完成一半事务的记录时，就会发生脏读取。例如：用户 A,B 看到的值都是 6，用户 B 把值改为 2，用户 A 读到的值仍为 6。

为了解决这些并发带来的问题。 我们需要引入并发控制机制。

并发控制机制

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁不能解决脏读的问题。

乐观锁应用

1. 使用自增长的整数表示数据版本号。更新时检查版本号是否一致，比如数据库中数据版本为 6，更新提交时 version=6+1,使用该 version 值 (=7) 与数据库 version+1(=7) 作比较，如果相等，则可以更新，如果不等则有可能其他程序已更新该记录，所以返回错误。
2. 使用时间戳来实现.基本思路与版本号一样

   悲观锁应用

   悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

#0.开始事务 begin work;

#1.查询出商品信息,并为这一行加上排他锁 select status from t_goods where id=1 for update;

#2.根据商品信息生成订单 insert into t_orders (id,goods_id) values (null,1);

#3.修改商品status为2 update t_goods set status=2;

#4.提交事务 commit;

• 上面的第一步我们执行了一次查询操作：select status from t_goods where id=1 for update;
• 这样就通过数据库实现了悲观锁
• 此时在 t_goods 表中，id 为 1 的 那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行
• 需要注意的是，在事务中，只有 SELECT … FOR UPDATE 或 LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般 SELECT … 则不受此影响