MySQL锁机制

标签：事务 加锁 -- 索引 MySQL 机制 where id

一 数据库的锁机制

什么是锁？为何要加入锁机制？

锁是计算机协调多个进程或线程并发访问某一资源的机制，那为何要加入锁机制呢？

因为在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。

当并发事务同时访问一个共享的资源时，有可能导致数据不一致、数据无效等问题，

例如我们在数据库的读现象中介绍过，在并发访问情况下，可能会出现脏读、不可重复读和幻读等读现象

为了应对这些问题，主流数据库都提供了锁机制，以及事务隔离级别的概念，

而锁机制可以将并发的数据访问顺序化，以保证数据库中数据的一致性与有效性

此外，锁冲突也是影响数据库并发访问性能的一个重要因素，锁对数据库而言显得尤其重要，也更加复杂。

并发控制

在计算机科学，特别是程序设计、操作系统、多处理机和数据库等领域，并发控制（Concurrency control）是确保及时纠正由并发操作导致的错误的一种机制。

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。下面举例说明并发操作带来的数据不一致性问题：

现有两处火车票售票点，同时读取某一趟列车车票数据库中车票余额为 X。两处售票点同时卖出一张车票，同时修改余额为 X -1写回数据库，这样就造成了实际卖出两张火车票而数据库中的记录却只少了一张。 产生这种情况的原因是因为两个事务读入同一数据并同时修改，其中一个事务提交的结果破坏了另一个事务提交的结果，导致其数据的修改被丢失，破坏了事务的隔离性。并发控制要解决的就是这类问题。

封锁、时间戳、乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

二 锁的分类

锁的分类(oracle)

一、按锁的粒度划分，可分为行级锁、表级锁、页级锁。（mysql支持）

二、按锁级别划分，可分为共享锁、排他锁

三、按使用方式划分，可分为乐观锁、悲观锁

四、按加锁方式划分，可分为自动锁、显式锁

五、按操作划分，可分为DML锁、DDL锁

DML锁（data locks，数据锁），用于保护数据的完整性，其中包括行级锁(Row Locks (TX锁))、表级锁(table lock(TM锁))。

DDL锁（dictionary locks，数据字典锁），用于保护数据库对象的结构，如表、索引等的结构定义。其中包排他DDL锁（Exclusive DDL lock）、共享DDL锁（Share DDL lock）、可中断解析锁（Breakable parse locks）

三 MySQL中的行级锁,表级锁,页级锁（粒度）

在DBMS中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

行级锁

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

• 特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 支持引擎：InnoDB
• 行级锁定分为行共享读锁（共享锁）与行独占写锁（排他锁） ，如下所示，用法详见下一小节

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

表级锁（偏向于读）

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

• 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
• 支持引擎：MyISAM、MEMORY、InNoDB
• 分类：表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁），如下所示

lock table 表名 read(write),表名 read(write)，.....;
//给表加读锁或者写锁，例如
mysql> lock table employee write;

Query OK, 0 rows affected (0.00 sec)

mysql> show open tables where in_use>= 1;

+----------+----------+--------+-------------+

| Database | Table    | In_use | Name_locked |

+----------+----------+--------+-------------+

| ttt      | employee |      1 |           0 |

+----------+----------+--------+-------------+

1 row in set (0.00 sec)

mysql> unlock tables; -- UNLOCK TABLES释放被当前会话持有的任何锁

Query OK, 0 rows affected (0.00 sec)

 

mysql> show open tables where in_use>= 1;

Empty set (0.00 sec)

 

mysql> 

页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁

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

---

四 行级锁之共享锁与排他锁（级别）

行级锁分为共享锁和排他锁两种。

与行处理相关的sql有：insert、update、delete、select，这四类sql在操作记录行时，可以为行加上锁，但需要知道的是：

1、对于insert、update、delete语句，InnoDB会自动给涉及的数据加锁，而且是排他锁（X）；

2、对于普通的select语句，InnoDB不会加任何锁，需要我们手动自己加，可以加两种类型的锁，如下所示

# 共享锁（S）：SELECT ... LOCK IN SHARE MODE;  -- 查出的记录行都会被锁住

# 排他锁（X)：SELECT ... FOR UPDATE;  -- 查出的记录行都会被锁住

准备表和数据，sql如下

create table employee(
id int primary key auto_increment,
name varchar(20) not null,
age int(3) unsigned not null default 20
);

insert into employee(name) values
('egon'),
('alex'),
('wupeiqi'),
('yuanhao'),
('liwenzhou'),
('jingliyang'),
('jinxin'),
('成龙'),
('歪歪'),
('丫丫'),
('丁丁'),
('星星'),
('格格'),
('张野'),
('程咬金'),
('程咬银'),
('程咬铜'),
('程咬铁')
;
update employee set age = 18 where id <=3;

实验：验证insert、update、delete是默认加排他锁的

需要知道的是，针对事务，mysql是**隐式开启、隐式提交，即mysql默认会把每条sql语句都放入一个事务中，并在该条sql语句执行完毕后自动提交，所以如果我们如果开启两个session，在一个session中直接运行update，很快就运行完毕并自动提交，而提交事务或回滚事务都会释放锁，这样话就无法验证效果了，所以我们采用手动开启事务方式进行验证，如此我们就可以自己控制事务的提交了

事务一	事务二
步骤1	start transaction;select name from employee where id = 1; -- name 为 jason	start transaction;select name from employee where id = 1; -- name 为 jason
步骤2		-- 把小写的jason变为大写，此时的update会自动加锁 update employee set name = "jason" where id = 1; -- 立即查看修改结果，name变为jason，但此时还没有commitselect name from employee where id = 1;
步骤3	-- 此处的update会阻塞在原地，因为事务二并未提交事务，即尚未释放排他锁 update employee set name = concat(name,"_NB") where id = 1;
步骤4		-- 事务二一旦提交，事务一阻塞在步骤3的操作即会运行成功commit;-- 查看修改结果，name变为大写的jasonselect name from employee where id = 1;
步骤5	-- 此处查询到的结果为jason_NBselect name from employee where id = 1;
步骤6	-- 提交之后，name持久化为jason_NBcommit;select name from employee where id = 1;

共享锁(Share Lock)

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，获准共享锁的事务只能读数据，不能修改数据直到已释放所有共享锁，所以共享锁可以支持并发读(参考下述实验三)。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁或不加锁（在其他事务里一定不能再加排他锁，但是在事务T自己里面是可以加的），反之亦然。

用法

SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁（eXclusive Lock）

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再对该行加任何类型的其他他锁（共享锁和排他锁），但是获取排他锁的事务是可以对数据就行读取和修改。

用法

SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

特例：加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通select查询没有任何锁机制。

实验一：事务二获取了排他锁，在事务一中验证上述特例

	事务一	事务二
步骤1	-- 开启事务 start transaction;	-- 开启事务start transaction;
步骤2		-- 加排他锁，锁住id<3的所有行 select * from employee where id < 3 for update;
步骤3	-- 阻塞在原地select * from employee where id = 1 for update; -- 阻塞在原地select * from employee where id = 1 lock in share mode; -- 我们看到开了排他锁查询和共享锁查询都会处于阻塞状态-- 因为id=1的数据已经被加上了排他锁，此处阻塞是等待排他锁释放。
步骤4	-- ctrl+c终止步骤3的阻塞状态 -- 注意： -- 不要按多次ctrl+c多次按会结束链接，按一次就可以了 -- 下述实验遇到阻塞都可以用采用ctrl+c的方式结束，或者等待锁超时
步骤5	-- 如果我们直接使用以下查询，即便id<3的行都被事务二锁住了-- 但此处仍可以查看到数据-- 证明普通select查询没有任何锁机制select name from employee where id = 1;
步骤6	-- 提交一下事务，不要影响下一次实验commit;	-- 提交一下事务，不要影响下一次实验commit;

实验二：事务二获取了共享锁，在其他事务里也只能加共享锁或不加锁，在事务二中验证

	事务一	事务二
步骤1	start transaction;	-- 开启事务、加共享锁，锁住id<3的所有行start transaction;select * from employee where id < 3 lock in share mode;
步骤2	-- 加排他锁，会阻塞在原地select * from employee where id = 1 for update; -- 加共享锁，可以查出结果，不会阻塞在原地select * from employee where id = 1 lock in share mode; -- 不加锁，必然也可以查出结果，不会阻塞在原地select name from employee where id = 1;
步骤3	-- 提交一下事务，不要影响下一次实验commit;	-- 提交一下事务，不要影响下一次实验commit;

实验三：事务二获取了共享锁，在其他事务里也只能加共享锁或不加锁，反之亦然，并验证在多个事务加了共享锁后，大家对加锁的数据行只能读不能写

	事务一	事务二
步骤1	start transaction;	-- 开启事务、加共享锁，锁住id<3的所有行start transaction;select * from employee where id < 3 lock in share mode;
步骤2		-- 此时尚未有其他事务为id<3的行加过锁，所以在本事务里还是可以使用update的-- 而update默认是加了排他锁update employee set name="jason" where id = 1;select name from employee where id = 1; -- 修改成功，结果为jason
步骤3	-- 加排他锁，阻塞在原地，因为事务二刚在步骤2中对id=1的行加了排他锁-- 一旦某一个事物对记录行加了排他锁后-- 其他事务均无法加任何锁select name from employee where id = 1 for update; -- 也无法加共享锁，加上共享锁，同样阻塞在原地select name from employee where id = 1 lock in share mode; -- 普通select查询，不受锁影响，可以查查结果-- 查询结果仍为修改前的结果，即jason_NB-- 因为事务二的update行为尚未提交select name from employee where id=1;
步骤4		-- 释放事务一加过的所锁，包括update加的互斥锁，还有select加的共享锁rollback; -- 手动开启事务的方式，在rollback之后事务就结束了，需要重新开启，切记start transaction;
步骤5		-- 重新对id<3的行加共享锁select * from employee where id < 3 lock in share mode;
步骤6	-- 在事务1中也对id<3的行加共享锁，可以加成功-- 因为当一个事务（比如事务2）对记录加了共享锁后-- 其他事务（比如事务1）只能对记录行加共享锁或不加锁select * from employee where id < 3 lock in share mode;
步骤7		-- 事务一已经对id<3的所有行都加了共享锁，id=1的行当然包含在内-- 所以在事物二中只能对id=1行加共享锁或者不加锁-- 而下述语句中update默认会加排他锁，所以会阻塞在原地，-- 即只能读不能改了update employee set name="jason" where id=1;
步骤8	-- 注意：-- 需要在ctrl+c掉事务二的步骤7，或者等一会显示锁超时，再执行下述sqlupdate employee set name="jason" where id=1; -- 原因如下：-- 因为如果在事务二的步骤7阻塞的同时，也在事务一里执行了下述sql，-- 事务一会报死锁错误（详见5.3），事务一会因此结束，此时事务二原本-- 的阻塞状态会立即执行成功，为啥？？？-- 1、首先：为何会出现死锁？-- 因为事务二中，步骤7的update语句是想获取互斥锁，-- 会阻塞在原地，需要等待事务一先释放共享锁。-- 而事务一执行下述了下述update语句同样是想获取互斥锁，-- 同样需要等事务二先释放共享锁，至此双方互相锁死-- 2、然后，事务一在抛出死锁异常之后，会被强行终止，只剩事务二自己-- 因为不要有事务二以外的其他事务存在并对记录行加锁了-- 于是事务二的sql不存在锁争抢问题，会立即执行成功-- 此时，若想继续实验，则需要也rollback结束事务二，重新开启两个-- 事务并加共享锁来进行试验 -- 所以，根据上述分析，我们就先*ctrl+c掉事务二的步骤7**--* 然后执行上述sql，该sql语句默认会加排他锁，所以会阻塞在原地*-- 因为*-- 事务二已经对id<3的所有行都加了共享锁，id=1的行当然包含在内-- 所以在事务一中只能对id=1行加共享锁或者不加锁-- 即只能读不能改了
步骤9	-- 提交一下事务，不要影响下一次实验commit;	-- 提交一下事务，不要影响下一次实验commit;

意向锁

意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。

意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。
如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁

InnoDB中有两个意向锁（表锁）：

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

意向锁是InnoDB自动加的，不需要用户干预。

五 Innodb存储引擎的锁机制

mysql常用存储引擎的锁机制

MyISAM和MEMORY采用表级锁(table-level locking)

BDB采用页面锁(page-level locking)或表级锁，默认为页面锁

InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁（偏向于写）

InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：

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

innodb锁机制官网链接

5.1 行级锁与表级锁的使用区分

MyISAM 操作数据都是使用表级锁，MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。所以不会产生死锁，但是由于每操作一条记录就要锁定整个表，导致性能较低，并发不高。

InnoDB 与 MyISAM 的最大不同有两点：一是 InnoDB 支持事务；二是 InnoDB 采用了行级锁。也就是你需要修改哪行，就可以只锁定哪行。

在Mysql中，行级锁并不是直接锁记录，而是锁索引。InnoDB 行锁是通过给索引项加锁实现的，而索引分为主键索引和非主键索引两种

1、如果一条sql 语句操作了主键索引，Mysql 就会锁定这条语句命中的主键索引（或称聚簇索引）；

2、如果一条语句操作了非主键索引（或称辅助索引），MySQL会先锁定该非主键索引，再锁定相关的主键索引。

3、如果没有索引，InnoDB 会通过隐藏的聚簇索引来对记录加锁。也就是说：如果不通过索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表级锁一样。

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

• 1、在不通过索引条件查询的时候,InnoDB 的效果就相当于表锁
• 2、当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
• 3、由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以即便你的sql语句访问的是不同的记录行,但如果命中的是相同的被锁住的索引键，也还是会出现锁冲突的。
• 4、即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将锁住所有行，相当于表锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引，如下案例

=>案例<**=**

*准备表和数据，sql如下*

create table employee(
id int primary key auto_increment,
name varchar(20) not null,
age int(3) unsigned not null default 20
);

insert into employee(name) values
('egon'),
('alex'),
('wupeiqi'),
('yuanhao'),
('liwenzhou'),
('jingliyang'),
('jinxin'),
('成龙'),
('歪歪'),
('丫丫'),
('丁丁'),
('星星'),
('格格'),
('张野'),
('程咬金'),
('程咬银'),
('程咬铜'),
('程咬铁')
;
update employee set age = 16 where id=1;
update employee set age = 18 where id in (2,3,4);

步骤1、创建索引

mysql> select * from employee;
+----+------------+-----+
| id | name       | age |
+----+------------+-----+
|  1 | jason       |  16 |
|  2 | alex       |  18 |
|  3 | wupeiqi    |  18 |
|  4 | yuanhao    |  18 |
|  5 | liwenzhou  |  20 |
|  6 | jingliyang |  20 |
|  7 | jinxin     |  20 |
|  8 | 成龙       |  20 |
|  9 | 歪歪       |  20 |
| 10 | 丫丫       |  20 |
| 11 | 丁丁       |  20 |
| 12 | 星星       |  20 |
| 13 | 格格       |  20 |
| 14 | 张野       |  20 |
| 15 | 程咬金     |  20 |
| 16 | 程咬银     |  20 |
| 17 | 程咬铜     |  20 |
| 18 | 程咬铁     |  20 |
+----+------------+-----+
18 rows in set (0.00 sec)

mysql> explain select * from employee where age = 20;  -- age字段没有索引的情况下的查询计划，条件为age = 20

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | employee | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

mysql> explain select * from employee where age = 18;  -- age字段没有索引的情况下的查询计划，条件为age = 18

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | employee | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

 

mysql> create index xxx on employee(age);  -- 为age字段添加索引

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> explain select * from employee where age = 20;  -- age字段没有索引的情况下的查询计划，条件为age = 20

此时 满足age=20的行太多，即便是为age字段加了索引也是无法命中的，看下面的explain计划，key字段为NULL，证明虽然建立了索引，但压根没用上 
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | employee | ALL  | xxx           | NULL | NULL    | NULL |   18 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

 

mysql> explain select * from employee where age = 18;  -- -- age字段没有索引的情况下的查询计划，条件为age = 18

查看计划，key字段为xxx，命中了索引，因为age=18的行总共才3行，其实我们通常就应该给那些区分度高的字段加索引，否则加了也是白加，跟没加一个鸟样
+----+-------------+----------+------+---------------+------+---------+-------+------+-------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra |

+----+-------------+----------+------+---------------+------+---------+-------+------+-------+

|  1 | SIMPLE      | employee | ref  | xxx           | xxx  | 4       | const |    3 | NULL  |

+----+-------------+----------+------+---------------+------+---------+-------+------+-------+

1 row in set (0.00 sec)

*步骤2：验证未命中索引则锁表*

	事务一	事务二
步骤1	start transaction;	start transaction;
步骤2		-- 因为条件age=20无法命中索引，所以会锁住整张表select * from employee where age = 20 for update;
步骤3	-- 阻塞select * from employee where age = 16 for update; -- 阻塞select * from employee where age = 18 for update; -- 阻塞select * from employee where age = 20 for update;
步骤3	-- 提交一下事务，不要影响下一次实验commit;	-- 提交一下事务，不要影响下一次实验commit;

*步骤3：验证命中索引则锁行*

	事务一	事务二
步骤1	start transaction;	start transaction;
步骤2		-- 因为条件age=18命中了索引，所以会锁住行而不是表select * from employee where age = 18 for update;
步骤3	-- 不阻塞select * from employee where age = 16 for update; -- 阻塞，因为事务二里锁住了age=18的行select * from employee where age = 18 for update; -- 阻塞，？？？，不是说只锁age=18的行吗！！！-- 请看下一小节：Next-Key Lockselect * from employee where age = 20 for update;
步骤3	-- 提交一下事务，不要影响下一次实验commit;	-- 提交一下事务，不要影响下一次实验commit;

5.2 三种行锁的算法

InnoDB有三种行锁的算法，都属于排他锁：

• 1、Record Lock：单个行记录上的锁。
• 2、Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

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

# 例如
例：假如employee表中只有101条记录，其depid的值分别是 1,2,...,100,101，下面的SQL：
mysql> select * from emp where depid > 100 for update;是一个范围条件的检索，并且命中了索引，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁

• 3、Next-Key Lock：等于Record Lock结合Gap Lock，也就说Next-Key Lock既锁定记录本身也锁定一个范围，特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock。

对于行查询，**innodb采用的都是Next-Key Lock，主要目的是解决幻读的问题，以满足相关隔离级别以及恢复和复制的需要。****

案例：

# 准备数据
create table t1(

id int,

key idx_id(id)

)engine=innodb;

 

insert t1

values

(1),

(5),

(7),

(11);

 

mysql> explain select * from t1 where id=7 for update;  -- key字段为idx_id，命中索引，即会采用行锁而不是表锁

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra       |

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

|  1 | SIMPLE      | t1    | ref  | idx_id        | idx_id | 5       | const |    1 | Using index |

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

1 row in set (0.00 sec)

实验

	事务一	事务二
步骤1	start transaction;	-- 开启事务start transaction;
步骤2		-- 加排他锁select * from t1 where id=7 for update; -- 须知-- 1、上述语句命中了索引，所以加的是行锁-- 2、InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围（GAP）表记录的索引值为1，5，7，11，其记录的GAP区间如下：（-∞,1]，(1,5]，(5,7]，(7,11]，(11,+∞）因为记录行默认就是按照主键自增的，所以是一个左开右闭的区间其中上述查询条件id=7处于区间(5,7]中，所以Next-Key lock会锁定该区间的记录，但是还没完-- 3、InnoDB存储引擎还会对辅助索引下一个键值加上gap lock。区间(5,7]的下一个Gap是(7,11],所以(7,11]也会被锁定综上所述，最终确定5-11之间的值都会被锁定
步骤3	-- 下述sql全都会阻塞在原地insert t1 values(5);insert t1 values(6);insert t1 values(7);insert t1 values(8);insert t1 values(9);insert t1 values(10); -- 下述等sql均不会阻塞insert t1 values(11); insert t1 values(1); insert t1 values(2);insert t1 values(3);insert t1 values(4);
步骤4	-- 提交一下事务，不要影响下一次实验commit;	-- 提交一下事务，不要影响下一次实验commit;

插入超时失败后，会怎么样？

超时时间的参数：innodb_lock_wait_timeout ，默认是50秒。
超时是否回滚参数：innodb_rollback_on_timeout 默认是OFF。

section A:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select * from t1 where id=7 for update;
section B:
mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert t1 values(2);

Query OK, 1 row affected (0.00 sec)

 

mysql> 

mysql> insert t1 values(7);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  -- 抛出超时异常

mysql> select * from t1;  -- 超时异常并不会被回滚

+------+

| id   |

+------+

|    1 |

|    2 |

|    5 |

|    7 |

|   11 |

+------+

5 rows in set (0.00 sec)

经过测试，不会回滚超时引发的异常，当参数innodb_rollback_on_timeout 设置成ON时，则可以回滚，会把插进去的12回滚掉。

默认情况下，InnoDB存储引擎不会回滚超时引发的异常，死锁问题带来的超时异常除外。

既然InnoDB有三种算法，那Record Lock什么时候用？还是用上面的列子，把辅助索引改成唯一属性的索引。

测试二：

>create table t(a int primary key)engine =innodb;
Query OK, 0 rows affected (0.19 sec)

>insert into t values(1),(3),(5),(8),(11);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

>select * from t;
+----+
| a  |
+----+
|  1 |
|  3 |
|  5 |
|  8 |
| 11 |
+----+
5 rows in set (0.00 sec)

section A:
>start transaction;
Query OK, 0 rows affected (0.00 sec)

>select * from t where a = 8 for update;
+---+
| a |
+---+
| 8 |
+---+
1 row in set (0.00 sec)

section B:

>start transaction;
Query OK, 0 rows affected (0.00 sec)

>insert into t values(6);
Query OK, 1 row affected (0.00 sec)

>insert into t values(7);
Query OK, 1 row affected (0.00 sec)

>insert into t values(9);
Query OK, 1 row affected (0.00 sec)

>insert into t values(10);
Query OK, 1 row affected (0.00 sec)

问题：

为什么section B上面的插入语句可以正常，和测试一不一样？

分析：

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围，按照这个方法是会和第一次测试结果一样。但是，当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

注意：通过主键或则唯一索引来锁定不存在的值，也会产生GAP锁定。即：

会话1：
>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

>start transaction;

>select * from t where id = 15 for update;
Empty set (0.00 sec)

会话2：
>insert into t(id,name) values(10,'k');
Query OK, 1 row affected (0.01 sec)

>insert into t(id,name) values(12,'k');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
>insert into t(id,name) values(16,'kxx');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
>insert into t(id,name) values(160,'kxx');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted 

如何让测试一不阻塞？可以显式的关闭Gap Lock：

1：把事务隔离级别改成：Read Committed，提交读、不可重复读。SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2：修改参数：innodb_locks_unsafe_for_binlog 设置为1。

5.3 死锁问题

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

　　下面，来看看两个死锁的例子 (一个是两个Session的两条SQL产生死锁；另一个是两个Session的一条SQL，产生死锁)：

　　　　

　

　　　　

　　上面的两个死锁用例。第一个非常好理解，也是最常见的死锁，每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁。

　　第二个用例，只有多个事务同时运行的情况下才可能出现，但隐蔽性极强，虽然每个Session都只有一条语句，仍旧会产生死锁。要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。　　

create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50)
);


#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post) values
('egon','male',78,'20170301','老男孩驻沙河办事处外交大使'), #以下是教学部
('zxx','male',19,'20150302','teacher'),
('wupeiqi','male',81,'20130305','teacher'),
('yuanhao','male',73,'20140701','teacher'),
('liwenzhou','male',28,'20121101','teacher'),
('jingliyang','female',18,'20110211','teacher'),
('jinxin','male',18,'19000301','teacher'),
('成龙','male',48,'20101111','teacher'),

('歪歪','female',48,'20150311','sale'),#以下是销售部门
('丫丫','female',38,'20101101','sale'),
('丁丁','female',18,'20110312','sale'),
('星星','female',18,'20160513','sale'),
('格格','female',28,'20170127','sale'),

('张野','male',28,'20160311','operation'), #以下是运营部门
('程咬金','male',18,'19970312','operation'),
('程咬银','female',18,'20130311','operation'),
('程咬铜','male',18,'20150411','operation'),
('程咬铁','female',18,'20140512','operation')
;


create index idx_name on emp(name);
create index idx_age on emp(age);

事务1与事务2同时运行，会出现死锁
事务1
begin;
update emp set post="IT" where name="egon" or name="zxx";  -- 索引的有序性，'zxx'>'egon'，所以在辅助索引中zxx对应的记录在后
                                                           -- 即 ('egon',主键字段为1)，('zxx',主键id为2)，在锁完辅助索引后锁主键索引时，先锁主键1对应的记录再锁2

事务2

begin;
select * from emp where age=78 or age = 19 for update;  -- 原理同上，但在锁主键时，先锁主键2对应的记录，再锁1，如果事务1与事务2是同时运行的，则会互相锁住

结论：

# 1、关于死锁问题需要储备的知识
在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

# 2、死锁产生的本质原理死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

有多种方法可以避免死锁，

（1）如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
（2）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
（3）对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
（4）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

5.4 什么时候使用表锁

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

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

5.5 行锁优化建议

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况，在着手根据状态量来分析改善；

show status like 'innodb_row_lock%';//查看行锁的状态

• 尽可能让所有数据检索都通过索引来完成， 从而避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离，详见下一章节

六、乐观锁与悲观锁(使用方式)

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。其实不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念，像memcache、hibernate、tair等都有类似的概念。

针对于不同的业务场景，应该选用不同的并发控制方式。所以，不要把乐观并发控制和悲观并发控制狭义的理解为DBMS中的概念，更不要把他们和数据中提供的锁机制（行锁、表锁、排他锁、共享锁）混为一谈。其实，在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

下面来分别学习一下悲观锁和乐观锁。

6.1、悲观锁

悲观锁介绍

当我们要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。

这种借助数据库锁机制在修改数据之前先锁定，再修改的方式被称之为悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）。

在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作都某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。
悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制 （也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据），现在互联网高并发的架构中，受到fail-fast思路的影响，悲观锁已经非常少见了。

在数据库中，悲观锁的流程如下：

在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。

如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。

如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。

其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

ps：行锁、表锁、读锁、写锁都是在操作之前先上排他锁

在数据表中的实现

在MySQL中使用悲观锁，必须关闭MySQL的自动提交，set autocommit=0，因为MySQL默认使用自动提交autocommit模式，在执行完sql后会自动提交并释放锁

set autocommit=0;

举例

假设商品表中有一个字段quantity表示当前该商品的库存量。假设有一件Dulex套套，其id为100，quantity=8个；如果不使用锁，那么操作方法

如下：

//step1: 查出商品剩余量
 select quantity from items where id=100;
//step2: 如果剩余量大于0，则根据商品信息生成订单
 insert into orders(id,item_id) values(null,100);
 //step3: 修改商品的库存
 update Items set quantity=quantity-1 where id=100;

这样子的写法，在小作坊真的很正常，No Problems，但是在高并发环境下可能出现问题。

其实在step1或者step2环节，已经有人下单并且减完库存了，这个时候仍然去执行step3，就造成了超卖。

但是使用悲观锁，就可以解决这个问题，在上面的场景中，商品信息从查询出来到修改，中间有一个生成订单的过程，使用悲观锁的原理就是，当我们在查询出items信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为数据被锁定了，就不会出现有第三者来对其进行修改了。而这样做的前提是需要将要执行的SQL语句放在同一个事物中，否则达不到锁定数据行的目的。

如下

//step1: 查出商品状态
select quantity from items where id=100 for update;
//step2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);
//step3: 修改商品的库存
update Items set quantity=quantity-2 where id=100;

select...for update是MySQL提供的实现悲观锁的方式。此时在items表中，id为100的那条数据就被我们锁定了，其它的要执行select quantity from items where id=100 for update的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

总结

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

优点：

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

6.2、乐观锁

乐观锁介绍

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。乐观事务控制最早是由孔祥重（H.T.Kung）教授提出。

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

数据版本,为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

在数据库中，乐观锁的实现有两种方式

• 1、使用版本号实现

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

• 2、使用时间戳实现

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

乐观锁举例

1、需求：

在使用mysql数据库存储数据的前提下，有一个抢任务系统，一个任务只能分配给n个人，如果有高并发请求，如何保证数据完整性？

2、一般做法

在不考虑到数据是否完整的情况下，我们一般只会按照以下思维开发：
1、用户请求抢任务接口
2、读取数据库剩余数量
3、如果大于0，剩余数量减1，更新数据库剩余数量（update task set 4、4、count=count-1 where id=‘任务id’）
5、返回数据

3、出现的问题提以及乐观锁的应用

为什么上面的做法不对呢？我们举个例子，假设用户1和用户2同时调用请求抢任务接口，并且数据库只剩下一个任务可抢，任务剩余数量使用count字段保存；
用户1和用户2请求接口情况模拟，表格的每一行表示一个时间点

用户1	用户2
执行1	执行1
执行2	执行2
执行3（更新count为0）
	执行3（更新count为-1）
执行4	执行4

通过以上的问题，我们不难知道，本来只有一个任务可抢的，现在被两个用户同时抢了，而且数据库还出现了-1的情况，而这种情况再高并发的时候经常会遇到。

要解决高并发带来的问题，就可以利用乐观锁的概念来解决。
将上面中的第3个步骤中是sql语句改为（update task set count=count-1 where id=1 and count=1）
当然，其中的count=1中的1是步骤2读取出来的数据总数。
或者可以给表加一个版本号version字段，默认为1，每次执行更新的时候自增1，并在where语句后带上读取到的版本号，以免再读取和更新数据之间，有第三者更新了数据库。

用户1	用户2
执行1	执行1
执行2	执行2
执行3（更新count，dao层返回1，表示更新成功）
	执行3（更新count，dao层返回0，表示更新失败）
执行4	执行4

最后结果

1. 用户请求抢任务接口
2. 读取数据库剩余数量
3. 如果大于0，剩余数量减1，更新数据库剩余数量（update task set count=count-1 where id=‘任务id’ and count=‘读取到的剩余数量’）
4. 返回数据

以上SQL其实还是有一定的问题的，就是一旦发上高并发的时候，就只有一个线程可以修改成功，那么就会存在大量的失败。

对于像淘宝这样的电商网站，高并发是常有的事，总让用户感知到失败显然是不合理的。所以，还是要想办法减少乐观锁的粒度的。

有一条比较好的建议，可以减小乐观锁力度，最大程度的提升吞吐率，提高并发能力！如下：

//修改商品库存 update task set count=count-1 where id=‘任务id’ and count=‘读取到的剩余数量’ and count-1 >= 0;
 

以上SQL语句中，通过count-1>0的方式进行乐观锁控制，商品个数count至少要有1件才可以。

以上update语句，在执行过程中，会在一次原子操作中自己查询一遍count的值，并将其扣减掉1。

没错！你参加过的天猫、淘宝秒杀、聚划算，跑的就是上述这条SQL，通过挑选乐观锁，可以减小锁力度，从而提升吞吐～

乐观锁需要灵活运用

现在互联网高并发的架构中，受到fail-fast思路的影响，悲观锁已经非常少见了。

优点与不足

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。

如何选择

在乐观锁与悲观锁的选择上面，主要看下两者的区别以及适用场景就可以了。

1、乐观锁并未真正加锁，效率高。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。

2、悲观锁依赖数据库锁，效率低。更新失败的概率比较低。

随着互联网三高架构（高并发、高性能、高可用）的提出，悲观锁已经越来越少的被使用到生产环境中了，尤其是并发量比较大的业务场景。

标签：事务,加锁,--,索引,MySQL,机制,where,id
From： https://www.cnblogs.com/ycmyay/p/17382746.html