精华内容
下载资源
问答
  • mysql锁机制
    千次阅读
    2021-11-26 13:34:10

    Mysql锁:

    • 在多线程当中如果想保证数据的准确性是如何实现的呢?没错,通过同步实现。同步就相当于是加锁。加了锁以后有什么好处呢?当一个线程真正在操作数据的时候,其他线程只能等待。当一个线程执行完毕后,释放锁。其他线程才能进行操作!

    • 那么我们的MySQL数据库中的锁的功能也是类似的,处理事务的隔离性中,可能会出现脏读、不可重复读、幻读的问题,所以,锁的作用也可以解决这些问题!

    • 在数据库中,数据是一种供许多用户共享访问的资源,如何保证数据并发访问的一致性、有效性,是所有数据库必须解决的一个问题,MySQL由于自身架构的特点,在不同的存储引擎中,都设计了面对特定场景的锁定机制,所以引擎的差别,导致锁机制也是有很大差别的。

    锁机制 :

    • 数据库为了保证数据的一致性,而使用各种共享的资源在被并发访问时变得有序所设计的一种规则。
    • 举例:在电商网站购买商品时,商品表中只存有1个商品,而此时又有两个人同时购买,那么谁能买到就是一个关键的问题。
      这里会用到事务进行一系列的操作:
      1. 先从商品表中取出物品的数据
      2. 然后插入订单
      3. 付款后,再插入付款表信息
      4. 更新商品表中商品的数量

    以上过程中,使用锁可以对商品数量数据信息进行保护,实现隔离,即只允许第一位用户完成整套购买流程,而其他用户只能等待,这样就解决了并发中的矛盾问题。

    锁的分类:

    按操作分类:

    • 共享锁:也叫读锁。针对同一份数据,多个事务读取操作可以同时加锁而不互相影响 ,但是不能修改数据记录。
    • 排他锁:也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入

    按粒度分类:

    • 表级锁:操作时,会锁定整个表。开销小,加锁快;不会出现死锁;锁定力度大,发生锁冲突概率高,并发度最低。偏向于MyISAM存储引擎!
    • 行级锁:操作时,会锁定当前操作行。开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高。偏向于InnoDB存储引擎!
    • 页级锁:锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间,会出现死锁,并发性能一般。

    按使用方式分类:

    • 悲观锁:每次查询数据时都认为别人会修改,很悲观,所以查询时加锁。
    • 乐观锁:每次查询数据时都认为别人不会修改,很乐观,但是更新时会判断一下在此期间别人有没有去更新这个数据

    不同存储引擎支持的锁

    存储引擎表级锁行级锁页级锁
    MyISAM支持不支持不支持
    InnoDB支持支持不支持
    MEMORY支持不支持不支持
    BDB支持不支持支持
    共享锁:

    多个共享锁之间可以共享,如果是有键的话InnoDB默认是行锁,没有的话就会提升到表锁,是行锁时多个窗口可以修改不同行的数据,同行的话需要等先加锁的提交,不同行可以直接修改,但是另外一个要查询也要等后面修改的提交。提交完锁就消失了

    共享锁:

    SELECT语句 LOCK IN SHARE MODE;
    

    窗口1:

    - 窗口1
    /*
    	共享锁:数据可以被多个事务查询,但是不能修改
    */
    -- 开启事务
    START TRANSACTION;
    
    -- 查询id为1的数据记录。加入共享锁
    SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
    
    -- 查询分数为99分的数据记录。加入共享锁
    SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;
    
    -- 提交事务
    COMMIT;
    

    窗口2:

    -- 窗口2
    -- 开启事务
    START TRANSACTION;
    
    -- 查询id为1的数据记录(普通查询,可以查询)
    SELECT * FROM student WHERE id=1;
    
    -- 查询id为1的数据记录,并加入共享锁(可以查询。共享锁和共享锁兼容)
    SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
    
    -- 修改id为1的姓名为张三三(不能修改,会出现锁的情况。只有窗口1提交事务后,才能修改成功)
    UPDATE student SET NAME='张三三' WHERE id = 1;
    
    -- 修改id为2的姓名为李四四(修改成功,InnoDB引擎默认是行锁)
    UPDATE student SET NAME='李四四' WHERE id = 2;
    
    -- 修改id为3的姓名为王五五(修改失败,InnoDB引擎如果不采用带索引的列加锁。则会提升为表锁)
    UPDATE student SET NAME='王五五' WHERE id = 3;
    
    -- 提交事务
    COMMIT;
    
    排他锁:

    在排他锁执行的时候,其他事务普通查询可以,不可以加锁任何操作

    -- 标准语法
    SELECT语句 FOR UPDATE;
    

    窗口1:

    -- 窗口1
    /*
    	排他锁:加锁的数据,不能被其他事务加锁查询或修改
    */
    -- 开启事务
    START TRANSACTION;
    
    -- 查询id为1的数据记录,并加入排他锁
    SELECT * FROM student WHERE id=1 FOR UPDATE;
    
    -- 提交事务
    COMMIT;
    

    窗口2:

    -- 窗口2
    -- 开启事务
    START TRANSACTION;
    
    -- 查询id为1的数据记录(普通查询没问题)
    SELECT * FROM student WHERE id=1;
    
    -- 查询id为1的数据记录,并加入共享锁(不能查询。因为排他锁不能和其他锁共存)
    SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
    
    -- 查询id为1的数据记录,并加入排他锁(不能查询。因为排他锁不能和其他锁共存)
    SELECT * FROM student WHERE id=1 FOR UPDATE;
    
    -- 修改id为1的姓名为张三(不能修改,会出现锁的情况。只有窗口1提交事务后,才能修改成功)
    UPDATE student SET NAME='张三' WHERE id=1;
    
    -- 提交事务
    COMMIT;
    
    MyISAM锁:

    MyISAM读锁:

    myisam是加整个表的锁,读锁的时候,不解锁的话所有的事务可以查,不可以有其他任何操作包括本身事务也不可以操作

    -- 加锁
    LOCK TABLE 表名 READ;
    
    -- 解锁(将当前会话所有的表进行解锁)
    UNLOCK TABLES;
    

    MyISAM写锁:

    写锁的时候,只要不解锁其他事务不可以执行任何操作,本身事务可以操作

    -- 标准语法
    -- 加锁
    LOCK TABLE 表名 WRITE;
    
    -- 解锁(将当前会话所有的表进行解锁)
    UNLOCK TABLES;
    
    悲观锁:
    • 就是很悲观,它对于数据被外界修改的操作持保守态度,认为数据随时会修改。
    • 整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。
    • 行锁,表锁不论是读写锁都是悲观锁。
    乐观锁:
    • 就是很乐观,每次自己操作数据的时候认为没有人会来修改它,所以不去加锁。
    • 但是在更新的时候会去判断在此期间数据有没有被修改。
    • 需要用户自己去实现,不会发生并发抢占资源,只有在提交操作的时候检查是否违反数据完整性。
    乐观锁的简单实现方式:

    实现思想:加标记去比较,一样则执行,不同则不执行

    方式一:版本号

    • 给数据表中添加一个version列,每次更新后都将这个列的值加1。
    • 读取数据时,将版本号读取出来,在执行更新的时候,比较版本号。
    • 如果相同则执行更新,如果不相同,说明此条数据已经发生了变化。
    • 用户自行根据这个通知来决定怎么处理,比如重新开始一遍,或者放弃本次更新。
    -- 创建city表
    CREATE TABLE city(
    	id INT PRIMARY KEY AUTO_INCREMENT,  -- 城市id
    	NAME VARCHAR(20),                   -- 城市名称
    	VERSION INT                         -- 版本号
    );
    
    -- 添加数据
    INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'广州',1),(NULL,'深圳',1);
    
    -- 修改北京为北京市
    -- 1.查询北京的version
    SELECT VERSION FROM city WHERE NAME='北京';
    -- 2.修改北京为北京市,版本号+1。并对比版本号
    UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;
    

    方式二:时间戳

    • 和版本号方式基本一样,给数据表中添加一个列,名称无所谓,数据类型需要是timestamp
    • 每次更新后都将最新时间插入到此列。
    • 读取数据时,将时间读取出来,在执行更新的时候,比较时间。
    • 如果相同则执行更新,如果不相同,说明此条数据已经发生了变化。
    悲观锁和乐观锁使用前提:
    • 对于读的操作远多于写的操作的时候,这时候一个更新操作加锁会阻塞所有的读取操作,降低了吞吐量。最后还要释放锁,锁是需要一些开销的,这时候可以选择乐观锁。
    • 如果是读写比例差距不是非常大或者系统没有响应不及时,吞吐量瓶颈的问题,那就不要去使用乐观锁,它增加了复杂度,也带来了业务额外的风险。这时候可以选择悲观锁。
    更多相关内容
  • MYSQL锁机制

    2018-07-26 19:23:56
    本文详细描述了MYSQL锁机制,掌握锁机制,对于避免死锁,提高软件的工作效率,有很大的帮助.
  • 本文实例讲述了MySQL锁机制与用法。分享给大家供大家参考,具体如下: MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁;BDB存储引擎采用的是...
  • mysql锁机制

    千次阅读 2021-12-09 15:31:36
    首先对mysql锁进行划分: 按照锁的粒度划分:行锁、表锁、页锁 按照锁的使用方式划分:共享锁、排它锁(悲观锁的一种实现) 还有两种思想上的锁:悲观锁、乐观锁。 InnoDB中有几种行级锁类型:Record Lock、Gap ...

    MySQL的锁机制


    首先对mysql锁进行划分:

    1. 按照锁的粒度划分:行锁、表锁、页锁
    2. 按照锁的使用方式划分:共享锁、排它锁(悲观锁的一种实现)
    3. 还有两种思想上的锁:悲观锁、乐观锁。
    4. InnoDB中有几种行级锁类型:Record Lock、Gap Lock、Next-key Lock

    MySQL的锁机制最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

    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
    

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

    MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如 MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同 一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB是支持行锁的, 这也是MyISAM被InnoDB替代的重要原因之一。

    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存储引擎支持页级锁

    全局锁
    除了行锁,表锁,页锁外,这里还补充一种全局锁,这种锁的范围最大。
    全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命 令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括 建表、修改表结构等)和更新类事务的提交语句。

    全局锁的典型使用场景是,做全库逻辑备份。 全 也就是把整库每个表都select出来存成文本。

    4.乐观锁和悲观锁

    4.1悲观锁

    在关系数据库管理系统里,悲观并发控制又名“悲观锁”,Pessimistic Concurrency Control,缩写“PCC”)是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行数据应用了锁,那只有当这个事务把锁释放,其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境,以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

    悲观锁,正如其名,它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度(悲观),因此,在整个数据处理过程中,将数据处于锁定状态 悲观锁的实现,往往依靠数据库提供的锁机制 (也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)

    mysql中实现悲观锁的具体流程:

    在对任意记录进行修改前,先尝试为该记录加上排他锁(exclusive locking)
    如果加锁失败,说明该记录正在被修改,那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。
    如果成功加锁,那么就可以对记录做修改,事务完成后就会解锁了。
    其间如果有其他对该记录做修改或加排他锁的操作,都会等待我们解锁或直接抛出异常。

    总而言之就是一句话:mysql中悲观锁的实现是通过排他锁来实现的

    在mysql(InnoDB)中使用悲观锁:

    1.开始事务
    begin;/begin work;/start transaction; (三者选一就可以)
    2.查询出商品信息
    select ... for update;(这里是使用的行锁的排他锁)
    4.提交事务
    commit;/commit work;
    

    通过下面的例子来说明:
    1.当你手动加上排它锁,但是并没有关闭mysql中的autocommit。

    1.这里先给user表所有的行加上行锁的排他锁
    mysql> select * from user for update;
    +----+------+--------+
    | id | name | psword |
    +----+------+--------+
    |  1 | a    | 1      |
    |  2 | b    | 2      |
    |  3 | c    | 3      |
    +----+------+--------+
    3 rows in set
    
    2.再对加了排他锁的行更改数据时,这里他会一直提示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.关闭mysql中的autocommit后的正常流程

    窗口1:
    mysql> set autocommit=0;(先关闭mysql中的autocommit)
    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:执行commit手动提交事务
    mysql> commit;
    Query OK, 0 rows affected
    再查询一下user表,发现已经执行了窗口2的更新操作
    mysql> select * from user;
    +----+-------+
    | id | price |
    +----+-------+
    |  1 |   400 |
    |  2 |   800 |
    +----+-------+
    2 rows in set
    

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

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

    4.2乐观锁

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

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

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

    mysql实现乐观锁一般来说有2种方式:
    1.使用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现方式。
    一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时,将version字段的值一同读出,数据每更新一次,对此version值加一。
    当提交更新的时候,判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对,如果数据库表当前版本号与第一次取出来的version值相等,就进行更新操作,否则认为是过期数据,正在提交的事务会进行回滚。
    2.第二种实现方式和第一种差不多,同样是在需要乐观锁控制的table中增加一个字段,名称无所谓,字段类型使用时间戳(timestamp), 和上面的version类似,也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比,如果一致就更新,否则就是版本冲突。

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

    乐观锁和MVCC的区别?
    在数据库中,并发控制是指在多个用户/进程/线程同时对数据库进行操作时,如何保证事务的一致性和隔离性的,同时最大程度地并发。

    当多个用户/进程/线程同时对数据库进行操作时,会出现3种冲突情形:

    1. 读-读,不存在任何问题
    2. 读-写,有隔离性问题,可能遇到脏读(会读到未提交的数据) ,幻影读等。
    3. 写-写,可能丢失更新

    要解决冲突,一种办法是是锁,即基于锁的并发控制,比如2PL两阶段锁协议,这种方式开销比较高,而且无法避免死锁。而基于无锁的并发控制有两种方式:就是MVCC多版本并发控制和OCC乐观并发控制,这两种方式分别解决上面的第2,3种情况。

    多版本并发控制(MVCC)是一种用来解决读-写冲突的无锁并发控制,也就是为事务分配单向增长的时间戳,为每个修改保存一个版本,版本与事务时间戳关联,读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作,写操作不用阻塞读操作的同时,避免了脏读和不可重复读

    乐观并发控制(OCC)是一种用来解决写-写冲突的无锁并发控制,认为事务间争用没有那么多,所以先进行修改,在提交事务前,检查一下事务开始后,有没有新提交改变,如果没有就提交,如果有就放弃并重试。乐观并发控制类似自旋锁。乐观并发控制适用于低数据争用,写冲突比较少的环境。

    多版本并发控制可以结合基于锁的并发控制来解决写-写冲突,即MVCC+2PL,也可以结合乐观并发控制来解决写-写冲突。

    5.1InnoDB锁的特性

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

    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
    

    DML(Data Manipulation Language)数据操纵语言,对数据库中的数据进行一些简单操作,如insert、delete、update、select等。DML操作是可以手动控制事务的开启、提交和回滚的。
    DDL(Data Definition Language)数据定义语言,对数据库中的某些对象(例如database、table)进行管理,如create、alter和drop。DDL操作是隐性提交的,不能rollback。
    DDL有助于更改数据库的结构,而DML有助于管理数据库中的数据。

    参考:MySQL的锁机制和加锁原理

    展开全文
  • MySQL锁机制

    千次阅读 2021-04-17 20:47:16
    是计算机协调多个线程或者线程并发访问某一资源的机制。 2、表锁(偏读) 2.1、表锁的基本操作 手动添加表锁:lock table 表名字 read | write,表名字2 read | write ...... 释放表锁:unlock tables 查看...

    1、什么是锁?

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

    2、表锁(偏读)

      2.1、表锁的基本操作

    手动添加表锁lock table 表名字 read | write,表名字2 read | write  ......

    释放表锁unlock tables  

    查看表上加的锁:show open tables;

    对room表加锁就会显示:

      2.2、表锁两种模式

    表共享读锁、表独占写锁

    读锁:又称为共享锁,对同一份数据,多个读操作可以同时进行而互不影响

    若事务T对数据对象A加上读锁,事务T只能读A不能修改A其他事务只能对其加读锁不可以加写锁直到T释放A上的读锁为止

    当前session可以查询该表记录不能查询其他没有锁定的表

    其他session可以查询或者更新未锁定

    其他session不可以更新该表(被锁的表一直被阻塞除非session1对表释放锁

    写锁:又称为排他锁,当前写操作没有完成前,它会阻断其他写锁读锁。(写锁独占

    若事务T对数据对象A加上写锁,则只允许T读取和修改A,其他事务都不可以再对数据A再加任何类型的锁,直到T释放对A上的锁为止

    当前session可以对该表增删改,不可以操作其他未被锁的表;

    其他session则不可以进行任何操作,一直被阻塞,除非session1对该表释放锁。

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

       2.3、表锁支持的存储引擎

    支持innodb、MYIsam,偏向MYIsam存储引擎

      2.4、使用场景

    以查询为主、只有少量按索引条件更新数据的应用。

      2.5、特点

    开销小,加锁快,不会出现死锁,发生所冲突的概率最高,并发度最低

    3、行锁(偏写)

    偏向Innodb存储引擎,Innodb行锁是通过给索引上索引项来实现的,意味着:只有通过索引条件检索数据

    话句话说就是:在一张表中有索引才会使用行锁,否则innodb将使用表锁。

     3.1、行锁支持的存储引擎

    支持的存储引擎:InnoDB

    InnoDB与MyIsam的最大区别有两点:InnoDB支持事务InnoDB支持行级锁

    3.2、特点

     3.2、Innodb存储引擎的表在使用索引时-行锁演示

    sesson1session2

    更新但是session1没有提交:

    session2也是同样操作id=1的数据行:被阻塞,只能等待下面是阻塞久了

    session1提交更新:

    解除阻塞,更新正常进行

    提交commit

    如果session1操作id=1的数据行,session2操作id=2的数据行会怎么样??

    各自互不影响

    各自互不影响

      3.3、Innodb存储引擎的表在没有使用索引时-行锁变表锁

    无索引行锁升级为表锁

    session1session2

    在hangsuo表中没有索引:

    对id=1的数据行加行锁:

    表被锁了,对表的增删改对不可以

    等待

    提交commti:

    对之前的更新回滚,不提交。

      3.4、间隙锁的危害

    【什么是间隙锁】

    当使用范围条件而不是等值检索,并请求共享或者排它锁时,Innodb会给符合条件的已有的数据记录的索引项加锁,对于键值在条件范围中的记录但是表中没有记录,叫间隙。

    Innodb会对这个”间隙“加锁,这个机制就叫”间隙锁。

    危害:造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下可能对造成很大危害。

    3.5、面试题:如何锁定一行

    锁定某一行,其对该行的其他事务对其操作会被阻塞,除非锁定行的会话结束commit

    3.6、如何分析行锁定

    通过检查 innodb_row_lock状态变量分析系统上的行锁的争夺情况。

    mysql> show status like 'innodb_row_lock%';
    +-------------------------------+--------+
    | Variable_name                 | Value  |
    +-------------------------------+--------+
    | Innodb_row_lock_current_waits | 0      |
    | Innodb_row_lock_time          | 418625 |
    | Innodb_row_lock_time_avg      | 32201  |
    | Innodb_row_lock_time_max      | 51886  |
    | Innodb_row_lock_waits         | 13     |
    +-------------------------------+--------+
    5 rows in set (0.00 sec) 

    五项中红色标明的是比较重要的:

    Innodb_row_lock_current_wait :当前正在等待锁定的数量

    Innodb_row_lock_time:从系统启动到现在锁定总时间长度

    nnodb_row_lock_time_avg:每次等待所花的平均时间

    Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间

    Innodb_row_lock_waits  :系统启动后到现在总共等待的次数

    5、悲观锁与乐观锁

    (1)悲观锁:顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会block直到它拿到锁。

    传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁。

    (2)乐观锁: 顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制。

    乐观锁适用于多读的应用类型,这样可以提高吞吐量。

    展开全文
  • 腾讯云数据库负责人林晓斌说过:“我们面试MySQL同事时只考察两点,索引和锁”。言简意赅,MySQL锁的重要性不言而喻。 本文通过同事“侨总”的一场面试,带你通俗易懂的掌握MySQL各种锁机制,希望可以帮到你!

    小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL江湖路 | 专栏目录

      腾讯云数据库负责人林晓斌说过:“我们面试MySQL同事时只考察两点,索引和锁”。言简意赅,MySQL锁的重要性不言而喻。
      本文通过同事“侨总”的一场面试,带你通俗易懂的掌握MySQL各种锁机制,希望可以帮到你!近期会继续整理深入性的锁机制文章,有兴趣的老铁,记得关注一下,到时叫你❤️❤️~

      今天的主人公是我们公司同事侨总,传说中手上有10个比特币的男人。自从比特币大涨以来,养成了几个小爱好:周末听戏坐包厢,骑马酒吧滑雪场。

    在这里插入图片描述

      这不,前两天侨总又双叒叕出来体验面试了,晚上请我烧烤时跟我聊了聊这次有趣的面试经历,真是意犹未尽,趁他回味之余我又吃了十几串儿腰子和羊肉~ 嗯,真香!

      对不住,跑题了。。人到中年嘛,保温杯里泡枸杞之余总会。。。

      来不及解释了,快上车!

    大家好,我是陈哈哈的同事“侨总”,领导一般不敢喊我名字,都叫我小侨~

    下面是我的一次面试经历,面试官是技术经理和HR,大家吃好喝好~

    在这里插入图片描述

    侨总:马…小马哥好!

    面试官:你好,小侨啊,看你简历写着精通MySQL锁,你认为精通应该是啥水平呢?

    侨总:马哥我是个老实人,我认为精通就是,比面试官知道的多就完了。。

    面试官:(??怎么有种似曾相识的感觉?《听我讲完redo log、binlog原理,面试官老脸一红》

    面试官:行,那你先给我说说MySQL设计这个锁是干啥用的呀?

    侨总:数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,为了保证数据的一致性,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要机制。

      简单说,数据表就像公共厕所。emmm…换个下饭的说法,数据表就好比您开的一家酒店,而每行数据就像酒店的房间,如果大家随意进出,就会出现多人抢夺同一个房间的情况,而在房间上装上锁,申请到钥匙的人才可以入住并且将房间锁起来,其他人只有等他用完退房后才可以再次使用,这样保证了房间的一致性,方便酒店进行管理。

      MySQL锁机制的初衷便是如此,当然,MySQL数据库由于其自身架构的特点,存在多种数据存储引擎,每种存储引擎所针对的应用场景特点都不太一样,为了满足各自特定应用场景的需求,每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计,所以各存储引擎的锁定机制也有较大区别。

    面试官:嗯,那你说一下MySQL都分为哪些锁。

    在这里插入图片描述

    侨总

    1. 按锁粒度从大到小分类:表锁页锁行锁;以及特殊场景下使用的全局锁

    2. 如果按锁级别分类则有:共享(读)锁排他(写)锁意向共享(读)锁意向排他(写)锁

    3. 以及Innodb引擎为解决幻读等并发场景下事务存在的数据问题,引入的Record Lock(行记录锁)Gap Lock(间隙锁)Next-key Lock(Record Lock + Gap Lock结合)等;

    4. 还有就是我们面向编程的两种锁思想:悲观锁、乐观锁。

    面试官:袁芳你怎么看?

    HR小姐姐:。。。

    面试官:小侨啊,那你来谈一谈你对表锁、行锁的理解吧。

    表锁

    侨总:表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单,带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定,所以可以很好的避免困扰我们的死锁问题。

      当然,锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高,大大降低并发度。

      使用表级锁定的主要是MyISAM,MEMORY,CSV等一些非事务性存储引擎。

    行锁

    侨总:与表锁正相反,行锁最大的特点就是锁定对象的颗粒度很小,也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小,所以发生锁定资源争用的概率也最小,能够给予应用程序尽可能大的并发处理能力从而提高系统的整体性能。

      虽然能够在并发处理能力上面有较大的优势,但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小,所以每次获取锁和释放锁需要做的事情也更多,带来的消耗自然也就更大了。此外,行级锁定也最容易发生死锁

      使用行级锁定的主要是InnoDB存储引擎。

    • 适用场景:从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新数据的情况,同时又有并发查询的应用场景。

    页锁

      除了表锁、行锁外,MySQL还有一种相对偏中性的页级锁,页锁是MySQL中比较独特的一种锁定级别,在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间,所以获取锁定所需要的资源开销,以及所能提供的并发处理能力也同样是介于上面二者之间。另外,页级锁定和行级锁定一样,会发生死锁。

      使用页级锁定的主要是BerkeleyDB存储引擎。

    面试官:那全局锁是什么时候用的呢?

    全局锁

    侨总:首先全局锁,是对整个数据库实例加锁。使用场景一般在全库逻辑备份时。

      MySQL提供加全局读锁的命令:Flush tables with read lock (FTWRL)

      这个命令可以使整个库处于只读状态。使用该命令之后,数据更新语句、数据定义语句和更新类事务的提交语句等修改数据库的操作都会被阻塞。

    风险:

    1. 如果在主库备份,在备份期间不能更新,业务停摆
    2. 如果在从库备份,备份期间不能执行主库同步的binlog,导致主从延迟同步

      还有一种锁全局的方式:set global readonly=true ,相当于将整个库设置成只读状态,但这种修改global配置量级较重,和全局锁不同的是:如果执行Flush tables with read lock 命令后,如果客户端发生异常断开,那么MySQL会自动释放这个全局锁,整个库回到可以正常更新的状态。但将库设置为readonly后,客户端发生异常断开,数据库依旧会保持readonly状态,会导致整个库长时间处于不可写状态,试想一下微信只能看,不能打字~~

    HR小姐姐:那微信不就完蛋了?

    侨总:是啊,抓紧找老实人背锅!

    面试官:不错,你把这几种锁的侧重点都表述清楚了。那你再说一下你对不同级别的那几种锁的使用场景和理解吧?

    侨总:MySQL基于锁级别又分为:共享(读)锁排他(写)锁意向共享(读)锁意向排他(写)锁

    共享(读)锁、排他(写)锁、意向共享(读)锁、意向排他(写)锁

    侨总:对于共享(读)锁排他(写)锁,比如咱们住酒店,入住前顾客都是有权看房的,只看不住想白嫖都是可以的,前台小姐姐会把门给你打开。当然,也允许不同的顾客一起看(共享 读),比如和这位杀马特小伙子。

    在这里插入图片描述

      看房时房间相当于公共场所,小姐姐嘱咐不能乱涂乱画,也不能偷喝免费的矿泉水。。如果你觉得不错,偷偷跑到前台要定这间房,交钱后会给你这个房间的钥匙并将房间状态改为已入住,不再允许其他人看房(排他 写)。

      对了,当办理入住时前台小姐姐也会通知看房的杀马特小伙子说这间房已经有人定了!!等看房的杀马特小伙儿骂骂咧咧出门后,看到满头大汗的你,鄙夷着咽了一口口水,咳tui!然后你锁上门哼着歌儿,开始干那些见不得人的事儿~~直到你退房前,其他人无法在看你的房

      可见,读锁是可以并发获取的(共享的),而写锁只能给一个事务处理(排他的)。当你想获取写锁时,需要等待之前的读锁都释放后方可加写锁;而当你想获取读锁时,只要数据没有被写锁锁住,你都可以获取到读锁,然后去看房。

      另外还有意向读\写锁,严格来说他们并不是一种锁,而是存放表中所有行锁的信息。就像我们在酒店,当我们预定一个房间时,就对该行(房间)添加 意向写锁,但是同时会在酒店的前台对该行(房间)做一个信息登记(旅客姓名、男女、住多长时间、家里几头牛等)。大家可以把意向锁当成这个酒店前台,它并不是真正意义上的锁(钥匙),它维护表中每行的加锁信息,是共用的。后续的旅客通过酒店前台来看哪个房间是可选的,那么,如果没有意图锁,会出现什么情况呢?假设我要住房间,那么我每次都要到每一个房间看看这个房间有没有住人,显然这样做的效率是很低下的。杀马特小伙儿表示支持!

      读写锁、意向锁的兼容性如下所示;

    锁类型读锁写锁意向读锁意向写锁
    读锁兼容冲突兼容冲突
    写锁冲突冲突冲突冲突
    意向读锁兼容冲突兼容兼容
    意向写锁冲突冲突兼容兼容

    侨总:再回到MySQL原理上讲

    1 共享(读)锁(Share Lock)

      共享锁,又叫读锁,是读取操作(SELECT)时创建的锁。其他用户可以并发读取数据,但在读锁未释放前,也就是查询事务结束前,任何事务都不能对数据进行修改(获取数据上的写锁),直到已释放所有读锁。

      如果事务A数据B(1024房)加上读锁后,则其他事务只能对数据B上加读锁,不能加写锁。获得读锁的事务只能读数据,不能修改数据。

    SQL显示加锁写法:

    SELECTLOCK IN SHARE MODE;
    

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

    2 排他(写)锁(Exclusive Lock)

      排他锁又称写锁、独占锁,如果事务A数据B加上写锁后,则其他事务不能再对数据B加任何类型的锁。获得写锁的事务既能读数据,又能修改数据

    SQL显示加锁写法:

    SELECTFOR UPDATE;
    

      在查询语句后面增加FOR UPDATE,MySQL 就会对查询结果中的每行都加写锁,当没有其他线程对查询结果集中的任何一行使用写锁时,可以成功申请写锁,否则会被阻塞。另外成功申请写锁后,也要先等待该事务前的读锁释放才能操作。

    3 意向锁(Intention Lock)

      意向锁属于表级锁,其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁:

    • 意向共享锁(IS):表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁前必须先取得该表的IS锁;

    • 意向排他锁(IX):类似上面,表示事务准备给数据行加入排他锁,说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

      意向锁是 InnoDB 自动加的,不需要用户干预。

      再强调一下,对于INSERT、UPDATE和DELETE,InnoDB 会自动给涉及的数据加排他锁;对于一般的SELECT语句,InnoDB 不会加任何锁,事务可以通过以下语句显式加共享锁或排他锁。

    共享锁:SELECT … LOCK IN SHARE MODE;
    排他锁:SELECT … FOR UPDATE;

    面试官:(这小子有两下子)嗯,袁芳你怎么看?

    HR:通俗易懂,我听懂了~~

    面试官:好,那最后一个问题,你上面提到了乐观锁和悲观锁,谈谈你对它的看法吧。

    侨总:其实悲观锁和乐观锁,也并不是 MySQL 或者数据库中独有的概念,而是并发编程的基本概念。主要区别在于,操作共享数据时,“悲观锁”即认为数据出现冲突的可能性更大,而“乐观锁”则是认为大部分情况不会出现冲突,进而决定是否采取排他性措施。

      反映到 MySQL 数据库应用开发中,悲观锁一般就是利用类似 SELECT … FOR UPDATE 这样的语句,对数据加锁,避免其他事务意外修改数据。乐观锁则与 Java 并发包中的 AtomicFieldUpdater 类似,也是利用 CAS 机制,并不会对数据加锁,而是通过对比数据的时间戳或者版本号,来实现乐观锁需要的版本判断

      MySQL的多版本并发控制 (MVCC),其本质就可以看作是种乐观锁机制,而排他性的读写锁、两阶段锁等则是悲观锁的实现。

    面试官:好,小侨我看你对MySQL锁这块儿确实研究得比较透彻,连HR都听懂了,还是让我比较满意的。

    面试官:你平时有什么爱好么?

    侨总:我除了周末听戏坐包厢,骑马酒吧滑雪场。就是喜欢炒比特币啦!

    面试官:哦,不好意思,我们公司反对炒比特币的行为,回去等通知吧。

    侨总:???
    在这里插入图片描述

      说着面试官马经理走出了会议室,HR小姐姐表示我哪壶不开提哪壶,马老板高点买的比特币,现在泡沫炸了,亏成马,老板都差点亏没了。

    展开全文
  • Mysql 锁机制笔记1

    2022-08-03 19:40:23
    1.不可重复读,A事务在第一次读和第二次读之间,如果B对数据进行的修 2.更新丢失,A和B同时操作一个数据,最后执行完毕的会覆盖前一个的执 3.脏读,A事务添加
  •   数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则。 表级:开销小,加锁快;不会出现死锁;锁定粒度大,发生冲突的概率最高,并发度最低。 ...
  • MySQL锁机制全面解析

    千次阅读 多人点赞 2021-09-28 20:40:16
    一、的作用 数据库使用是为了支持对共享资源的并发访问,同时保证数据的完整性和一致性。 二、的类型 2.1 全局 全局意味着对整个数据库实例加上。通常使用的是全局读——Flush tables with read ...
  • Mysql锁机制及原理简析 一.前言 1.什么是锁? 锁是计算机协调多个进程或线程并发访问某一资源的机制。 锁保证数据并发访问的一致性、有效性; 锁冲突也是影响数据库并发访问性能的一个重要因素。 锁是Mysql在服务器...
  • MYSQL锁机制全揭秘

    2017-10-12 14:15:07
    MYSQL锁机制全揭秘,锁对数据库而言显得尤其重要,也更加复杂。
  • MYSQL锁机制全揭秘.pdf

    2019-05-30 02:09:51
    现在开发中越来越多会接触到各种锁,这里分享mysql锁机制,在开发中随着用户量增大并发量也随之增大,资源争用成为了开发者关注的焦点,锁的实现也必不可少。所以我们不得不深入了解锁的原理及机制,来优化我们的...
  • MySql锁机制

    千次阅读 2020-02-05 20:44:32
    MYSQL共有几种锁机制? 两种 共享锁(读锁):其他事务可以读,但不可以写 排它锁(写锁):其他事务不可以读也不可以写 MYSQL按锁粒度共分几种,有什么特点? 由小到大分三种 行锁:开销大,加锁慢;会出现死锁;锁定粒度...
  • 59 对MySQL锁机制再深入一步,共享锁和独占锁到底是什么?l.pdf
  • 行业-59 对MySQL锁机制再深入一步,共享锁和独占锁到底是什么?l.rar
  • 主要给大家介绍了关于MySQL InnoDB中锁机制的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用MySQL具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • Mysql锁机制

    万次阅读 2018-06-18 00:17:00
    对于任何一种数据库来说都需要有相应的锁定机制,所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点,存在多种数据存储引擎,每种存储引擎所针对的应用场景特点都不太一样,为了满足各自特定应用场景的需求...
  • MySQL高级——MySql锁机制(表锁)

    千次阅读 2021-03-24 22:13:04
    目录一、MySql表锁(偏读)特点二、建表SQL三、MySql手动增加表锁的案例分析四、MySql手动释放表锁的案例分析五、MySql手动增加表的读案例分析 一、MySql表锁(偏读)特点 偏向MyISAM存储引擎; 开销小,加锁快; 无...
  • Mysql锁机制之行锁、表锁、死锁

    千次阅读 2022-03-08 22:15:52
    乐观用的最多的就是数据的版本记录来体现 version ,其实就是一个标识。 例如:update test set a=a-1 where id=100 and a> 0; 对应的version就是a字段,并不一定非得要求有一个字段叫做version,要求的是有这个...
  • MySQL中的锁机制详细说明

    千次阅读 多人点赞 2020-06-30 17:45:40
    一、MySQL锁机制起步 锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能,实现了锁机制,MySQL中的锁是在服务器层或者存储引擎层实现的。 二、行锁与...
  • 是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 131,928
精华内容 52,771
关键字:

mysql锁机制

mysql 订阅
友情链接: DBSCAN.zip