精华内容
下载资源
问答
  • MySQL的锁

    万次阅读 2020-09-03 17:24:21
    MySQL各存储引擎使用了三种类型(级别)锁定机制:表级锁定、行级锁定和页级锁定。 MySQL大致可归纳为以下3种: 表级:开销小,加锁快;不会出现死锁;锁定粒度大,发生冲突概率最高,并发度最低。 行级...

    一、概述

    数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制。
    MySQL各存储引擎使用了三种类型(级别)的锁定机制:表级锁定、行级锁定和页级锁定。
    MySQL大致可归纳为以下3种锁:
    表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
    行级锁:开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率最低,并发度最高。
    页级锁:开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般。

    顺便说下什么是死锁?

    所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去,此时称系统处于死锁状态或系统产生了死锁,这些永远互相等待的进程称为死锁进程。

    MySQL表级锁的锁模型(MyISAM)

    1、读锁和写锁

    MySQL表级锁有两种模式:表共享锁(读锁)和表独占写锁(写锁)
    MyISAM在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行更新操作(update,delete,insert)前,会自动给涉及的表加写锁。
    所以对MyISAM表进行操作,会有以下情况:
    (1)、对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进程的写操作。
    (2)、对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作。

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

    2、表锁的优化
    使用表级锁定在锁定实现的过程中比实现行级锁定或页级锁定所带来的附加成本要小,锁定本身所消耗的资源也是最少的。但是由于锁定的粒度比较大,所以造成锁定资源的争用情况也会比其他的锁定级别都要多,从而在较大程度上会降低并发处理能力。所以表锁优化,最关键的是如何让其提高并发度。由于锁定级别是不可能改变的了,所以首先需要尽可能的锁定的时间变短,然后就是让可能并发进行的操作尽可能的并发。

    1、缩短锁定时间

    (1)、尽量减少大的复杂的Query,将复杂的Query拆分成几个小的Query分步进行。
    (2)、尽可能的建立足够高效的索引,让数据检索更迅速。
    (3)、尽量让MyISAM存储引擎的表中存放必要的信息,控制字段类型。
    (4)、利用合理的机会优化MyISAM表数据文件。

    三、InnoDB锁的问题

    InnoDB与MyISAM的最大不同有两点:

    1、InnoDB支持事务(TRANSACTION),而MyISAM不支持事务
    2、InnoDB支持行级锁和表级锁,而MyISAM只支持表级锁

    1、事务(Transaction)及其ACID属性
    事务是由一组SQL语句组成的逻辑处理单元,事务具有4属性,通常称为事务的ACID属性。
    原子性:原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
    一致性:如果事务执行之前数据库是一个完整性的状态,那么事务结束后,无论事务是否执行成功,数据库仍然是一个完整性状态。
    隔离性:事务的隔离性是指多个用户并发访问数据库时,一个用户的事务不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离。
    持久性:持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。

    2、并发事务带来的问题
    更新丢失: 当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。
    脏读: 事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
    不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。
    幻读: 系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

    3、事务隔离级别

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

    4、InnoDB行级锁
    InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应的数据行加锁来实现的。

    InnoDB的这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB退化为表级锁

    InnoDB实现了以下两种类型的行锁:

    共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
    排他锁:允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集共享读锁和排他写锁。

    5、间隙锁

    当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)” .

    举例来说,假如emp表中只有101条记录,其empid的值分别是1,2,…,100,101,下面的SQL:

    SELECT * FROM emp WHERE empid > 100 FOR UPDATE

    是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。

    这个时候如果你插入empid等于102的数据的,如果那边事物还没有提交,那你就会处于等待状态,无法插入数据

    6、什么情况行锁会变表锁

    举一个例子,一个A表有个varchar属性的哪么,如果你在查询没有加‘name’(引号)那么这和时候同样能够实现查询功能,单行锁会变表锁。

    四、优化建议

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

    展开全文
  • Mysql的锁

    千次阅读 2017-07-28 20:47:34
    总的来说,Mysql 的锁分为以下三种: 表级锁:计算简单,不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。不会出现死锁的原因在于所有的锁是同时获取的。 行级锁:计算复杂,会出现死锁;锁定粒度最小...

    总的来说,Mysql 的锁分为以下三种:

    表级锁:计算简单,不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。不会出现死锁的原因在于所有的锁是同时获取的。

    行级锁:计算复杂,会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

     页面锁:开销和加锁时间界于表锁和行锁之间,会出现死锁,锁定粒度界于表锁和行锁之间,并发度也界于两者之间。

    常用的 Mysql 的两种引擎:MyISAM 和 Innodb最大的区别在于:1、是否支持事务,只有InnoDB支持,MyISAM不支持。2、MyISAM支持行锁和表锁,默认使用的是行锁。而MyISAM只支持行锁。事务本身带有锁的作用。

    加锁的操作:

    lock tables 表名 read/write;

    解锁的操作:

    unlock tables;

    对于 MyISAM 存储引擎,MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预。给MyISAM表显示加锁,一般是为了一定程度模拟事务操作,实现对某一时间点多个表的一致性读取。一旦显示的加了表锁之后,就只能显示的查询表锁,不能访问未加锁的表。在上锁过程中,必须锁定所有的表,及时同一张以别名的心事出现了多次,也必须锁定多次。

    读写锁的优先级不同:一般来说,写锁的优先级比读锁要高。但是这是可以通过设置来改变的。通过low_priority 和 high_priority 属性来设置每条操作语句(update/insert[low_priority],[high_priority]),这时属性的设置只对该条操作有效,或者设置 low_priority_updates 选项,达到对整条连接有效,如果想一次对所有的连接有效,如要在启动时设置 low_priority_updates 选项。

    总的来说,MyISAM引擎的读和写是串行的,有个concurrent_insert 的系统变量的值设置为0,1,2 可以用来控制。

    通过 set global concurrent_insert = xx这种方式来改变缺省的行为。

     

    查看数据表的引擎的操作:

    show table status from 数据库名 where name = "数据表名"

     

    测试记录(打开两个客户端连接数据库服务器):

    对于InnoDb 引擎。(此时的实验条件为 concurrent_insert == 1 下进行)。

    t1线程加读锁时,t2线程只能够查询,update和insert操作都被阻塞,知道t1线程解锁。而在加锁阶段,t1线程也只能进行查询操作,update 和 insert 操作都会报错。

     

    t1 线程加写锁时,t2线程的任何操作都被阻塞不返回,t2操作的结果在t1解锁之后返回。而在加锁阶段,t1线程能够进行查询、更新、插入操作。

    改变 concurrent_insert == 2 (set global concurrent_insert= 2;) 结果仍然一样?这一点好奇怪。


    展开全文
  • MySQL 数据库sql命令查询被锁的表实例演示,mysql的锁表与解锁,mysql强制解锁杀掉进程,mysql查询锁表一直转圈。 show open tables where in_use > 0 命令可以查询锁表。 in_use 为 1 表示这个表同时被两个用户使用...

    show open tables where in_use > 0 命令可以查询锁表。
    in_use1 表示这个表同时被两个用户使用,一个正在用,一个在锁定中。

    -- 为md_class表增加个写锁定
    lock tables md_class write;
    -- 查看锁表
    show open tables where in_use > 0;
    -- 表解锁
    unlock tables;
    

    查看锁表:
    在这里插入图片描述
    特殊情况下的锁定是线程阻塞导致的,查询锁表都查不出来,一直转圈,即使查询出也无法解锁,需要强制杀掉阻塞的线程。
    select * from information_schema.innodb_trx; 方法可以查询到有两条阻塞的线程。

    在这里插入图片描述
    通过 kill + trx_mysql_thread_id 可以直接把对应的进程杀掉。
    例:kill 3886;

    在这里插入图片描述

    喜欢的点个赞❤吧!

    展开全文
  • MySQL的锁机制和加锁原理

    万次阅读 多人点赞 2019-03-09 10:35:01
    MySQL的锁机制和加锁原理 文章目录MySQL的锁机制和加锁原理1.行锁2.表锁3.页锁4.乐观锁和悲观锁4.1悲观锁4.2乐观锁5.MySQL/InnoDB中的行锁和表锁问题5.1InnoDB锁的特性6.Record Lock、Gap Lock、Next-key Lock锁6.1...

    MySQL的锁机制和加锁原理


    首先对mysql锁进行划分:

    1. 按照锁的粒度划分:行锁、表锁、页锁
    2. 按照锁的使用方式划分:共享锁、排它锁(悲观锁的一种实现)
    3. 还有两种思想上的锁:悲观锁、乐观锁。
    4. InnoDB中有几种行级锁类型:Record Lock、Gap Lock、Next-key Lock
    5. Record Lock:在索引记录上加锁
    6. Gap Lock:间隙锁
    7. Next-key Lock:Record Lock+Gap 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;
    

    共享锁就是允许多个线程同时获取一个锁,一个锁可以同时被多个线程拥有。

    排它锁用法(X 锁 写锁)

    ​ 若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

    select ... for update
    

    排它锁,也称作独占锁,一个锁在某一时刻只能被一个线程占有,其它线程必须等待锁被释放之后才可能获取到锁。

    2.表锁

    ​ 表级锁是mysql锁中粒度最大的一种锁,表示当前的操作对整张表加锁,资源开销比行锁少,不会出现死锁的情况,但是发生锁冲突的概率很大。被大部分的mysql引擎支持,MyISAM和InnoDB都支持表级锁,但是InnoDB默认的是行级锁。

    共享锁用法

    LOCK TABLE table_name [ AS alias_name ] READ
    

    排它锁用法

    LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE
    

    解锁用法

    unlock tables;
    

    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;
    

    通过下面的例子来说明:

    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 name=aa where id=1;
    1054 - Unknown column 'aa' in 'field list'
    mysql> insert into user values(4,d,4);
    1054 - Unknown column 'd' in 'field list'
    

    2.正常流程

    窗口1:
    mysql> set autocommit=0;
    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 price=price-100 where id=1;
    执行上面操作的时候,会显示等待状态,一直到窗口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
    
    

    ​ 上面的例子展示了排它锁的原理:一个锁在某一时刻只能被一个线程占有,其它线程必须等待锁被释放之后才可能获取到锁或者进行数据的操作。

    悲观锁的优点和不足

    ​ 悲观锁实际上是采取了“先取锁在访问”的策略,为数据的处理安全提供了保证,但是在效率方面,由于额外的加锁机制产生了额外的开销,并且增加了死锁的机会。并且降低了并发性;当一个事物所以一行数据的时候,其他事物必须等待该事务提交之后,才能操作这行数据。

    4.2乐观锁

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

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

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

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

    乐观锁的优点和不足

    ​ 乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的,因此尽可能直接做下去,直到提交的时候才去锁定,所以不会产生任何锁和死锁。但如果直接简单这么做,还是有可能会遇到不可预期的结果,例如两个事务都读取了数据库的某一行,经过修改以后写回数据库,这时就遇到了问题。

    5.1InnoDB锁的特性

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

    有关执行计划的解释可以看着这篇文章:https://www.jianshu.com/p/b5c01bd4a306

    1.通过非索引项检索数据,加表锁!

    price属性并没有加索引,因此这时候添加的锁为表级锁!
    窗口1:
    mysql> select * from product where price=88 for update;
    +----+------+-------+-----+
    | id | name | price | num |
    +----+------+-------+-----+
    |  2 | 蒙牛 |    88 |   1 |
    +----+------+-------+-----+
    
    窗口2:
    mysql> update product set price=price-100 where id=6;
    这里会等待,直到窗口1 commit后显示下面结果!
    Query OK, 1 row affected
    Rows matched: 1  Changed: 1  Warnings: 0
    

    2.使用相同索引值但是不同行引发的冲突

    这里的num属性 加上了普通索引,price属性并没有索引
    窗口1:
    mysql> set autocommit=0;
    Query OK, 0 rows affected
    
    mysql> select * from product where num=1 and price=68 for update;
    +----+------+-------+-----+
    | id | name | price | num |
    +----+------+-------+-----+
    |  1 | 伊利 |    68 |   1 |
    +----+------+-------+-----+
    
    窗口2:
    mysql> update product set price=price+100 where num=1 and price=88;
    这里会发生等待,直到窗口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 |
    +----+----------+-------+-----+
    

    3.当使用索引检索数据时不同事务可以操作不同行数据

    锁一行数据,DML操作其他行并没有影响
    窗口1:
    mysql> select * from user where id=1 for update;
    +----+-------+
    | id | price |
    +----+-------+
    |  1 |   400 |
    +----+-------+
    
    窗口2:
    mysql> update user set price=price+100 where id=2;
    无需等待窗口1 commit
    Database changed
    Rows matched: 1  Changed: 1  Warnings: 0
    

    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 num=3 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 price=price+100 where num=1;
    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
    

    ​ 通过上面的例子可以看出Gap 锁的作用是在1,3的间隙之间加上了锁。而且并不是锁住了表,我更新num=1,5的数据是可以的.可以看出锁住的范围是(1,3]U[3,4)。

    6.2.2 为什么说gap锁是RR隔离级别下防止幻读的主要原因。

    首先得理解什么是幻读:https://blog.csdn.net/qq_38238296/article/details/88363017

    解决幻读的方式很简单,就是需要当事务进行当前读的时候,保证其他事务不可以在满足当前读条件的范围内进行数据操作。

    ​根据索引的有序性,我们可以从上面的例子推断出满足where条件的数据,只能插入在num=(1,3]U[3,4)两个区间里面,只要我们将这两个区间锁住,那么就不会发生幻读。

    6.2.3. 主键索引/唯一索引+当前读会加上Gap锁吗?

    直接通过例子来说明

    窗口A:
    mysql> select * from product_copy where id=6 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
    

    例子说明的其实就是行锁的原因,我只将id=6的行数据锁住了,用Gap锁的原理来解释的话:因为主键索引和唯一索引的值只有一个,所以满足检索条件的只有一行,故并不会出现幻读,所以并不会加上Gap锁。

    6.2.4通过范围查询是否会加上Gap锁

    ​ 前面的例子都是通过等值查询,下面测试一下范围查询。

    窗口A:
    mysql> select * from product_copy where num>3 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
    

    其实原因都是一样,只要满足检索条件的都会加上Gap锁

    6.2.5 检索条件并不存在的当前读会加上Gap吗?

    1.等值查询

    窗口A:
    mysql> select * from product_copy where num=5 for update;
    Empty set
    
    窗口B:64都会等待
    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
    

    原因一样会锁住(4,5]U[5,n)的区间

    2.范围查询

    这里就会有点不一样

    窗口A:
    mysql> select * from product_copy where num>6 for update;
    Empty set
    窗口B:84 都会锁住
    mysql> insert into product_copy values(11,'kris',1888,4);
    Query OK, 1 row affected
    
    mysql> insert into product_copy values(11,'kris',1888,8);
    Query OK, 1 row affected
    

    ​ 上面的2例子看出当你查询并不存在的数据的时候,mysql会将有可能出现区间全部锁住。

    6.3.Next-Key Lock

    这个锁机制其实就是前面两个锁相结合的机制,既锁住记录本身还锁住索引之间的间隙。

    7.死锁的原理及分析

    7.1. MVCC

    ​ MySQL InnoDB存储引擎,实现的是基于多版本并发控制协议—MVCC(Multi-Version Concurrency Control) MVCC最大的好处,相信也是耳熟能详:读不加锁,读写不冲突。在读多写少的OLTP应用中,读写不冲突是非常重要的,极大的增加了系统的并发性能,这也是为什么现阶段,几乎所有的RDBMS,都支持了MVCC。

    7.2. 2PL:Two-Phase Locking

    ​ 传统RDBMS(关系数据库管理系统)加锁的一个原则,就是2PL (二阶段锁):Two-Phase Locking。相对而言,2PL比较容易理解,说的是锁操作分为两个阶段:加锁阶段与解锁阶段,并且保证加锁阶段与解锁阶段不相交。下面,仍旧以MySQL为例,来简单看看2PL在MySQL中的实现。

    transaction mysql
    begin; 加锁阶段
    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获得 id=1的锁 session2获得id=5的锁,然后session想要获取id=5的锁 等待,session2想要获取id=1的锁 ,也等待!

    2.两个session的一条语句

    在这里插入图片描述

    ​ 这种情况需要我们了解数据的索引的检索顺序原理简单说下:普通索引上面保存了主键索引,当我们使用普通索引检索数据时,如果所需的信息不够,那么会继续遍历主键索引。

    ​ 假设默认情况是RR隔离级别,针对session 1 从name索引出发,检索到的是(hdc,1)(hdc,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恰好都持有了第一把锁,请求加第二把锁,死锁就发生了。

    避免死锁,这里只介绍常见的三种

    1. 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。
    2. 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;
    3. 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率;

    这篇文章关于mysql锁写的很有深度:http://hedengcheng.com/?p=771

    展开全文
  • MySql之深入分析MySql 的锁

    千次阅读 2020-01-30 13:08:01
    MySql之深入分析MySql 的锁
  • Mysql的锁(S锁和X锁的区别)

    千次阅读 2018-11-28 10:56:41
    Mysql的锁系统:shared lock 和 exclusive lock (共享锁和排它锁,也叫读锁和写锁,即read lock和write lock) 读锁是共享的,或者说是相互不阻塞的写锁是排他的,一个写锁会阻塞其他的写锁和读锁在实际的数据库系统...
  • 你针对懂Mysql的锁吗?详谈Myql的锁机制 锁的基本介绍 要说锁,应该追溯到操作系统中的多线程原理,锁机制在其中发挥着必不可少的作用,先抛出锁的定义 锁是计算机协调多个进程或线程并发访问某一资源的机制 在...
  • mysql的锁详解

    万次阅读 2020-08-02 12:08:25
    因为mysql的数据也是一种共享资源,那么为了保证数据并发房屋的一致性有效性,那么就需要了。 分类 从性能上分为乐观和悲观 乐观:乐观一般会使用版本号机制实现 使用版本号:数据库表会加上一个...
  • 聊聊MySQL的锁机制

    2019-08-30 11:05:48
    聊聊MySQL的锁机制 在MySQL中,除了对事务的管理,还有对锁机制的操纵。一直以来,这块我都不是很明白,于是,今天打算好好撸它一撸。 MySQL中的锁的划分原则: 按照锁的粒度划分:全局锁、表锁、页锁、行锁、 ...
  • Mysql的锁类型、锁模式、加锁方式

    千次阅读 2019-05-23 20:03:55
    Mysql的锁: 锁类型(lock_type): 表锁: 通过Mysql服务实现,加锁:lock table xxx read/write,解锁:unlock tables; 当会话将表加上锁后,在锁释放之前,会话只能访问这些加锁的表 表锁里又可以分为读锁和写锁...
  • 经常听到数据库的锁机制,不同的数据库的不同实现不同, 听多了就头疼了,今天就把MySQL的锁整的明明白白: 锁: 首先想一下为什么我们会需要锁,其实就是为了解决并发操作数据的,是一种控制并发的机制。 乐观...
  • 【面试题】MySQL的锁机制

    千次阅读 2020-09-21 16:51:38
    MySQL的锁机制 通常有 行锁 行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,但加锁的开销也最大。有可能会出现死锁的情况。 ...
  • Mysql 的锁与索引的关系

    千次阅读 2016-09-07 16:14:00
     mysql的锁:(行锁,表锁,页锁) 与索引存在很大的关系,我们平时在写sql的时候其实很少注意这个问题(估计是个人水平比较低,所以很少注意吧),那么在有索引和没有索引的时候数据库锁的策略是什么呢?...
  • MySQL的锁机制

    千次阅读 2020-04-21 12:58:39
    但是,为了保证并发访问数据的一致性和完整性,MySQL服务端内部有它特有的锁机制。 MySQL支持插件式的存储引擎,不同的存储引擎内部锁实现也大不相同。 例如:MyISAM只支持表锁,而InnoDB则支持更细粒度的行锁和间隙...
  • mysql的锁表问题

    2015-06-30 09:33:53
    mysql的锁表问题 SHOW PROCESSLIST查看数据库中表的状态,是否被锁; kill id //杀掉被锁的表 =================================================== set autocommit=0; select * from t1 where uid='xxxx' ...
  • MySQL的锁表问题

    2014-09-12 11:17:56
    mysql的锁表问题 http://blog.csdn.net/u012109105/article/details/34180951 SHOW PROCESSLIST查看数据库中表的状态,是否被锁; kill id //杀掉被锁的表 ===========================================...
  • 延伸阅读: 三分钟了解Mysql的表级锁 ...InnoDB不仅支持行级别的锁,也支持表级别的锁。平常我们会听到各种锁,你是不是不仅迷茫它们怎么用的,还会迷茫它们 各自之间都是什么关系? 什么是乐观锁和悲观锁? ...
  • 谈谈MySQL的锁

    万次阅读 多人点赞 2018-08-20 06:23:26
    ,在现实生活中是为我们想要隐藏于外界所使用一种工具。在计算机中,是协调多个进程或县城并发访问某一资源一种机制。在数据库当中,除了传统计算资源(CPU、RAM、I/O等等)争用之外,数据也是一种供许多...
  • 浅谈mysql的锁和索引之间莫大的联系

    千次阅读 2017-08-29 17:00:24
    在平时我们用mysql的锁时,一般刚接触数据库是很少考虑锁的效率,一般只求到达防止并发的目的就可以了,但是随着数据量的增大我们就会发现有很多sql我们已经写的非常优化了,但是有时候还是很慢,很难找到原因,这...
  • 相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level ...
  • 掌握MySQL的锁机制

    2019-05-19 16:32:48
    MySQL的锁机制,按粒度分为行级锁,页级锁,表级锁,其中按用法还分为共享锁和排他锁. 行级锁 行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁...
  • mysql的锁机制

    2016-08-09 12:52:35
    锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户...本章我们着重讨论MySQL锁机制的特点,常见的锁问题,以及解决MySQL
  • mysql的锁机制 1、MySQL锁的基本介绍 ​ **锁是计算机协调多个进程或线程并发访问某一资源的机制。**在数据库中,除传统的 计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。如何保证...
  • MySQL的锁(1)

    2012-11-15 13:07:15
    MySQL的锁(1)  锁机制是数据库系统区别于文件系统的一个关键特性,他可以确保用户能以一致的方式读取和修改数据。  为了保证一致性,必须有锁的介入。MySQL操作缓冲池中的LRU列表,删除、添加、移动...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 22,646
精华内容 9,058
关键字:

mysql的锁

mysql 订阅