首页 / MYSQL / 6.mysql 锁机制

6.mysql 锁机制

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了6.mysql 锁机制，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含12060字，纯文字阅读大概需要18分钟。

内容图文

定义：锁是计算机协调多个进程或者线程并发访问某一资源的机制

在数据库中，除传统的计算资源（如CPU,RAM,IO等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

实际场景案例：生活购物

打个比方，我们在淘宝上抢购一件商品，商品只有一件库存，这个时候如果有很多人想去买，那么如何解决是你买到还是其他人买到的问题？

这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款记录表然后更新商品数量。在这个过程中，使用锁可以对有限资源进行保护，解决隔离和并发的矛盾

锁分类

从数据操作的类型（读、写）分

　　读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响 ?

　　写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的颗粒度:表锁，行锁

三锁

开销、加锁速度、死锁、粒度、并发性能。只能就具体应用的特点来说哪种锁更合适

表锁（偏读）

特点 ? 偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低

案例分析 ? 建表SQL ? 加读锁 ? 加写锁

【表级锁分析-建表sql】
create table mylock(
id int not null primary key auto_increment,
name varchar(20)
)engine myisam;

insert into mylock(name) values(‘a‘);
insert into mylock(name) values(‘b‘);
insert into mylock(name) values(‘c‘);
insert into mylock(name) values(‘d‘);
insert into mylock(name) values(‘e‘);

select * from mylock;

【手动添加表锁】
lock table 表名 read(write),表名2 read(write),其他;
lock table mylock read,book write;

【查看已添加过的锁】
show open tables;

【释放表锁】
unlock tables;

加读锁
1.session1 获得表mylock的read锁定 , lock table mylock read;
  session2连接终端

2.session1 可以查询该表的记录，select * from mylock; 
-->当前session1不能查询其他没有锁定的表，select * from book;
   session2等其他终端也可以查询该表的记录
-->其他session可以查询或者更新未锁定的表，select * from book;  update book set card=51 where book_id=1;

3. 当前session中插入或者更新 锁定的表  都会提示错误； insert into mylock(name) values(‘ee‘);  update mylock set name=‘a1‘ where id=1; 
    其他session插入或者更新锁定的表会一直等待，直到获得锁，  insert into mylock(name) values(‘ee‘);  update mylock set name=‘a1‘ where id=1; 

 4.释放锁后，其他session获得锁，对应插入或者更新操作完成。
 
 加写锁
我们为mylock表加写锁（myISAM存储引擎的写阻塞读的例子）

1. session1 获得表的write锁定，unlock tables; lock tables mylock write;
    待session1开启写锁后，session2在连接终端

2.当前session 对锁定的表 查询+ 更新 + 插入操作都可以执行；select * from mylock where id=1;   update mylock set name=‘a11‘ where id=1;  insert into mylock(name) values(‘ff‘);
其他session对锁定表的查询被阻塞，需要等待锁被释放；  select * from mylock where id=1;
        更新与插入操作，会等待释放锁。update mylock set name=‘a11‘ where id=1;  insert into mylock(name) values(‘ff‘);
备注：可以尝试用不同的id进行测试，mysql是有缓存的，第二次的条件会从缓存中取得，影响锁效果的演示

3.释放锁 unlock tables;
其他session获得锁，返回查询结果

############################案例结论######################
myISAM 在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

mysql的表级锁有两种模式：
    表共享读锁（Table read lock）
    表独占写锁 ( Table write lock )
对比======================
锁类型        可否兼容        读锁        写锁
读锁            是                    是            否
写锁            是                    否            否

结论=====================
结合上述对比,所以对myISAM表进行操作，会有以下情况：
1.对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一张表的读请求，但是会阻塞对同一张表的写请求。
    只有当读锁被释放后，才会执行其他进程的写操作。
2.对myISAM表的写操作（加写锁），会阻塞其他进程对同一张表的读和写操作，只有当写操作释放后，才会执行其他
    进程的读写操作

简而言之，就是读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都阻塞。


###########################表锁分析####################
【看看那些表被加锁了】
show open tables;

【如何分析表锁定】
可以通过检查table_locks_waited 和table_locks_immediate状态变量来分析系统上的表锁定；
show status like ‘table%‘;

这里有两个状态变量记录mysql内部表锁定的情况，两个变量说明如下：
table_locks_waited：产生表级锁定的次数，表示可以立即获取的查询次数，每立即获取锁值加1；
table_locks_immediate：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1）
     此值高则说明存在着比较严重的表级锁争用的情况；

此外，myisam的读写锁调度是写优先，这也是mysiam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何
操作，大量的更新会使查询很难得到锁，从而造成永久阻塞；

行锁（偏写）：

特点：

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）;二是采用了行级锁

由于行锁支持事务，复习老知识：

事务（Transation）及其ACID属性

事务transation 及其ACID属性

事务是由一组sql组成的逻辑处理单元，事务具有以下四个属性，通常简称事务的ACID属性。 ?

　　原子性（Atomicity）: 事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全部不执行。 ?

　　一致性（Consistent）:在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或者双向链表）也都是必须是正确的。 ?

　　隔离性（Isolation）:数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的‘独立’环境执行。这就意味着事务处理过程的中间状态对外部是不可见的，反之亦然。 ?

　　持久性（Durable）:事务完成后，它对数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题 ?

　　更新丢失（lost update）当两个或者多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在（隔离性），就会发生丢失更新问题 ? -----最后的更新覆盖了由其他事务所做的更新。例如：两个程序员在修改同一个java文件，每个程序员独立的更改自己的副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其 ? 更改副本的编辑人员覆盖了前一个程序员所做的更改。

　　脏读（Dirty reads） ? 一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时候，另一个事务也来读取同一条记录，这时候如果不加以控制，第二个事务读取了这些‘脏数据’，并据此做了进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做‘脏读’

　　总结：事务A读取到了事务B已修改但是未提交的数据，并在这个数据的基础上做了操作。此时，如果事务B进行了回滚，A读取的数据就无效，违反了一致性的要求

不可重复读（Non-Repeatable Reads）一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或者某些记录已经被删除了。这种现象叫做‘不可重复读’。

　　　　总结：事务A读取到了事务B已经提交的修改数据，不符合隔离性。

　幻读（Phantom reads）一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就被称为‘幻读’

　　　　总结：事务A读取到了事务B提交的新增数据，不符合隔离性

　　　　注意：幻读和脏读有点类似 ? 脏读是事务B里面修改了数据 ? 幻读是事务B里面新增了数据

事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

总结：数据库的事务隔离越严格，并发副作用就越小，但是付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，

? 这显然与“并发”是矛盾的。同时，不同的应用对数据读一致性和事务隔离程度的要求也是不同的。比如许多应用对“不可重复读”和“幻读” ? 并不敏感，可能更关心数据并发访问的能力。

查看当前数据库的事务隔离级别:show variables like ‘tx_isolation‘;

######################建表SQL##################
create table test_innodb_lock(a int(11),b varchar(16)) engine=innodb;

insert into test_innodb_lock values(1,"aaa");
insert into test_innodb_lock values(2,"222");
insert into test_innodb_lock values(3,"3333");
insert into test_innodb_lock values(4,"4000");
insert into test_innodb_lock values(5,"5000");
insert into test_innodb_lock values(6,"6000");
insert into test_innodb_lock values(7,"7000");
insert into test_innodb_lock values(8,"8000");
insert into test_innodb_lock values(9,"9000");
insert into test_innodb_lock values(1,"b1");

create index test_innodb_lock_a on test_innodb_lock(a);
create index test_innodb_lock_b on test_innodb_lock(b);

select * from  test_innodb_lock;
######################行锁定基本演示##################


行锁定基本演示：
1.session1    -> set autocommit=0;
   session2 ->set autocommit=0;

2.session1 -> update test_innodb_lock set b=‘b1‘ where a=1; #更新但是不提交，没有手写commit
   session2 -> update test_innodb_lock set b=‘b2‘ where a=1; #session2 被阻塞，只能等待

3. session1 -> commit; #提交更新 
    session2 -> 接触阻塞，更新正常进行
4. session2 执行命令commit;

注意：若果session1更新会话a=1,session2更新会话a=9,互相并不影响。
######################无索引行锁升级为表锁##################
varchar  不用 ‘ ‘  导致系统自动转换类型, 行锁变表锁
session1 -> update test_innodb_lock set a=41 where b=4000;

session2 -> update test_innodb_lock set b="9000" where a=9;
######################间隙锁危害##################
间隙锁的危害
案例：间隙锁带来的插入问题
1.session1 -> update test_innodb_lock set b=a*20 where a >1 and a <5;
  session2 ->insert into test_innodb_lock values(2,‘200‘); #阻塞产生，暂时不能插入
2.session1 -> commit;
   session2 -> 阻塞解除，完成插入



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

【危害】
因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值不存在。
间隙锁有同一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候
无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的损失。
######################面试题：常考如何锁定一行##################

面试题：如何锁定一行
1.session1 -> begin;    
                    select * from test_innodb_lock where a=8 for update;

session2 -> update test_innodb_lock set b=‘8001‘ where a=8;

session1 -> commit;

解释：select xxx...  from XX... for update;锁定某一行后，其他的操作会被阻塞，直到锁定行的会话提交commit;

######################案例结论##################
案例总结：
    innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更好一些，但是在整体并发处理能力方面要远远优于myISAM的表级锁定。
当系统并发量较高的时候，innodb的整体性能和myISAM相比就会有比较显著的优势了。
    但是，innodb的行级锁定同样也有其脆弱的一面(行锁变表锁)，当我们使用不当的时候，可能会让innodb的整体性能表现不仅不能比myISAM高，甚至会更差
#######################行锁分析######################
行锁分析
【如何分析行锁定】
通过InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
mysql> show status like ‘innodb_row_lock%‘;
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 20428 |
| Innodb_row_lock_time_avg      | 4085  |
| Innodb_row_lock_time_max      | 5453  |
| Innodb_row_lock_waits         | 5     |
+-------------------------------+-------+

对各个状态量的说明：
Innodb_row_lock_current_waits：当前正在等待锁定的数量
Innodb_row_lock_time：从系统启动到现在锁定的总时间长度
Innodb_row_lock_time_avg：每次等待所花费的平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits：系统启动后到现在总共等待的此时

对于这五个状态变量，比较重要的是以下三项
Innodb_row_lock_time_avg：等待平均时长
Innodb_row_lock_waits：等待总次数
Innodb_row_lock_time：等待总时长

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，
然后根据分析结果着手制定优化计划。

优化建议 ?

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁 ?

合理设计索引，尽量缩小锁的范围 ?

尽可能较少检索条件，避免间隙锁 ?

尽量控制事务大小，减少锁定资源量和时间长度

尽可能低级别事务隔离

页锁：

开销和加锁时间界于表锁和行锁之间：会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。 ? 了解一下即可

6.mysql 锁机制

标签：类型试题存储引擎现在主表中间需要 cti 会话

本文系统来源：https://www.cnblogs.com/weixiaotao/p/10741500.html

内容总结

以上是互联网集市为您收集整理的6.mysql 锁机制全部内容，希望文章能够帮你解决6.mysql 锁机制所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/506242.html

来源：【匿名】

【上一篇】MySQL 全局锁、表锁以及行锁【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【6.mysql 锁机制】教程文章相关的互联网学习教程文章

Mysql的锁机制与PHP文件锁处理高并发简单思路【代码】

以购买商品举例：① 从数据库获取库存的数量。② 检查一下库存的数量是否充足。③ 库存的数量减去买家购买的数量(以每个用户购买一个为例)。④ 最后完成购买。仅仅这几行逻辑代码在并发的情况下会出现问题，自己可以想象一下。这里暂时就不测试了，下面会针对并发的处理给出测试结果。创建表：CREATE TABLE `warehouse` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘id‘,`stock` int(11) NOT NULL DEFAULT ‘0‘ COMMENT ‘库...

MySQL锁机制

当并发事务同时访问一个资源的时候，有可能导致数据不一致。因此需要一种致机制来将访问顺序化。锁就是其中的一种机制。数据库上的操作可以归纳为两中，读和写。多个事务同时读一个对象的时候，是不会有冲突的。同时读和写或者同时写才会产生冲突。因此为了提高并发性，通常定义两种锁：A. 共享锁（Shared Lock、S锁）也叫读锁.共享锁表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。B. 排他锁（Exclusive Lock、...

MySQL锁机制

一、概况MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。MySQL这3种锁的特性可大致归纳如下。·表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率...

请问抢购的时候为什么选择用PHP文件锁而不是MySQL锁机制，PHP文件锁与其对比有什么特殊优点吗？

请问抢购的时候为什么选择用PHP文件锁而不是MySQL锁机制，PHP文件锁有什么特殊优点吗？回复内容：请问抢购的时候为什么选择用PHP文件锁而不是MySQL锁机制，PHP文件锁有什么特殊优点吗？

MySql的锁机制简单梳理

-- 使用Version字段自旋实现，更新前先查版本，更新前先判断版本悲观锁-- 行锁（Innodb特有）-- 读锁，写锁-- 表锁（由MySql源码实现）-- 读锁，写锁这里解释一下有些博客和资料中的分类是有差异的，记得有一篇博客说表锁属于乐观锁误导了我，在面试时面试官问了我这个问题我说属于乐观锁他又问了一下你确定吗...我就知道错了。乐观锁（Optimistic Concurrency Control，OCC）：假设不会发生并发冲突，只在提交操作时检查是否违...

mysql锁机制详解及死锁处理方式

还是没有从根本上同时解决查询和更新的问题。在一个有大数据量高并发表的mysql里，我们还可采用另一种策略来进行优化，那就是通过mysql主从（读写）分离来实现负载均衡，这样可避免优先哪一种操作从而可能导致另一种操作的堵塞。下面将用一个篇幅来说明mysql的读写分离技术。MyISAM使用的是 flock 类的函数，直接就是对整个文件进行锁定（叫做文件锁定），InnoDB使用的是 fcntl 类的函数，可以对文件中局部数据进行锁定（叫做行锁定...

MySQL的锁机制【图】

为了保证一致性，必须有锁的介入。MySQL操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素等地方也都适用锁，从而允许对多种不同资源的并发访问。打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？这里肯定要用到事物，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁...

MySQL锁机制

当并发事务同时访问一个资源的时候，有可能导致数据不一致。因此需要一种致机制来将访问顺序化。锁就是其中的一种机制。数据库上的操作可以归纳为两中，读和写。多个事务同时读一个对象的时候，是不会有冲突的。同时读和写或者同时写才会产生冲突。因此为了提高并发性，通常定义两种锁： A. 共享锁（Shared Lock、S锁）也叫读锁. 共享锁表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。 B. 排他锁（Exclusive ...

MySQL锁机制 -- 行锁

一、行锁概念及特点1.概念：给单独的一行记录加锁，主要应用于innodb表存储引擎2.特点：在innodb存储引擎中应用比较多，支持事务、开销大、加锁慢；会出现死锁；锁的粒度小，并发情况下，产生锁等待的概率比较低，所以支持的并发数比较高。二、数据库事务1.概念：事务是一系列操作组成的工作单元，该工作单元内的操作是不可分割的，也就是说要么全部都执行，要么全部不执行。2.特性：ACID原子性：事务是最小的工作单元，不可分割一...

mysql的锁机制【代码】【图】

Table_locks_immediate：产生表级锁定的次数； Table_locks_waited：出现表级锁定争用而发生等待的次数；产生的锁定次数是自动启动之后开始计算的。等待时间过长，表示锁的争用情况比较严重行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。如果一个事务请求的锁模式与当前的锁兼...

Mysql的锁机制与PHP文件锁处理高并发简单思路【代码】【图】

以购买商品举例： ① 从数据库获取库存的数量。 ② 检查一下库存的数量是否充足。 ③ 库存的数量减去买家购买的数量(以每个用户购买一个为例)。 ④ 最后完成购买。仅仅这几行逻辑代码在并发的情况下会出现问题，自己可以想象一下。这里暂时就不测试了，下面会针对并发的处理给出测试结果。创建表： CREATE TABLE `warehouse` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘id‘, `stock` int(11) NOT NULL DEFAULT ‘0...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / 6.mysql 锁机制

6.mysql 锁机制

内容导读

内容图文

三锁

行锁（偏写）：

事务transation 及其ACID属性

并发事务处理带来的问题 ?

事务隔离级别

内容总结

内容备注

内容手机端

【6.mysql 锁机制】教程文章相关的互联网学习教程文章

Mysql的锁机制与PHP文件锁处理高并发简单思路【代码】

MySQL锁机制

MySQL锁机制

请问抢购的时候为什么选择用PHP文件锁而不是MySQL锁机制，PHP文件锁与其对比有什么特殊优点吗？

MySql的锁机制简单梳理

mysql锁机制详解及死锁处理方式

MySQL的锁机制【图】

MySQL锁机制

MySQL锁机制 -- 行锁

mysql的锁机制【代码】【图】

Mysql的锁机制与PHP文件锁处理高并发简单思路【代码】【图】

MySQL锁机制详解及死锁处理方式

mysql锁机制【图】

13.MySQL锁机制

mysql的锁机制

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程