精华内容
下载资源
问答
  • MySql类型转换导致行锁升级为表锁

    千次阅读 2018-08-05 10:58:06
    MySql的写语句中,给表列赋值与表类型不符合时,MySql底层的优化器发挥作用,做一个强制类型转化,此时能正常操作,但导致行锁升级为表锁。示例如下 以student表为例,表字段类型: 表内容如下: 打开...

    在MySql的写语句中,给表列赋值与表类型不符合时,MySql底层的优化器发挥作用,会做一个强制类型转化,此时能正常操作,但会导致行锁升级为表锁。示例如下
    以student表为例,表字段类型:
    这里写图片描述
    表内容如下:
    这里写图片描述

    打开两个session会话窗口,并把两个会话窗口中的MySql的自动提交模式改为手动提交

    >set autocommit=false;

    这里写图片描述
    在会话窗口1中执行更新语句,但不提交事务。age列在建表时指定的是int类型,此地更新语句中用字符串’100’进行赋值,在MySql的优化器中会自动把字符串’100’强制转化为整形100,然后再执行SQL检索。

    >update student set class=3 where age='100'

    然后再会话窗口2中对另外没关系的数据执行更新操作

    >update student set age=28 where name='lzj';

    正常情况下,两条SQL语句操作的行数据不同,执行起来会互不影响,但实际会话1中的更新操作阻塞了会话2中的更新操作
    这里写图片描述
    会话1中执行了更新操作,但没有执行事务提交,事务的隔离级别为Read Committed,所以在会话2中还看不到会话1中更新后的结果。但在回话2中执行对其它行数据更新操作时,出现了阻塞。可见会话1中的SQL语句的赋值出现了强转,导致会话1由行锁升级为表锁,锁住了整个student表,因而会话2中的SQL阻塞。下面对会话1中的更新操作执行事务提交,那么会话2中的更新操作就会继续执行了
    这里写图片描述
    对会话1中的更新操作执行commit手动提交事务后,会话1释放掉student的表锁,会话2中的更新操作可以继续执行。
    最后对会话2中的更新也执行commit事务提交,两条SQL都更新完毕,student表内容如下:
    这里写图片描述

    从上述案例观知,SQL语句赋值与表列类型不匹配时,MySql的优化器强制转化为匹配的类型,导致行锁升级为表锁。所以开发中一定要注意类型的匹配,避免行锁升级为表锁,影响并发性能。

    展开全文
  • Mysql锁分为表锁行锁表锁虽然开销小,锁表快,但高并发性能低。行锁虽然开销大,锁表慢,但是并发性能高。InnoDB采用的行锁,支持事务;MyISAM采用表锁不支持事务 InnoDB行锁会表锁吗? 案例分析: 创建表...

    锁机制

    Mysql锁分为表锁和行锁,表锁虽然开销小,锁表快,但高并发下性能低。行锁虽然开销大,锁表慢,但是并发性能高。InnoDB采用的行锁,支持事务;MyISAM采用表锁不支持事务

    InnoDB行锁会变表锁吗?

    案例分析:
    创建表结构

    CREATE TABLE `user` (
      `ID_` bigint(20) NOT NULL AUTO_INCREMENT,
      `AGE_` int(11) DEFAULT NULL,
      `NAME_` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`ID_`),
      KEY `AGE_AND_NAME_` (`AGE_`,`NAME_`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4
    

    注意:AGE_和NAME_采用了组合索引
    案例一: 通过主键修改

    时间顺序Transaction-ATransaction-B
    1开启事务
    2update user set NAME_=‘测试1.1’ where ID_=1;
    3开启事务
    4update user set NAME_=‘测试2.1’ where ID_=2;
    5修改ID=2,修改成功
    6update user set NAME_=‘测试1.2’ where ID_=1;
    7修改ID=1,阻塞中
    8提交事务
    9修改ID=1的命令自动执行,耗时10s
    # Transaction-A
    set autocommit = 0;
    update user set NAME_='测试1.1' where ID_=1;
    #10S稍后提交
    commit;
    
    # Transaction-B
    set autocommit = 0;
    update user set NAME_='测试2.1' where ID_=2;  #命令1
    Affected rows : 0, Time: 0.00sec
    update user set NAME_='测试1.2' where ID_=1;  #命令2 
    Affected rows : 1, Time: 10.13sec
    

    由上图可知 事务A开启事务后,事务B中 命令1正常修改,但是命令2处于阻塞当中。事务A提交事务之后,命令2自动执行。

    结论:多个事务操作同一行数据时,后来的事务处于阻塞等待状态。采用了行锁的方式

    案例二: 通过唯一索引修改
    案例一通过主键修改,下面通过唯一索引修改

    #Transaction-A
    set autocommit = 0;
    update user set NAME_='测试1.1' where AGE_=1;
    #10S稍后提交
    commit;
    
    
    #Transaction-B
    set autocommit = 0;
    update user set NAME_='测试2.2' where AGE_=2;  #命令1
    Affected rows : 0, Time: 0.00sec
    update user set NAME_='测试1.2' where AGE_=1;  #命令2
    Affected rows : 1, Time: 10.96sec
    

    和案例一的结论一样,都采用了行锁的方式

    案例三

    我们把AGE_去掉索引,只保留NAME_索引

    #Transaction-A
    set autocommit = 0;
    update user set NAME_='测试1.1' where AGE_=1;
    #10S稍后提交
    commit;
    
    
    #Transaction-B
    set autocommit = 0;
    update user set NAME_='测试2.2' where AGE_=2;  #命令1
    Affected rows : 0, Time: 10.61sec 
    update user set NAME_='测试1.2' where AGE_=1;  #命令2
    Affected rows : 1, Time: 0.00sec
    

    通过案例3看到,事务A开始事务,事务B中命令1处于阻塞当中,事务A提交事务后,命令1自动执行,命令2自动执行

    总结:

    通过上面三个案例得知,InnoDB通过索引查询,使用的行锁,如果非索引查询会采用表锁。

    InnoDB默认是行锁,前提条件是建立在索引之上的。如果筛选条件没有建立索引,会降级到表锁。即如果where条件中的字段都加了索引,则加的是行锁;否则加的是表锁。

    InnoDB行锁是通过给索引上的索引项加锁来实现的。所以,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。其他注意事项:

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

    锁冲突 死锁

    行锁锁的一行数据,所以会发生死锁。
    比如:事务A锁住了第1行,事务B锁住了第2行,此时事务A请求锁住第2行,会阻塞到事务B释放第2行,同时事务B也请求锁住第一行,也会阻塞到事务A释放第1行,就会造成死活,会产生Deadlock错误

    锁的类型

    1. 锁分 共享锁 和 排它锁
      共享锁(S):允许一个事务去读一行,阻止其他事务获取相同数据集的排它锁。
      排它锁(X):允许获得排它锁的事务更新数据,阻止其他事务获取相同数据集的共享锁和排它锁

      InnoDB对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;
      主动加锁的方式:

      • 共享锁(S):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
      • 排他锁(X) :SELECT * FROM table_name WHERE … FOR UPDATE
    2. 意向锁
      意向锁是表级别的锁。分为意向读锁, 意向写锁。意向锁树数据库层面控制的;
      Mysql为了解决行锁和表锁共存的问题,引入了意向锁。意向读锁和意向写锁。

    mysql数据库意向锁意义

    展开全文
  • mysql 行锁升级为表锁

    2021-07-20 15:40:24
    总所周知,MySQL 的 InnoDB 存储引擎支持事务,支持行级锁(InnoDB 的行锁是通过给索引项加锁实现的)。得益于这些特性,数据库支持高并发。如果 InnoDB 更新数据使用的不是行锁,而是表锁呢?是的,InnoDB 其实很...

    行锁升级为表锁

    总所周知,MySQL 的 InnoDB 存储引擎支持事务,支持行级锁(InnoDB 的行锁是通过给索引项加锁实现的)。得益于这些特性,数据库支持高并发。如果 InnoDB 更新数据使用的不是行锁,而是表锁呢?是的,InnoDB 其实很容易就升级为表锁,届时并发性将大打折扣了。

    常用的索引有三类:主键、唯一索引、普通索引。主键不由分说,自带最高效率的索引属性;唯一索引指的是该属性值重复率为0,一般可作为业务主键,例如:学号;普通索引与前者不同的是,属性值的重复率大于0,不能作为唯一指定条件,例如:学生姓名。

    下面我们来看下使用索引与不使用索引的情况下加锁会不会升级为表锁。


    在不使用索引的情况下加锁

    事务顺序事务1事务2
    begin;
    select * from user where age = 17 for update;
    begin;
    select * from user where age = 15 for update;
    commit;commit;

    运行结果:

    在这里插入图片描述

    在不给 age 字段加索引的情况下进行排它锁的加锁操作,可以看到尽管加锁的数据是不同的,但是事务2在加锁时出现了锁等待现象。说明此时事务1从行级锁升级为表锁,导致事务2在给 age = 15 的数据加锁时出现了锁等待现象。


    在使用普通索引的情况进行加锁

    alter table user add index idx_age(age); --给age字段加个索引

    事务顺序事务1事务2
    begin;
    select *from user where age = 17 for update;
    begin;
    select * from user where age = 15 for update;
    commit;commit;

    运行结果:

    在这里插入图片描述
    在加了索引之后,再一次进行以上操作。可以看到,user 表不在进行表锁,那是因为行锁是建立在索引字段的基础上,如果行锁锁定的列表锁索引列则会升级为表锁。


    范围性查询测试

    事务顺序事务1事务2
    begin;
    select * from user where age between 13 and 16 for update;
    begin;
    select * from user where age = 17 for update;
    commit;commit;

    运行结果:

    在这里插入图片描述
    当要进行加锁的数据不确定时,也一样会升级为表锁。

    总结:
    行锁是建立在索引的基础上。
    普通索引的数据重复率过高导致索引失效,行锁升级为表锁。

    展开全文
  • 事务的隔离级别为:可重复读时, 如果有索引(包括主键索引),以索引列为条件更新数据,存在间隙锁,行锁,页锁,而锁住一些行。 如果没有索引,更新数据时锁住整张表。 ...

    事务的隔离级别为:可重复读时,

    如果有索引(包括主键索引),以索引列为条件更新数据,会存在间隙锁,行锁,页锁,而锁住一些行。

    如果没有索引,更新数据时会锁住整张表。

    事务隔离级别为:串行化时,读写数据都会锁住整张表。(一次只能一个连接玩表)

    展开全文
  • 我们前几篇讲了索引是什么,如何使用explain分析索引使用情况,如何去优化索引,以及show profiles分析SQL语句执行资源消耗的学习。今天我们来讲讲MySQL的各种锁,这里存储引擎我们使用InnoDB; 准备工作 创建表 ...
  • InnoDB 引擎,其中的支持行锁就是 InnoDB 适合多并发优势所在,但是行锁的一些细节没有深入理解过的话,可能造成一定的误解,造成“「看似命中索引,走行锁,结果却是表锁,最终导致锁等待情况」”。 一、「InnoDB...
  • mysql什么情况下会触发表锁

    千次阅读 2020-06-13 01:08:16
    锁是计算机协调多个进程或线程并发访问某一...本章我们着重讨论MySQL锁机制的特点,常见的锁问题,以及解决MySQL锁问题的一些方法或建议。 MySQL锁概述 相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是
  • mysql> update test_innodb_lock set b='4000' where a = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 第一个客户端,对 a = 9 执行更新操作,操作成功 mysql> update...
  • Mysql InnoDB引擎的行锁表锁

    万次阅读 多人点赞 2018-08-14 22:55:29
    MySQL 支持全部4个隔离级别,但在具体实现时,有一些特点,比如在一些隔离级别是采用MVCC一致性读,但某些情况下又不是,这些内容在后面的章节中将做进 一步介绍。 mysql默认的事务处理级别是'REPEATABLE-...
  • mysql innodb引擎什么时候表锁什么时候行锁? InnoDB基于索引的行锁 InnoDB行锁是通过索引上的索引项来实现的,这一点MySQL与Oracle不同,后者是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味...
  • InnoDB默认的行锁可以使得操作不同行时不会产生相互影响、不会阻塞,从而很好的解决了多事务和并发的问题。但是,那得基于一个前提,即 Where 条件中使用上了索引;反之,如果没有使用上索引,则是全表扫描、全部...
  • 通过案例实操来演示MySQL中行锁、表锁、间隙锁的问题是怎么产生的。
  • 前言我们前几篇讲了索引是什么,如何使用explain分析索引使用情况,如何去优化索引,以及show profiles分析SQL语句执行资源消耗的学习。今天我们来讲讲MySQL的各种锁,这里存储引擎我们使用InnoDB准备工作创建表 tb_...
  • MySQL查询之你用的是行锁还是表锁

    千次阅读 2018-03-27 14:41:59
    1.创建engine=innodb的表session1(连接1)中set ...为什么修改了之后,没有提交,再查询还是对的,原来是在同一个session中,数据隔离级别设置这样。具体执行步骤:1. 在session1中 执行update,但是没有co...
  • MySQL】innodb行锁表锁

    千次阅读 2019-07-30 16:45:46
    关于下文中的表格看法,按照时间轴排列的 ...在不通过索引(主键)条件查询的时候,InnoDB是表锁而不是行锁。 创建表 创建一个没有主键的表 CREATE TABLE `test_no_key` ( `f1` int(11) NOT NULL , `f2...
  • mysql 表锁行锁下

    2020-03-11 18:24:38
    可重复读: A session执行: B sesion执行: ...where id = 1,balance没有变成400-50=350,lilei的balance值用的是步骤 (2)中的350来算的,所以是300,数据的一致性倒是没有被破坏。可重复...
  • 而导致行锁变为表锁情况之一就是: SQL的更新(update)或者删除(delete)语句中未使用到索引,导致在InnoDB在对数据进行相应操作的时候必须把整个表锁起来进行检索(表锁)。而如果使用了索引的话,InnoDB只...
  • MySQL:全局锁,表锁行锁

    千次阅读 2019-05-09 10:17:30
    根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。 1、全局锁 全局锁即给整个数据库实例加锁。主要的使用场景:做全库逻辑备份的时候,为了保证备份期间的库在同一个逻辑时间点,即保证一致...
  • MySQL深入浅出之全局锁和表锁行锁

    千次阅读 2021-07-05 09:31:22
    行锁 mysql行锁属于各个引擎自己的功能 不是所有的引擎都有行锁, 比如MyISam, 这也是InnoDB替代他的原因 不支持行锁, 意味着并发控制只能使用表锁, 同一张表任意时刻只能有一个更新执行, 影响业务并发度 4.1 ...
  • 有一些前提条件: 这个表有索引: 表的结构: 当一个事务对表的一行进行修改: updatetest_innodb_lock set a=4 where b=4000; ...发生了类型转化,索引失效,导致 行锁变为表锁; ...
  • 下面几节我们重点介绍MySQL表锁和 InnoDB行锁的问题,由于BDB已经被InnoDB取代,即将成为历史,在此就不做进一步的讨论了。   MyISAM表锁 MyISAM存储引擎只支持表锁,这也是MySQL开始几个版本中唯一支持...
  • Mysql的InnoDB存储引擎支持...而导致行锁变为表锁情况之一就是:  SQL的更新(update)或者删除(delete)语句中未使用到索引,导致在InnoDB在对数据进行相应操作的时候必须把整个表锁起来进行检索(表锁)。而...
  • Mysql锁机制,行锁表锁

    千次阅读 2021-11-13 12:04:42
    文章目录 锁是计算机协调多个线程访问同一个系统资源的机制 锁的分类: 从数据的操作类型分为:读锁和写锁 从数据的操作粒度分为:行锁表锁
  • 窗口 B 的变化 可以看到这个时候窗口 B 已经执行成功了 表锁 当索引失效的时候,行锁会升级成表锁,索引失效的其中一个方法是对索引自动 or 手动的换型。a 字段本身是 integer,我们加上引号,就变成了 String,这...
  • 文章目录1、概述2、行级锁2.1 共享锁和排他锁2.2 意向共享锁和意向排他锁2.3 间隙锁(Next-Key锁)2.4 举例2.5 死锁和死锁检测3、表级锁3.1 表锁3.2 元数据锁(meta data lock...
  • 行锁表锁mysql 的 InnoDB引擎支持行锁,与Oracle不同,mysql行锁是通过索引加载的,即是行锁是加在索引响应的行上的,要是对应的SQL语句没有走索引,则全表扫描, 行锁则无法实现,取而代之的是表锁表锁...
  • Mysql行锁表锁问题

    2021-05-21 18:53:16
    文章目录一、Mysql锁问题1.锁概述2.锁分类3. Mysql 锁 一、Mysql锁问题 1.锁概述 锁是计算机协调多个进程或线程并发访问某一资源的...1) 表锁:操作时,锁定整个表。 2) 行锁:操作时,锁定当前操作行。 从对数据
  • 文章目录前言全局锁表锁行锁小结 前言 数据库设计锁的初衷是处理并发问题,作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。 根据...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 7,416
精华内容 2,966
关键字:

mysql什么情况下行锁会变成表锁

mysql 订阅