电子商务网站建设与管理课程评价,郑州房产网,重庆綦江网站制作公司电话,中国十大做网站公司文章目录 1.行级锁的概念2.行锁的概念以及基本使用2.1.行锁的概念2.2.常见的SQL语句所对应的行锁类别2.3.行锁的基本使用 3.间隙锁和临键锁的概念以及基本使用3.1.间隙锁和临键锁的概念3.2.间隙锁和临键锁的基本使用 1.行级锁的概念
行级锁指的是#xff0c;每次操作锁住的是… 文章目录 1.行级锁的概念2.行锁的概念以及基本使用2.1.行锁的概念2.2.常见的SQL语句所对应的行锁类别2.3.行锁的基本使用 3.间隙锁和临键锁的概念以及基本使用3.1.间隙锁和临键锁的概念3.2.间隙锁和临键锁的基本使用 1.行级锁的概念
行级锁指的是每次操作锁住的是表中一行的数据锁的力度最小发生锁冲突的概率也是最低的并且并发度也是最高的行级锁主要应用于InnoDB存储引擎中。
在InnoDB存储引擎中表中每一行数据都是基于索引组织的在索引中分为聚集索引和二级索引聚集索引的叶子节点挂的是表中的一行数据二级索引的叶子节点挂的是表中每条数据的主键因此在InnoDB存储引擎中表中的数据都是基于索引组织存放的并且基于索引组织存放后叶子节点之间是有序存储的还形成了双向链表。
行级锁是通过索引结构中每行数据对应的索引项来实现的索引项也就是索引元素、聚集索引中的叶子节点主键每个叶子节点都对应表中的每一行数据因为行级锁是通过表中索引结构的索引元素来添加行数锁的并非是对表中的每条记录添加的行级锁
对于行级锁主要分为以下三类
行锁
顾名思义就是锁定单行记录的锁主要是为了防止当前事务对这条数据处理时其他的事务也对这条数据进行处理在RC、RR隔离级别下都支持行锁。 间隙锁
间隙锁指的是锁住两个叶子节点之间间隙部分的索引元素但是不锁定对应的记录只是要确保索引元素之间的间隙不发生改变以免其他事务通过索引元素之间的间隙元素写入数据产生幻读的现象。在RR隔离级别下支持。
如下图所示只是为了锁住两个叶子节点间的索引元素间隙例如29和34两个索引元素只是为了锁定29-34之间的间隙防止新数据通过29-34之间的一个索引元素写入数据从而导致幻读的现象。 临键锁
临键锁指的是将行锁与间隙锁组合锁定既锁定数据又要锁定住叶子节点之间的间隙元素在RR隔离级别下支持。 2.行锁的概念以及基本使用
2.1.行锁的概念
在行锁中还会分为两种类型的行锁一种是共享锁S另外一种是排它锁X。
默认的情况下InnoDB在RR事务隔离级别运行行锁通过next-key锁进行搜索和索引扫描防止幻读。
当我们针对唯一索引的字段进行检索时对已存在的记录进行匹配将会优化为行锁。
InnoDB的行锁是针对索引加的锁因此当不通过索引字段检索数据时默认会将表加上一个表锁。
1共享锁
在共享锁中允许多个事务读取读取相同数据集的共享锁语句但是拒绝排它锁处理与共享锁相同的数据集。
例如事务A查询表中小明人员的年龄产生了行共享锁此时事务B也查询小明人员的年龄产生共享锁共享锁之间互相兼容因此两个事务同时读取相同数据集的内容是允许的没有任何问题。
但是当事务A查询表中小明人员的年龄时事务B此时要修改小明人员的信息修改操作会产生排它锁事务A正在读取这条数据事务B却要修改此时会出现行锁冲突使事务B的修改语句一直处于阻塞状态。只有当事务A针对这条数据处理完提交事务后事务B才能成功执行对应的修改SQL。
结论共享锁与共享锁之间相互兼容但是共享锁与排它锁之间互斥会产生冲突此时只有当共享锁释放后排它锁才能执行。
2排它锁
在排它锁中不允许事务读取相同数据集的共享锁和排它锁。
例如事务A正在修改姓名为小明的数据此时事务B也要修改或者查询姓名为小明的数据此时无论是修改或者查询都会处于阻塞状态因为事务A正在操作这条数据并且产生了排它锁而事务B的修改也会产生排它锁、查询会产生共享锁无论排它锁还是共享锁都被事务A的排它锁互斥从而产生行锁冲突。
结论排它锁与共享锁和其他的排它锁都互斥会导致行锁冲突对于一条数据只有当排它锁释放之后才能对这条数据进行处理。
2.2.常见的SQL语句所对应的行锁类别
SQL行锁类型说明INSERT排它锁自动加锁UPDATE排它锁自动加锁DELETE排它锁自动加锁SELECT不加任何锁SELECT…LOCK IN SHARE MODE共享锁手动在SELECT之后添加LOCK IN SHARE MODESELECT…FOR UPDATE排它锁手动在SELECT之后添加FOR UPDATE
2.3.行锁的基本使用
1普通的select查询不会加任何的锁此处略过没有效果
2验证共享锁与共享锁之间的兼容
首先开启一个事务创建一个共享锁然后在其他客户端也执行相同的查询共享锁观察效果。
1.首先开启一个事务
mysql begin;2.创建一个行共享锁
mysql select * from xscjb limit 1 lock in share mode;
------------------------------------
| xh | xm | ywcj | sxcj | yycj | pjcj |
------------------------------------
| 1 | 小明 | 66 | 75 | 93 | NULL |
------------------------------------3.查看行共享锁
mysql select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
-------------------------------------------------------------------------
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
-------------------------------------------------------------------------
| db_1 | xscjb | NULL | TABLE | IS | NULL |
| db_1 | xscjb | PRIMARY | RECORD | S | 1 |
-------------------------------------------------------------------------
#lock_mode为S时就表示是行共享锁4.其他客户端也执行相同的查询语句没有任何影响
mysql select * from xscjb limit 1 lock in share mode;
------------------------------------
| xh | xm | ywcj | sxcj | yycj | pjcj |
------------------------------------
| 1 | 小明 | 66 | 75 | 93 | NULL |
------------------------------------3验证共享锁与排他锁之间的兼容
首先开启一个事务查询xscjb中xh为1的数据不提交然后再开启一个事务更改id为2的数据观察效果紧接着也更改id为1的数据。
1.首先开启一个事务
mysql begin;2.创建一个行共享锁查询xh为1的数据
mysql select * from xscjb where xh 1 lock in share mode;
------------------------------------
| xh | xm | ywcj | sxcj | yycj | pjcj |
------------------------------------
| 1 | 小明 | 66 | 75 | 93 | NULL |
------------------------------------
1 row in set (0.00 sec)3.查看行共享锁
mysql select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
-----------------------------------------------------------------------------
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
-----------------------------------------------------------------------------
| db_1 | xscjb | NULL | TABLE | IS | NULL |
| db_1 | xscjb | PRIMARY | RECORD | S,REC_NOT_GAP | 1 |
-----------------------------------------------------------------------------
#lock_mode为S时就表示是行共享锁4.再开启一个事务修改xh为2的数据正常修改
mysql update xscjb set ywcj 100 where xh 2;5.紧接着修改与第一个事务相同数据集的数据会一直处于阻塞状态
mysql update xscjb set ywcj 100 where xh 1;#只有当第一个事务提交完毕后才能对相同的数据集进行处理4验证排它锁与排他锁之间的冲突
首先打开第一个事务修改xh为1的数据产生排它锁然后再打开另一个事务也修改xh为1的数据验证排它锁与排它锁之间的互斥。
1.首先开启一个事务
mysql begin;2.修改xh为1的数据
mysql update xscjb set ywcj 100 where xh 1;3.查看产生的排它锁
mysql select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
-----------------------------------------------------------------------------
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
-----------------------------------------------------------------------------
| db_1 | xscjb_logs | NULL | TABLE | IX | NULL |
| db_1 | xscjb | NULL | TABLE | IX | NULL |
| db_1 | xscjb | PRIMARY | RECORD | X,REC_NOT_GAP | 1 |
-----------------------------------------------------------------------------
#lock_mode为X表示排它锁4.再开启一个事务同样修改xh为1的数据
mysql update xscjb set ywcj 100 where xh 1;#会一直处于阻塞状态只有当第一个事务提交完成后这个操作才能被执行。5当检索的字段没有索引时字段添加表锁
xscjb表中xm字段是没有索引的下面我们在SQL语句中就使用这个没有添加索引的字段作为检索条件观察会有什么效果。
1.开启事务A修改xm为小黑的数据
mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql update xscjb set ywcj 100 where xm 小黑;2.开启事务B修改小李的数据
mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql update xscjb set ywcj 100 where xm 小李;此时会发现小黑的数据修改成功了但是小李的数据却不能修改并且即使是有排它锁只要不是相同的数据集就可以修改那么目前是不能被修改的这样就说明了当检索的字段不是索引字段时那么行锁就会升级为表锁只有当事务A提交后事务B才能对表中的行进行处理更验证了行锁是根据索引去添加的。 我们只需要将xm字段添加一个索引就不会导致行锁升级为表锁了。
3.间隙锁和临键锁的概念以及基本使用
3.1.间隙锁和临键锁的概念
间隙锁是锁的索引结构中两个叶子节点之间间隙的索引元素只是锁住间隙不锁住数据。
例如两个叶子节点分别是7和10间隙锁就会锁住7-10之间的间隙此时插入数据时就不能使用7-10之间的 值作为主键值避免间隙被使用。
间隙锁唯一的目的就是防止其他的事务将数据插入到两个叶子节点之间的间隙位置间隙可以共存一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用的间隙锁。也就是说多个事务可以同时开启相同的间隙锁。
为什么要用间隙锁和临键锁呢因为在查询、修改某个范围的数据时如果没有间隙锁那么其他事务就可能会在这一段间隙内写入数据从而影响当前事务提交数据的准确性因为我们要锁定这个范围内的间隙已经对应的数据。
3.2.间隙锁和临键锁的基本使用
1使用唯一索引列进行等值检索时如果给不存在的记录添加锁后会被设置成间隙锁。 开启一个事务A修改xscjb中的一条数据但是修改的数据在表中不存在观察产生的间隙锁。 1.开启事务A修改表中不存在的数据
mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql update xscjb set ywcj 120 where xh 10;
#当修改的数据时表中不存在的数据会自动将这个主键范围内设置一个间隙锁2.查看产生的间隙排它锁
mysql select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
-------------------------------------------------------------------------
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
-------------------------------------------------------------------------
| db_1 | xscjb | NULL | TABLE | IX | NULL |
| db_1 | xscjb | PRIMARY | RECORD | X,GAP | 13 |
-------------------------------------------------------------------------
#lock_mode为GAP表示是间隙锁
#lock_data这里的13表示锁住的间隙最大是13最小是从距离13最近的范围也就是88-13之间产生了间隙锁3.此时开启事务B写入主键为11的数据将会阻塞
mysql insert into xscjb values (11,小笑,11,11,11,NULL);首先我们来查询一下xscjb表的数据可以看到主键8-13这个范围内没有其他数据此时我们开启一个事务A来修改主键为10的数据主键为10的数据不存在并处于8-13的间隙内此时就会产生一个间隙锁间隙锁的范围就是8-13可以从lock_data这里看到最大的间隙范围值对应的最小间隙范围值就是距离13最近的一个值也就是8-13这时开启了事务B事务B想要插入xh为11的数据则会被阻塞因为xh为11这个值处于8-13的间隙内因此会被阻塞当事务A提交后阻塞才会被释放。 2使用普通索引非唯一索引进行等值检索一直向后遍历直到直到不满足检索条件的数据此时既会产生间隙锁又会产生临键锁。
我们的条件是对非唯一索引字段进行检索那么一次检索中可能就会出现多条相同的值例如我们检索条件是nl18那么表中可能有很多条nl18的数据查询nl18的数据时会加上共享锁但是我们不能只锁住这一行数据因为还会有很多条记录存在因此还需要接着往后面遍历直到找到一个不满足条件值例如检索到了nl26的数据此时后面就没有nl18的数据了那么此时会对所有nl18的数据添加临键锁并且还会对nl18~26之前的所有间隙添加间隙锁保证数据获取的一致性。
首先为xscjb表中的ywcj字段添加一个普通索引。
mysql create index idx_xscjb_ywcj on xscjb(ywcj);使用普通索引作为检索条件观察添加的间隙锁和临键锁。
mysql select * from xscjb where ywcj 88 lock in share mode;
------------------------------------
| xh | xm | ywcj | sxcj | yycj | pjcj |
------------------------------------
| 8 | 小李 | 88 | 77 | 66 | NULL |
| 13 | 小赵 | 88 | 77 | 66 | NULL |
| 18 | 小王 | 88 | 77 | 66 | NULL |
| 25 | 小江 | 88 | 77 | 66 | NULL |
------------------------------------mysql select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
---------------------------------------------------------------------------------
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
---------------------------------------------------------------------------------
| db_1 | xscjb | NULL | TABLE | IS | NULL |
| db_1 | xscjb | idx_xscjb_ywcj | RECORD | S | 88, 8 |
| db_1 | xscjb | idx_xscjb_ywcj | RECORD | S | 88, 13 |
| db_1 | xscjb | idx_xscjb_ywcj | RECORD | S | 88, 18 |
| db_1 | xscjb | idx_xscjb_ywcj | RECORD | S | 88, 25 |
| db_1 | xscjb | PRIMARY | RECORD | S,REC_NOT_GAP | 25 |
| db_1 | xscjb | PRIMARY | RECORD | S,REC_NOT_GAP | 18 |
| db_1 | xscjb | PRIMARY | RECORD | S,REC_NOT_GAP | 13 |
| db_1 | xscjb | PRIMARY | RECORD | S,REC_NOT_GAP | 8 |
| db_1 | xscjb | idx_xscjb_ywcj | RECORD | S,GAP | 100, 1 |
---------------------------------------------------------------------------------
10 rows in set (0.00 sec)可以看到使用普通索引查询出多条ywcj为88的数据此时对于所有查询出来88的数据都会添加一个临键锁并且也会对88-100之间的间隙设置一个间隙锁。此时ywcj为88的数据也不允许被排它锁进行修改88~100之间的间隙也不允许写入数据。 3使用唯一索引检索一个范围观察加的锁。
我们使用唯一索引字段去检索一个范围查询观察加的锁。
1.开启事务检索唯一索引的范围查询
mysql begin;mysql select * from xscjb where xh 15 lock in share mode;
------------------------------------
| xh | xm | ywcj | sxcj | yycj | pjcj |
------------------------------------
| 18 | 小王 | 88 | 77 | 66 | NULL |
| 19 | 小黑 | 100 | 77 | 66 | NULL |
| 25 | 小江 | 88 | 77 | 66 | NULL |
------------------------------------2.观察添加了什么锁
mysql select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
--------------------------------------------------------------------------------------
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
--------------------------------------------------------------------------------------
| db_1 | xscjb | NULL | TABLE | IS | NULL |
| db_1 | xscjb | PRIMARY | RECORD | S | supremum pseudo-record |
| db_1 | xscjb | PRIMARY | RECORD | S | 25 |
| db_1 | xscjb | PRIMARY | RECORD | S | 19 |
| db_1 | xscjb | PRIMARY | RECORD | S | 18 |
--------------------------------------------------------------------------------------当使用唯一索引进行范围检索时会对检索出来的数据都加一个临键锁不允许被其他事务处理保证数据一致性并且并不知道每次检索会出来多少条数据因此还有一个supremum pseudo-record标志也是临键锁表示正无穷大无论检索出来多少条数据只要是这个范围的数据都会添加临键锁防止其他事务修改数据。
