如何使用阿里云建设网站,做视频网站的公司,企业滴滴app下载,磁力搜索引擎哪个好原文链接#xff1a;https://blog.csdn.net/qq_38238296/article/details/88362999
文章目录
MySQL的锁机制和加锁原理1.行锁2.表锁3.页锁4.乐观锁和悲观锁4.1悲观锁4.2乐观锁5.1InnoDB锁的特性 6.Record Lock、Gap Lock、Next-key Lock锁6.1.Record Lock6.2.Gap Lock6.2.…原文链接https://blog.csdn.net/qq_38238296/article/details/88362999
文章目录
MySQL的锁机制和加锁原理1.行锁2.表锁3.页锁4.乐观锁和悲观锁4.1悲观锁4.2乐观锁5.1InnoDB锁的特性 6.Record Lock、Gap Lock、Next-key Lock锁6.1.Record Lock6.2.Gap Lock6.2.1 什么叫间隙锁6.2.2 为什么说gap锁是RR隔离级别下防止幻读的主要原因。6.2.3. 主键索引/唯一索引当前读会加上Gap锁吗6.2.4通过范围查询是否会加上Gap锁6.2.5 检索条件并不存在的当前读会加上Gap吗 6.3.Next-Key Lock7.死锁的原理及分析7.1. MVCC7.2. 2PLTwo-Phase Locking7.3 为什么会发生死锁 首先对mysql锁进行划分 按照锁的粒度划分行锁、表锁、页锁按照锁的使用方式划分共享锁、排它锁悲观锁的一种实现还有两种思想上的锁悲观锁、乐观锁。InnoDB中有几种行级锁类型Record Lock、Gap Lock、Next-key LockRecord Lock在索引记录上加锁Gap Lock间隙锁Next-key LockRecord LockGap Lock
1.行锁
行级锁是Mysql中锁定粒度最细的一种锁表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小但加锁的开销也最大。有可能会出现死锁的情况。 行级锁按照使用方式分为共享锁和排他锁。
共享锁用法S锁 读锁
若事务T对数据对象A加上S锁则事务T可以读A但不能修改A其他事务只能再对A加S锁而不能加X锁直到T释放A上的S锁。这保证了其他事务可以读A但在T释放A上的S锁之前不能对A做任何修改。
select ... lock in share mode;
1
共享锁就是允许多个线程同时获取一个锁一个锁可以同时被多个线程拥有。
排它锁用法X 锁 写锁
若事务T对数据对象A加上X锁事务T可以读A也可以修改A其他事务不能再对A加任何锁直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
select ... for update
1
排它锁也称作独占锁一个锁在某一时刻只能被一个线程占有其它线程必须等待锁被释放之后才可能获取到锁。
2.表锁
表级锁是mysql锁中粒度最大的一种锁表示当前的操作对整张表加锁资源开销比行锁少不会出现死锁的情况但是发生锁冲突的概率很大。被大部分的mysql引擎支持MyISAM和InnoDB都支持表级锁但是InnoDB默认的是行级锁。
共享锁用法
LOCK TABLE table_name [ AS alias_name ] READ
1
排它锁用法
LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE
1
解锁用法
unlock tables;
1
3.页锁
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快但冲突多行级冲突少但速度慢。所以取了折衷的页级一次锁定相邻的一组记录。BDB支持页级锁
4.乐观锁和悲观锁
在数据库的锁机制中介绍过数据库管理系统DBMS中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。
乐观并发控制(乐观锁)和悲观并发控制悲观锁是并发控制主要采用的技术手段。
无论是悲观锁还是乐观锁都是人们定义出来的概念可以认为是一种思想。其实不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念像memcache、hibernate、tair等都有类似的概念。
针对于不同的业务场景应该选用不同的并发控制方式。所以不要把乐观并发控制和悲观并发控制狭义的理解为DBMS中的概念更不要把他们和数据中提供的锁机制行锁、表锁、排他锁、共享锁混为一谈。其实在DBMS中悲观锁正是利用数据库本身提供的锁机制来实现的。
4.1悲观锁 在关系数据库管理系统里悲观并发控制又名“悲观锁”Pessimistic Concurrency Control缩写“PCC”是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行数据应用了锁那只有当这个事务把锁释放其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。 悲观锁正如其名它指的是对数据被外界包括本系统当前的其他事务以及来自外部系统的事务处理修改持保守态度(悲观)因此在整个数据处理过程中将数据处于锁定状态。 悲观锁的实现往往依靠数据库提供的锁机制 也只有数据库层提供的锁机制才能真正保证数据访问的排他性否则即使在本系统中实现了加锁机制也无法保证外部系统不会修改数据
悲观锁的具体流程 在对任意记录进行修改前先尝试为该记录加上排他锁exclusive locking 如果加锁失败说明该记录正在被修改那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。 如果成功加锁那么就可以对记录做修改事务完成后就会解锁了。 其间如果有其他对该记录做修改或加排他锁的操作都会等待我们解锁或直接抛出异常。 在mysql/InnoDB中使用悲观锁
首先我们得关闭mysql中的autocommit属性因为mysql默认使用自动提交模式也就是说当我们进行一个sql操作的时候mysql会将这个操作当做一个事务并且自动提交这个操作。
1.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
2.查询出商品信息
select ... for update;
4.提交事务
commit;/commit work;
123456
通过下面的例子来说明
1.当你手动加上排它锁但是并没有关闭mysql中的autocommit。
SESSION1:
mysql select * from user for update;
------------------
| id | name | psword |
------------------
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
------------------
3 rows in set这里他会一直提示Unknown mysql update user set nameaa where id1; 1054 - Unknown column ‘aa’ in ‘field list’ mysql insert into user values(4,d,4); 1054 - Unknown column ‘d’ in ‘field list’ 12345678910111213141516
2.正常流程
窗口1
mysql set autocommit0;
Query OK, 0 rows affected
我这里锁的是表
mysql select * from user for update;
-----------
| id | price |
-----------
| 1 | 500 |
| 2 | 800 |
-----------
2 rows in set窗口2 mysql update user set priceprice-100 where id1; 执行上面操作的时候会显示等待状态一直到窗口1执行commit提交事务才会出现下面的显示结果 Database changed Rows matched: 1 Changed: 1 Warnings: 0
窗口1 mysql commit; Query OK, 0 rows affected mysql select * from user; ±—±------ | id | price | ±—±------ | 1 | 400 | | 2 | 800 | ±—±------ 2 rows in set 12345678910111213141516171819202122232425262728293031
上面的例子展示了排它锁的原理一个锁在某一时刻只能被一个线程占有其它线程必须等待锁被释放之后才可能获取到锁或者进行数据的操作。
悲观锁的优点和不足
悲观锁实际上是采取了“先取锁在访问”的策略为数据的处理安全提供了保证但是在效率方面由于额外的加锁机制产生了额外的开销并且增加了死锁的机会。并且降低了并发性当一个事物所以一行数据的时候其他事物必须等待该事务提交之后才能操作这行数据。
4.2乐观锁 在关系数据库管理系统里乐观并发控制又名“乐观锁”Optimistic Concurrency Control缩写“OCC”是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前每个事务会先检查在该事务读取数据后有没有其他事务又修改了该数据。如果其他事务有更新的话正在提交的事务会进行回滚。 乐观锁 Optimistic Locking 相对悲观锁而言乐观锁假设认为数据一般情况下不会造成冲突所以在数据进行提交更新的时候才会正式对数据的冲突与否进行检测如果发现冲突了则让返回用户错误的信息让用户决定如何去做。
相对于悲观锁在对数据库进行处理的时候乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。 数据版本,为数据增加的一个版本标识。当读取数据时将版本标识的值一同读出数据每更新一次同时对版本标识进行更新。当我们提交更新的时候判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对如果数据库表当前版本号与第一次取出来的版本标识值相等则予以更新否则认为是过期数据。 乐观锁的优点和不足
乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的因此尽可能直接做下去直到提交的时候才去锁定所以不会产生任何锁和死锁。但如果直接简单这么做还是有可能会遇到不可预期的结果例如两个事务都读取了数据库的某一行经过修改以后写回数据库这时就遇到了问题。
5.1InnoDB锁的特性
在不通过索引条件查询的时候InnoDB使用的确实是表锁由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行 的记录,但是如果是使用相同的索引键,是会出现锁冲突的。当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划explain查看,以确认是否真正使用了索引。
有关执行计划的解释可以看着这篇文章https://www.jianshu.com/p/b5c01bd4a306
1.通过非索引项检索数据加表锁
price属性并没有加索引因此这时候添加的锁为表级锁
窗口1
mysql select * from product where price88 for update;
----------------------
| id | name | price | num |
----------------------
| 2 | 蒙牛 | 88 | 1 |
----------------------窗口2 mysql update product set priceprice-100 where id6; 这里会等待直到窗口1 commit后显示下面结果 Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 1234567891011121314
2.使用相同索引值但是不同行引发的冲突
这里的num属性 加上了普通索引price属性并没有索引
窗口1
mysql set autocommit0;
Query OK, 0 rows affectedmysql select * from product where num1 and price68 for update; ----±-----±------±---- | id | name | price | num | ----±-----±------±---- | 1 | 伊利 | 68 | 1 | ----±-----±------±----
窗口2 mysql update product set priceprice100 where num1 and price88; 这里会发生等待直到窗口1 commit 显示下面结果 Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from product; ----±---------±------±---- | id | name | price | num | ----±---------±------±---- | 1 | 伊利 | 68 | 1 | | 2 | 蒙牛 | 188 | 1 | ----±---------±------±---- 123456789101112131415161718192021222324
3.当使用索引检索数据时不同事务可以操作不同行数据
锁一行数据DML操作其他行并没有影响
窗口1
mysql select * from user where id1 for update;
-----------
| id | price |
-----------
| 1 | 400 |
-----------窗口2 mysql update user set priceprice100 where id2; 无需等待窗口1 commit Database changed Rows matched: 1 Changed: 1 Warnings: 0 1234567891011121314
6.Record Lock、Gap Lock、Next-key Lock锁
6.1.Record Lock
单条索引上加锁record lock 永远锁的是索引而非数据本身如果innodb表中没有索引那么会自动创建一个隐藏的聚集索引锁住的就是这个聚集索引。所以说当一条sql没有走任何索引时那么将会在每一条聚集索引后面加X锁这个类似于表锁但原理上和表锁应该是完全不同的。
6.2.Gap Lock
间隙锁是在索引的间隙之间加上锁这是为什么Repeatable Read隔离级别下能防止幻读的主要原因。有关幻读的详细解释https://blog.csdn.net/qq_38238296/article/details/88363017
6.2.1 什么叫间隙锁
直接通过例子来说明
mysql select * from product_copy;
------------------------
| id | name | price | num |
------------------------
| 1 | 伊利 | 68 | 1 |
| 2 | 蒙牛 | 88 | 1 |
| 6 | tom | 2788 | 3 |
| 10 | 优衣库 | 488 | 4 |
------------------------
其中id为主键 num为普通索引
窗口A
mysql select * from product_copy where num3 for update;
----------------------
| id | name | price | num |
----------------------
| 6 | tom | 2788 | 3 |
----------------------
1 row in set窗口B mysql insert into product_copy values(5,‘kris’,1888,2); 这里会等待 直到窗口A commit才会显示下面结果 Query OK, 1 row affected
但是下面是不需要等待的 mysql update product_copy set priceprice100 where num1; Query OK, 2 rows affected Rows matched: 2 Changed: 2 Warnings: 0 mysql insert into product_copy values(5,‘kris’,1888,5); Query OK, 1 row affected 123456789101112131415161718192021222324252627282930
通过上面的例子可以看出Gap 锁的作用是在1,3的间隙之间加上了锁。而且并不是锁住了表我更新num15的数据是可以的.可以看出锁住的范围是1,3]U[3,4)。
6.2.2 为什么说gap锁是RR隔离级别下防止幻读的主要原因。
首先得理解什么是幻读https://blog.csdn.net/qq_38238296/article/details/88363017
解决幻读的方式很简单就是需要当事务进行当前读的时候保证其他事务不可以在满足当前读条件的范围内进行数据操作。
根据索引的有序性我们可以从上面的例子推断出满足where条件的数据只能插入在num1,3]U[3,4)两个区间里面只要我们将这两个区间锁住那么就不会发生幻读。
6.2.3. 主键索引/唯一索引当前读会加上Gap锁吗
直接通过例子来说明
窗口A
mysql select * from product_copy where id6 for update;
----------------------
| id | name | price | num |
----------------------
| 6 | tom | 2788 | 3 |
----------------------窗口B并不会发生等待 mysql insert into product_copy values(5,‘kris’,1888,3); Query OK, 1 row affected 1234567891011
例子说明的其实就是行锁的原因我只将id6的行数据锁住了用Gap锁的原理来解释的话因为主键索引和唯一索引的值只有一个所以满足检索条件的只有一行故并不会出现幻读所以并不会加上Gap锁。
6.2.4通过范围查询是否会加上Gap锁
前面的例子都是通过等值查询下面测试一下范围查询。
窗口A
mysql select * from product_copy where num3 for update;
------------------------
| id | name | price | num |
------------------------
| 10 | 优衣库 | 488 | 4 |
------------------------窗口B会等待 mysql insert into product_copy values(11,‘kris’,1888,5); Query OK, 1 row affected 不会等待 mysql insert into product_copy values(3,‘kris’,1888,2); Query OK, 1 row affected 1234567891011121314
其实原因都是一样只要满足检索条件的都会加上Gap锁
6.2.5 检索条件并不存在的当前读会加上Gap吗
1.等值查询
窗口A
mysql select * from product_copy where num5 for update;
Empty set窗口B6 和 4都会等待 mysql insert into product_copy values(11,‘kris’,1888,6); Query OK, 1 row affected
mysql insert into product_copy values(11,‘kris’,1888,4); Query OK, 1 row affected 12345678910
原因一样会锁住4,5]U[5,n的区间
2.范围查询
这里就会有点不一样
窗口A
mysql select * from product_copy where num6 for update;
Empty set
窗口B8 和 4 都会锁住
mysql insert into product_copy values(11,kris,1888,4);
Query OK, 1 row affectedmysql insert into product_copy values(11,‘kris’,1888,8); Query OK, 1 row affected 123456789
上面的2例子看出当你查询并不存在的数据的时候mysql会将有可能出现区间全部锁住。
6.3.Next-Key Lock
这个锁机制其实就是前面两个锁相结合的机制既锁住记录本身还锁住索引之间的间隙。
7.死锁的原理及分析
7.1. MVCC
MySQL InnoDB存储引擎实现的是基于多版本并发控制协议—MVCC(Multi-Version Concurrency Control) MVCC最大的好处相信也是耳熟能详读不加锁读写不冲突。在读多写少的OLTP应用中读写不冲突是非常重要的极大的增加了系统的并发性能这也是为什么现阶段几乎所有的RDBMS都支持了MVCC。
7.2. 2PLTwo-Phase Locking
传统RDBMS关系数据库管理系统加锁的一个原则就是2PL (二阶段锁)Two-Phase Locking。相对而言2PL比较容易理解说的是锁操作分为两个阶段加锁阶段与解锁阶段并且保证加锁阶段与解锁阶段不相交。下面仍旧以MySQL为例来简单看看2PL在MySQL中的实现。
transactionmysqlbegin;加锁阶段insert into加insert对应的锁update table加update对应的锁delete from加delete对应的锁commit解锁阶段将insert、update、delete的锁全部解开 上面的例子可以看出2PL就是将加锁、解锁分为两个阶段并且互相不干扰。加锁阶段只加锁解锁阶段只解锁。
7.3 为什么会发生死锁
MyISAM中是不会产生死锁的因为MyISAM总是一次性获得所需的全部锁要么全部满足要么全部等待。而在InnoDB中锁是逐步获得的就造成了死锁的可能。不过现在一般都是InnoDB引擎关于MyISAM不做考虑
在InnoDB中行级锁并不是直接锁记录而是锁索引。索引分为主键索引和非主键索引两种如果一条sql语句操作了主键索引MySQL就会锁定这条主键索引如果一条语句操作了非主键索引MySQL会先锁定该非主键索引再锁定相关的主键索引。
当两个事务同时执行一个锁住了主键索引在等待其他相关索引。另一个锁定了非主键索引在等待主键索引。这样就会发生死锁。
通过两个SQL死锁的例子来说明
1.两个session的两条语句 这种情况很好理解首先session1获得 id1的锁 session2获得id5的锁然后session想要获取id5的锁 等待session2想要获取id1的锁 也等待
2.两个session的一条语句 这种情况需要我们了解数据的索引的检索顺序原理简单说下普通索引上面保存了主键索引当我们使用普通索引检索数据时如果所需的信息不够那么会继续遍历主键索引。
假设默认情况是RR隔离级别针对session 1 从name索引出发检索到的是hdc,1hdc,6不仅会加name索引上的记录X锁而且会加聚簇索引上的记录X锁加锁顺序为先[1,hdc,100]后[6,hdc,10] 这个顺序是因为B树结构的有序性。而Session 2从pubtime索引出发[10,6],[100,1]均满足过滤条件同样也会加聚簇索引上的记录X锁加锁顺序为[6,hdc,10]后[1,hdc,100]。发现没有跟Session 1的加锁顺序正好相反如果两个Session恰好都持有了第一把锁请求加第二把锁死锁就发生了。
避免死锁这里只介绍常见的三种
如果不同程序会并发存取多个表尽量约定以相同的顺序访问表可以大大降低死锁机会。在同一个事务中尽可能做到一次锁定所需要的所有资源减少死锁产生概率对于非常容易产生死锁的业务部分可以尝试使用升级锁定颗粒度通过表级锁定来减少死锁产生的概率
这篇文章关于mysql锁写的很有深度http://hedengcheng.com/?p771 MySQL高级 03-21 emMySQL/em是目前常用的关系型数据库管理系统在WEB应用方面 emMySQL/em 也是目前很好的 RDBMS 应用软件之一随着淘宝去IOE(去除IBM小型机、Oracle数据库及EMC存储设备)化的推进emMySQL/em 数据库在当前的互联网应用中变得越来越重要。本教程主要讲授针对 Java 开发所需的 emMySQL/em 高级知识课程中会让大家快速掌握索引如何避免索引失效索引的优化策略了解innodb和myisam存储引擎熟悉emMySQL/emem锁/emem机制/em能熟练配置emMySQL/em主从复制熟练掌握explain、show profile、慢查询日志等日常SQL诊断和性能分析策略。 MySQL的锁机制和加锁原理深入分析 jack_shuai的博客 06-20 1956 一、MySQL/InnoDB中的行锁和表锁问题 首先我们知道InnoDB默认支持的是行锁但这并不代表InnoDB不支持表锁。必须明白这一点在InnoDB中并不是在数据行上加锁而是在对应的索引上加锁这一点和oracle并不同后者是在数据行上加锁的。这种实现的特点是只有通过索引条件检索数据的时候加的是行锁否则加表锁假如检索条件没有用到索引也是加表锁
1.通过非索引项检索数据加表…
