18@MySQL中的锁机制

文章目录

一、数据库的锁机制

1、什么是锁

锁是一种保障数据安全的机制
锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。
MySQL中为了保证数据访问的一致性与有效性等功能，实现了锁机制，
MySQL中的锁是在服务器层或者存储引擎层实现的

#锁是计算机协调多个进程或线程并发访问某一资源的机制，为什么要是用锁机制？
    1）并发的事务并发同时访问一个共享资源时，有可能访问数据不一致，数据无效的问题，在并发访问情况下，可能会出现脏读、不可重复读和幻读等读现象
    2）锁机制可以将并发的数据访问顺序化，以保证数据库中数据的一致性与有效性
    3）为了保护并发的写行为时数据安全的


#数据安全性的保障
    1）以互斥锁为例：多个任务在执行时需要抢到互斥锁才能运行，同一时间只有一个任务可以拿到，其它都需要等待，即互斥锁保障多个任务同一时间只有一个在运行，保证数据的一致性

2、并发控制

并发控制（Concurrency control）是确保及时纠正由并发操作导致的错误的一种机制

#并发控制的实现
     1）数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性
     2）封锁、时间戳、乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段



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

二、锁的分类

1）按锁的粒度划分：行级锁、表级锁、页级锁（mysql支持）
2）按锁级别划分：共享锁、排他锁
3）按使用方式划分：乐观锁、悲观锁
4）按加锁方式划分：自动锁、显式锁
5）按操作划分：DML锁、DDL锁

【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）

三、行级锁、表级锁、页级锁（粒度划分）

数据库管理系统（DBMS）按照粒度把数据库锁划分：
行级锁(INNODB引擎)、表级锁(MYISAM引擎)、页级锁(BDB引擎 )

引擎对锁的支持情况：

1、【行级锁】

行级锁是Mysql中锁定粒度最细的一种锁,其加锁粒度最小，但加锁的开销也最大,发生锁冲突的概率最低，并发度也最高

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


#行级锁的特点：
   开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高


#支持引擎：InnoDB
   行级锁定分为行共享读锁（共享锁）与行独占写锁（排他锁）

【行级锁的分类】

1）共享锁（S）：加了锁的记录，所有事务都能去读取但不能修改，同时阻止其他事务获得相同数据集的排他锁；
2）排他锁（X）：允许已经获得排他锁的事务去更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁；


#共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
         示列：  select * from 表 where id = 3 or id=5 lock in share mode;




#排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE
         示列：   insert、update、delete写操作自带排他锁
			     select * from 表 where id = 3 or id=5 for update;

【行级锁示意图】

在这里插入图片描述

【案列】（行级锁）

SQL的执行为例，讲解一下InnoDB对于单行数据的加锁原理：

#示列：
  update user set age = 10 where id = 49;
  update user set age = 10 where name = 'Tom';


#sql注解：
第一条SQL使用主键查询，只需要在 id = 49 这个主键索引上加上锁。
第二条 SQL 使用二级索引来查询，那么首先在 name = Tom 这个索引上加写锁，然后由于使用 InnoDB 二级索引还需再次根据主键索引查询，所以还需要在 id = 49 这个主键索引上加锁。

sql :update user set age = 10 where id > 49;

sql注解：mysql Server 会根据 WHERE 条件读取第一条满足条件的记录，然后 InnoDB 引擎会将第一条记录返回并加锁，接着 MySQL Server 发起更新改行记录的 UPDATE 请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有匹配的记录为止

2、【表及锁】（偏向于读）

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持
开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低

最常使用的MYISAM与INNODB都支持表级锁定

表级锁定分为表共享读锁（共享锁S）与表独占写锁（排他锁X）

#表级锁的特点：
    开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。



#支持引擎：
    MyISAM、MEMORY、InNoDB

【表级锁的分类】

表级锁也是意向锁，分为读意向锁（IS锁）和写意向锁（IX锁）
意向锁之间是不会产生冲突的，它只会阻塞表级读锁或写锁。意向锁不于行级锁发生冲突
表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

锁之间的兼容性：

（注意上面的X与S是说表级的X锁和S锁，意向锁不和行级锁发生冲突）

【表级锁示意图】

#表级锁概述：
    1)表锁使用的是一次性锁技术，也就是说，在会话开始的地方使用 lock 命令将后续需要用到的表都加上锁，在表释放前，只能访问这些加锁的表，不能访问其他表，直到最后通过 unlock tables 释放所有表锁
    2）除了使用 unlock tables 显示释放锁之外，会话持有其他表锁时执行lock table 语句会释放会话之前持有的锁；会话持有其他表锁时执行 start transaction 或者 begin 开启事务时，也会释放之前持有的锁
    3）表锁由 MySQL Server 实现，行锁则是存储引擎实现，不同的引擎实现的不同。在 MySQL 的常用引擎中 InnoDB 支持行锁，而 MyISAM 则只能使用 MySQL Server 提供的表锁

在这里插入图片描述

【案列】（表级锁）

#格式l：ock 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>

3、【页及锁】

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

BDB支持页级锁

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

4、【行级锁与表及锁之间的比较】

#表锁：加锁过程的开销小，加锁的速度快；不会出现死锁的情况；锁定的粒度大，发生锁冲突的几率大，~~并发度低~~ ；
     一般在执行DDL语句时会对整个表进行加锁，比如说 ALTER TABLE 等操作；
     如果对InnoDB的表使用行锁，被锁定字段不是主键，也没有针对它建立索引的话，那么将会锁整张表；
     表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用。


#行锁：加锁过程的开销大，加锁的速度慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，~~并发度也最高~~ ；
     最大程度的支持并发，同时也带来了最大的锁开销。
     在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
     行级锁只在存储引擎层实现，而 MySQL 服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

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

共享锁
排他锁

1、行操作的相关sql语句

#对于写操作：
    insert、update、delete  ------> sql语句InnoDB 会自动给涉及的数据加锁，而且是排他锁


#对于读操作：
    select -----> 普通的sql语句，InnoDB不会加任何锁，需要我们手动自己加，可以加两种类型的锁
   1)共享锁（S）：SELECT ... LOCK IN SHARE MODE;    --# 查出的记录行都会被锁住
   2)排他锁（X)：SELECT ... FOR UPDATE;            --# 查出的记录行都会被锁住

2、innodb的加锁方法

意向锁是 InnoDB 自动加的，不需要用户干预；
1）对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加上排他锁；
2）对于普通的SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集添加共享锁或排他锁：
        共享锁（S）：select * from table_name where ... lock in share mode  此时其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
        排他锁（X）：select * from table_name where ... for update   其他session可以查询记录，但是不能对该记录加共享锁或排他锁，只能等待锁释放后在加锁





####################################### 两种加锁方式 ################################
1)#select **for update**  （添加排他锁）
在执行这个 select 查询语句的时候，会将对应的索引访问条目加上排他锁（X锁），也就是说这个语句对应的锁就相当于update带来的效果；

#*使用场景*：
    为了让确保自己查找到的数据一定是最新数据，并且查找到后的数据值允许自己来修改，此时就需要用到select for update语句；

#*性能分析*：
    select for update语句相当于一个update语句。在业务繁忙的情况下，如果事务没有及时地commit或者rollback可能会造成事务长时间的等待，从而影响数据库的并发使用效率。






2)#select lock **in share mode** （添加共享锁）
in share mode 子句的作用就是将查找的数据加上一个share锁，这个就是表示其他的事务只能对这些数据进行简单的 select 操作，而不能进行 DML 操作

#*使用场景*：
    为了确保自己查询的数据不会被其他事务正在修改，也就是确保自己查询到的数据是最新的数据，并且不允许其他事务来修改数据。与select for update不同的是，本事务在查找完之后不一定能去更新数据，因为有可能其他事务也对同数据集使用了 in share mode 的方式加上了S锁；

#*性能分析*：
    select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待

3、案列

#数据表准备：
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是默认加排他锁的
############################(事务一)#############################
#步骤一：
start transaction;
select name from employee where id = 1;
#步骤三：
-- 此处的update会阻塞在原地，因为事务二并未提交事务，即尚未释放排他锁
update employee set name = concat(name,"_NB") where id = 1;，
#步骤五：
 -- 此处查询到的结果为EGON_NB
select name from employee where id = 1;
#步骤六：
-- 提交之后，name持久化为EGON_NB
commit;
select name from employee where id = 1;

###########################(事务二)###############################
#步骤二：
-- 把小写的egon变为大写，此时的update会自动加锁
update employee set name = "EGON" where id = 1;
立即查看修改结果，name变为EGON，但此时还没有commit
select name from employee where id = 1;
#步骤四：
-- 事务二一旦提交，事务一阻塞在步骤3的操作即会运行成功
commit;
-- 查看修改结果，name变为大写的EGON
select name from employee where id = 1;

4、共享锁与排他锁

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

【共享锁】(Share Lock)

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，获准共享锁的事务只能读数据，不能修改数据直到已释放所有共享锁，所以共享锁可以支持并发读
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁或不加锁（在其他事务里一定不能再加排他锁，但是在事务T自己里面是可以加的）

【共享锁的使用方法】

#语法：SELECT ... LOCK IN SHARE MODE;

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

【排他锁】

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

【排他锁的使用】

#格式： SELECT ... FOR UPDATE;

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

5、意向锁

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

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



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

五、innodb存储引擎的锁机制

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

1、【innodb的锁模式分类】

InnoDB的锁定模式实际上可以分为四种：
共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX）

2、【行锁的三种算法】（Record Lock、Gap Lock、Next-Key Lock）

innodb行锁的三种算法：（属于排他锁）

Record Lock：适用于单行记录上的锁
Gap Lock：间隙锁
Next-Key Lock：临键锁，Record Lock结合Gap Lock，Next-Key Lock既锁定记录本身也锁定一个范围，注意，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock
Insert Intention Lock：插入意向锁

【算法之间的关系】

在这里插入图片描述

#算法锁的详解：
#记录锁（Record Lock）
记录锁最简单的一种行锁形式，补充下：行锁是加在索引上的，如果当你的查询语句不走索引的话，那么它就会升级到表锁，最终造成效率低下，所以在写SQL语句时需要特别注意



#间隙锁（Gap Lock）
 1）间隙锁是所在两个存在的索引之间，是一个开区间，像最开始的那张索引图，15和18之间，是有（16，17）这个间隙存在的
 2）当我们使用范围条件而不是相等条件去检索，并请求锁时，InnoDB就会给符合条件的记录的索引项加上锁；而对于键值在条件范围内但并不存在（参考上面所说的空闲块）的记录，就叫做间隙
 3）间隙锁是可以共存的，共享间隙锁与独占间隙锁之间是没有区别的，两者之间并不冲突。其存在的目的都是防止其他事务往间隙中插入新的纪录，故而一个事务所采取的间隙锁是不会去阻止另外一个事务在同一个间隙中加锁的




#临键锁（Next-key Lock）
 1）临键锁是记录锁与与间隙锁的结合，所以临键锁与间隙锁是一个同时存在的概念，并且临键锁是个左开有闭的却比如(16, 18]
 2）MySQL 默认隔离级别是RR，在这种级别下，如果你使用 select in share mode 或者 select for update 语句，那么InnoDB会使用临键锁（记录锁 + 间隙锁），因而可以防止幻读




#插入意向锁（Insert Intention Lock）
 1）插入意图锁是一种间隙锁，在行执行 INSERT 之前的插入操作设置。如果多个事务 INSERT 到同一个索引间隙之间，但没有在同一位置上插入，则不会产生任何的冲突。假设有值为4和7的索引记录，现在有两事务分别尝试插入值为 5 和 6 的记录，在获得插入行的排他锁之前，都使用插入意向锁锁住 4 和 7 之间的间隙，但两者之间并不会相互阻塞，因为这两行并不冲突。
 2）插入意向锁只会和 间隙或者 Next-key 锁冲突，正如上面所说，间隙锁作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行

【不同类型锁之间的兼容】

在这里插入图片描述

【间隙锁实列】

# 准备数据
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)


#实验步骤
###################### (事务一) #######################
步骤一：
start transaction;
步骤四:
-- 下述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);   
步骤五：
-- 提交一下事务，不要影响下一次实验
commit;




###################### (事务二) #######################
步骤二：
-- 开启事务
start transaction;

步骤三:
-- 加排他锁
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之间的值都会被锁定
步骤六:
-- 提交一下事务，不要影响下一次实验
commit;



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

3、【死锁】

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

第一个案例：每个事务执行两条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）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能

4、【表锁的使用】

1》事务需要更新大部分数据，表又较大，
   若使用默认的行锁,不仅该事务执行效率低(因为需要对较多行加锁,加锁是需要耗时的); 而且可能造成其他事务长时间锁等待和锁冲突; 这种情况下可以考虑使用表锁来提高该事务的执行速度


2》事务涉及多个表，较复杂，很可能引起死锁，造成大量事务回滚
   这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ

5、【行锁优化】

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

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

六、乐观锁与悲观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性
乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段

1、悲观锁

【悲观锁介绍】

当我们要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发,这种借助数据库锁机制在修改数据之前先锁定，再修改的方式被称之为悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）

#悲观锁简述：
1）在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作都某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。

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


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

【悲观锁的基本流程】

#使用基本流程
1) 在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。

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

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

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



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







#悲观锁数据表中的实现
MySQL使用悲观锁，开启显示提交，必须先关闭MySQL的自动提交功能
set autocommit=0;        #MySQL默认使用自动提交autocommit模式，在执行完sql后会自动提交并释放锁

【悲观锁案列】

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

#步骤分析一
1） #查出商品剩余量
 select quantity from items where id=100;

2）#如果剩余量大于0，则根据商品信息生成订单
 insert into orders(id,item_id) values(null,100);

3）# 修改商品的库存
 update Items set quantity=quantity-1 where id=100;

在这里插入图片描述

1》对于以上写法，在小作坊真的很正常，No Problems，但是在高并发环境下可能出现问题
2》其实在step1或者step2环节，已经有人下单并且减完库存了，这个时候仍然去执行step3，就造成了超卖。
3》但是使用悲观锁，就可以解决这个问题，在上面的场景中，商品信息从查询出来到修改，中间有一个生成订单的过程，使用悲观锁的原理就是，当我们在查询出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的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改

【悲观锁的优缺点】

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

1》#优点：
   悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。



2》#缺点：
  （a）在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；
  （b）在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

2、乐观锁

【乐观锁介绍】

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

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

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






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

【乐观锁的实现】

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

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

【乐观锁案列】

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

#分析步骤一：
1》用户请求抢任务接口
2》读取数据库剩余数量
3》如果大于0，剩余数量减1，更新数据库剩余数量（update task set count=count-1 where id=‘任务id’）
4》返回数据

乐观锁的应用：
假设用户1和用户2同时调用请求抢任务接口，并且数据库只剩下一个任务可抢，任务剩余数量使用count字段保存；
用户1和用户2请求接口情况模拟，表格的每一行表示一个时间点

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

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

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


ps : 以上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)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁

3、悲观锁于乐观锁的选择

根据适应场景选择

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

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





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

七、多版本并发控制

【MVCC并发控制】

posted @ 2021-07-16 21:18 ଲ一笑奈&何阅读(143) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

博客一笑奈何

一笑奈何博客

18@MySQL中的锁机制

文章目录

一、数据库的锁机制

1、什么是锁

2、并发控制

二、锁的分类

【DML锁与DDL锁】

三、行级锁、表级锁、页级锁（粒度划分）

1、【行级锁】

【行级锁的分类】

【行级锁示意图】

【案列】（行级锁）

2、【表及锁】 （偏向于读）

【表级锁的分类】

【表级锁示意图】

【案列】（表级锁）

3、【页及锁】

4、【行级锁与表及锁之间的比较】

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

1、行操作的相关sql语句

2、innodb的加锁方法

3、案列

4、共享锁与排他锁

【共享锁】(Share Lock)

【共享锁的使用方法】

【排他锁】

【排他锁的使用】

5、意向锁

五、innodb存储引擎的锁机制

1、【innodb的锁模式分类】

2、 【行锁的三种算法】（Record Lock、Gap Lock、Next-Key Lock）

【算法之间的关系】

【不同类型锁之间的兼容】

【间隙锁实列】

3、【死锁】

【死锁的示列】

【死锁总结】

4、【表锁的使用】

5、【行锁优化】

六、乐观锁与悲观锁

1、悲观锁

【悲观锁介绍】

【悲观锁的基本流程】

【悲观锁案列】

【悲观锁的优缺点】

2、乐观锁

【乐观锁介绍】

【乐观锁的实现】

【乐观锁案列】

【乐观锁的优缺点】

3、悲观锁于乐观锁的选择

七、多版本并发控制

公告

2、【表及锁】（偏向于读）

2、【行锁的三种算法】（Record Lock、Gap Lock、Next-Key Lock）