精华内容
下载资源
问答
  • 目录一、建表SQL二、MySql无索引行锁升级为表锁基本演示 一、建表SQL 1、创建test_innodb_lock表的SQL CREATE TABLE test_innodb_lock ( a INT(11), b VARCHAR(16) )ENGINE=INNODB; INSERT INTO test_innodb_lock ...

    一、建表SQL

    1、创建test_innodb_lock表的SQL

    CREATE TABLE test_innodb_lock (
    a INT(11),
    b VARCHAR(16)
    )ENGINE=INNODB;
    
    INSERT INTO test_innodb_lock VALUES(1,'b2');
    INSERT INTO test_innodb_lock VALUES(3,'3');
    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');
    

    2、创建test_innodb_lock表a字段索引

    CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
    

    3、创建test_innodb_lock表b字段索引

    CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
    

    4、执行查询语句

    SELECT * FROM test_innodb_lock;
    

    在这里插入图片描述

    二、MySql引行锁升级为表锁基本演示

    1、 在客户端1 和客户端2中都手动关闭自动提交功能

    #关闭mysql自动提交
    SET autocommit=0;
    #开启mysql自动提交
    SET autocommit=1;
    

    在这里插入图片描述
    2、在客户端1中更新a=4的数据但不手动提交,然后在客户端2中更新a=8的数据也不手动提交。查看客户端1和客户端2是否能更新成功。

    #在客户端1中执行
    UPDATE test_innodb_lock SET b='4001' WHERE a =4;
    #然后在客户端2中执行
    UPDATE test_innodb_lock SET b='8001' WHERE a =8;
    

    在这里插入图片描述

    • 结论:在客户端1中更新a=4的数据但不手动提交,然后在客户端2中更新a=8的数据也不手动提交。客户端1和客户端2都能更新数据成功。

    3、在客户端1中更新b=4000的数据(b字段是varchar类型,但是不加单引号)但不手动提交,然后在客户端2中更新a=9的数据(b字段是varchar类型,但是加单引号)也不手动提交。查看客户端1和客户端2是否都能更新数据成功。

    #在客户端1中执行
    UPDATE test_innodb_lock SET a=41 WHERE b=4000;
    #然后在客户端2中执行
    UPDATE test_innodb_lock SET b='9002' WHERE a=9;
    

    在这里插入图片描述

    • 结论:在客户端1中更新b=4000的数据(b字段是varchar类型,但是不加单引号)但不手动提交,由于在column字段b上面建了索引,如果没有正常使用,会导致行锁变表锁比如没加单引号导致索引失效,行锁变表锁,虽然会更新成功,但是在客户端2中更新数据会被阻塞。
    展开全文
  • 1. 系统版本MySQL 5.7.25 ubuntu 16.042. 全局锁全局锁即对整个数据库实例加锁,使得整个库处于只读状态,会阻塞DML和DDL语句。使用如下命令(简称FTWRL)可数据库加全局...3. 表级锁3.1 表锁表锁用于用于单个表加...

    1. 系统版本

    MySQL 5.7.25 ubuntu 16.04

    2. 全局锁

    全局锁即对整个数据库实例加锁,使得整个库处于只读状态,会阻塞DML和DDL语句。

    使用如下命令(简称FTWRL)可为数据库加全局锁:

    flush tables with read lock;

    释放全局锁命令如下:

    unlock tables;

    此外,在客户端异常断开后,全局锁会自动释放。

    3. 表级锁

    3.1 表锁

    表锁用于用于为单个表加锁,表锁的类型分为读锁和写锁。

    3.1.1 加表锁的命令

    lock tables tb_name read/write;

    3.1.2 释放锁的命令

    unlock tables;

    3.1.3表锁对线程的阻塞作用

    当前线程(假设为线程A)对表加表锁后:

    (1)不能对没有加锁的表执行DML和DDL语句;

    (2)只能读加了read表锁的表;

    (3)能读写加了write表锁的表。

    在线程A没有释放表所前,对于后续的线程B:

    (1)无法读写由其他线程(此处为线程A)加了write表锁的表;

    (2)可读但无法写其他线程(此处为线程A)加了read表锁的表;

    (3)可读写没有加表锁的表。

    3.2 元数据锁

    元数据锁(meta data lock 简称MDL) 用于确保事务执行过程中表结构的稳定。MDL在MySQL 5.5中加入。MDL会在访问一个表时自动被加上。MDL分为写锁和读锁。

    3.2.1 MDL读锁

    对一个表执行DML语句时,会加上MDL读锁。MDL读锁之间不互斥。

    3.2.2 MDL写锁

    对一个表执行DDL语句时,会加上MDL写锁。MDL写锁和MDL读锁之间、MDL写锁之间互斥。例如,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完方可执行。

    4. 行锁

    故名思议,行锁即对行数据行进行加锁。行锁在引擎层由n各个数据库引进行擎独自实现。因此,如MyISAM没有行锁,InnoDB则有行锁。

    5 两阶段锁协议及死锁

    两阶段锁协议能提高数据库的事务处理效率。结合MVCC(多版本并发控制),能提高数据库的并发性能。但两阶段协议也形成了造成死锁的可能性,也为数据库带来为解决死锁问题而产生的额外性能开销甚至是数据库崩溃。

    以下说明基于MySQL的InnoDB引擎。

    5.1 并发控制

    并发控制主要有两种方式:锁和多版本并发控制(MVCC)。

    5.1.1 锁

    为事务所需涉及的表加上锁(为只需获数据的共享锁,为增删改数据的表加上排他锁),确保事务串行化,确保数据的正确性。但这影响了数据库的并发性能。

    5.1.2 MVCC(多版本并发控制)

    MVCC为数据库的并发性能提供了高效的解决方案。

    InnoDB的MVCC的实现方式为为每一个事务赋予一个自增且唯一的transaction ID。同时为涉及到事务增删改的数据行形成一个新版本,同时赋这个新版本一个row trx_id,row trx_id的值和对应事务的transaction ID值相同。

    在事务开始执行的时候,数据库会对整个库创建一个视图,对于在视图建时未提交的行数版本,该视图不可见。

    注意,对于已经创建但未提交的数据版本,视会通过row trx_id以及undo log回滚数据行之前已提交的版本。

    5.2两阶段锁协议(Two-Phase Locking,2PL)

    两阶段锁协议指的是事务的执行分为两个阶段,分别为扩展阶段(此阶段只能创建锁而不能释放锁,即需要加锁的时候才加锁)以及收阶段只能释而不加锁)。

    两阶段协保证了多个事务在并发的情况下等同于串行的执行,即事务的隔离性。

    5.2 死锁形成原因

    当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。

    5.3 死锁解决机制

    (1)超时

    直接进入等待,直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。

    (2)死锁主动检查

    发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为on,表示开启死锁检测。

    注意,死锁检查会消耗额外资源,若并发的线过多,则有可能由死锁检测而消耗过多资源而导数据库崩溃。

    展开全文
  • 众所周知,MySQL 的 InnoDB 存储引擎支持事务,支持行级锁(innodb的行锁是通过给...经过我操作验证,得出行锁升级为表锁的原因之一是: SQL 语句中未使用到索引,或者说使用的索引未被数据库认可(相当于没有使用...

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

    经过我操作验证,得出行锁升级为表锁的原因之一是: SQL 语句中未使用到索引,或者说使用的索引未被数据库认可(相当于没有使用索引)。

    我相信,MySQL InnoDB 存储引擎引发表锁的原因肯定不止一个因素,针对其解决方法也不是只有一种。

    据掘金上另一位作者【Blink-前端】,提出行锁升级为表锁与 事务的隔离级别 有关,并给出了事例。当然,我同意这个说法,因为事务的隔离性是靠加锁来实现的,而加锁势必会影响并发。本篇只针对 索引影响并发 作出说明,并特别希望有朋友能提出质疑并给出独特见解,万分感谢。

    普通索引

    既然谈及索引是影响并发的决定因素之一,那我们就来了解一下索引这位主角。

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

    为什么我会想到 “普通索引对并发有影响”?这源自【掘金】微信群抛出的一个问题:

    mysql 5.6 在 update 和 delete 的时候,where 条件如果不存在索引字段,那么这个事务是否会导致表锁?

    有人回答:

    只有主键和唯一索引才是行锁,普通索引是表锁。

    我针对 “普通索引是表锁” 进行了验证,结果发现普通索引并不一定会引发表锁,在普通索引中,是否引发表锁取决于普通索引的高效程度。

    上文提及的“高效”是相对主键和唯一索引而言,也许“高效”并不是一个很好的解释,明白在一般i情况下,“普通索引”效率低于其他两者即可。

    属性值重复率高

    为了突出效果,我将“普通索引”建立在一个“值重复率”高的属性下。以相对极端的方式,扩大对结果的影响。

    我会创建一张“分数等级表”,属性有“id”、“score(分数)”、“level(等级)”,模拟一个半自动的业务——“分数”已被自动导入,而“等级”需要手工更新。

    操作步骤如下:

    1. 取消 MySQL 的 事务自动提交
    2. 建表,id自增,并给“score(分数)”创建普通索引
    3. 插入分数值,等级为 null
    4. 开启两个事务 session_1、session_2,两个事务以“score”为条件指定不同值,锁定数据
    5. session_1 和 session_2 先后更新各自事务锁定内容的“level”
    6. 观察数据库对两个事务的响应

    取消 事务自动提交

    mysql> set autocommit = off;Query OK, 0 rows affected (0.02 sec)mysql> show variables like "autocommit";+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| autocommit               | OFF   |+--------------------------+-------+1 rows in set (0.01 sec)
    

    建表、创建索引、插入数据:

    DROP TABLE IF EXISTS `test1`;CREATE TABLE `test1` (`ID`  int(5) NOT NULL AUTO_INCREMENT ,`SCORE`  int(3) NOT NULL ,`LEVEL`  int(2) NULL DEFAULT NULL ,PRIMARY KEY (`ID`))ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;ALTER TABLE `test2` ADD INDEX index_name ( `SCORE` );INSERT INTO `test1`(`SCORE`) VALUE (100);……INSERT INTO `test1`(`SCORE`) VALUE (0);……
    

    “SCORE” 属性的“值重复率”奇高,达到了 50%,剑走偏锋:

    mysql> select * from test1;+----+-------+-------+| ID | SCORE | LEVEL |+----+-------+-------+|  1 |   100 | NULL  ||  2 |     0 | NULL  ||  5 |   100 | NULL  ||  6 |   100 | NULL  ||  7 |   100 | NULL  ||  8 |   100 | NULL  ||  9 |   100 | NULL  || 10 |   100 | NULL  || 11 |   100 | NULL  || 12 |   100 | NULL  || 13 |   100 | NULL  || 14 |     0 | NULL  || 15 |     0 | NULL  || 16 |     0 | NULL  || 17 |     0 | NULL  || 18 |     0 | NULL  || 19 |     0 | NULL  || 20 |     0 | NULL  || 21 |     0 | NULL  || 22 |     0 | NULL  || 23 |     0 | NULL  || 24 |   100 | NULL  || 25 |     0 | NULL  || 26 |   100 | NULL  || 27 |     0 | NULL  |+----+-------+-------+25 rows in set
    

    开启两个事务(一个窗口对应一个事务),并选定数据:

    -- SESSION_1,选定 SCORE = 100 的数据mysql> BEGIN;SELECT t.* FROM `test1` t WHERE t.`SCORE` = 100 FOR UPDATE;Query OK, 0 rows affected+----+-------+-------+| ID | SCORE | LEVEL |+----+-------+-------+|  1 |   100 | NULL  ||  5 |   100 | NULL  ||  6 |   100 | NULL  ||  7 |   100 | NULL  ||  8 |   100 | NULL  ||  9 |   100 | NULL  || 10 |   100 | NULL  || 11 |   100 | NULL  || 12 |   100 | NULL  || 13 |   100 | NULL  || 24 |   100 | NULL  || 26 |   100 | NULL  |+----+-------+-------+12 rows in set
    

    再打开一个窗口:

    -- SESSION_2,选定 SCORE = 0 的数据mysql> BEGIN;SELECT t.* FROM `test1` t WHERE t.`SCORE` = 0 FOR UPDATE;Query OK, 0 rows affected+----+-------+-------+| ID | SCORE | LEVEL |+----+-------+-------+|  2 |     0 | NULL  || 14 |     0 | NULL  || 15 |     0 | NULL  || 16 |     0 | NULL  || 17 |     0 | NULL  || 18 |     0 | NULL  || 19 |     0 | NULL  || 20 |     0 | NULL  || 21 |     0 | NULL  || 22 |     0 | NULL  || 23 |     0 | NULL  || 25 |     0 | NULL  || 27 |     0 | NULL  |+----+-------+-------+13 rows in set
    

    session_1 窗口,更新“LEVEL”失败:

    mysql> UPDATE `test1` SET `LEVEL` = 1 WHERE `SCORE` = 100;1205 - Lock wait timeout exceeded; try restarting transaction
    

    在之前的操作中,session_1 选择了 SCORE = 100 的数据,session_2 选择了 SCORE = 0 的数据,看似两个事务井水不犯河水,但是在 session_1 事务中更新自己锁定的数据失败,只能说明在此时引发了表锁。别着急,刚刚走向了一个极端——索引属性值重复性奇高,接下来走向另一个极端。

    属性值重复率低

    还是同一张表,将数据删除只剩下两条,“SCORE” 的 “值重复率” 为 0:

    mysql> delete from test1 where id > 2;Query OK, 23 rows affectedmysql> select * from test1;+----+-------+-------+| ID | SCORE | LEVEL |+----+-------+-------+|  1 |   100 | NULL  ||  2 |     0 | NULL  |+----+-------+-------+2 rows in set
    

    关闭两个事务操作窗口,重新开启 session_1 和 session_2,并选择各自需要的数据:

    -- SESSION_1,选定 SCORE = 100 的数据mysql> BEGIN;SELECT t.* FROM `test1` t WHERE t.`SCORE` = 100 FOR UPDATE;Query OK, 0 rows affected+----+-------+-------+| ID | SCORE | LEVEL |+----+-------+-------+|  1 |   100 | NULL  |+----+-------+-------+1 row in set-- -----------------新窗口----------------- ---- SESSION_2,选定 SCORE = 0 的数据mysql> BEGIN;SELECT t.* FROM `test1` t WHERE t.`SCORE` = 0 FOR UPDATE;Query OK, 0 rows affected+----+-------+-------+| ID | SCORE | LEVEL |+----+-------+-------+|  2 |     0 | NULL  |+----+-------+-------+1 row in set
    

    session_1 更新数据成功:

    mysql> UPDATE `test1` SET `LEVEL` = 1 WHERE `SCORE` = 100;Query OK, 1 row affectedRows matched: 1  Changed: 1  Warnings: 0
    

    相同的表结构,相同的操作,两个不同的结果让人出乎意料。第一个结果让人觉得“普通索引”引发表锁,第二个结果推翻了前者,两个操作中,唯一不同的是索引属性的“值重复率”。根据 单一变量 证明法,可以得出结论:当“值重复率”低时,甚至接近主键或者唯一索引的效果,“普通索引”依然是行锁;当“值重复率”高时,MySQL 不会把这个“普通索引”当做索引,即造成了一个没有索引的 SQL,此时引发表锁

    小结

    索引不是越多越好,索引存在一个和这个表相关的文件里,占用硬盘空间,宁缺勿滥,每个表都有主键(id),操作能使用主键尽量使用主键。

    同 JVM 自动优化 java 代码一样,MySQL 也具有自动优化 SQL 的功能。低效的索引将被忽略,这也就倒逼开发者使用正确且高效的索引。

    展开全文
  • 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引擎中将行锁升级为表锁 **众所周知,MySQL 的 InnoDB 存储引擎支持事务,支持行级锁(innodb的行锁是通过给索引项加锁实现的)。得益于这些特性,数据库支持高并发。如果 InnoDB 更新数据使用的不是...
  • MySql的写语句中,给表列赋值与表类型不符合时,MySql底层的优化器发挥作用,会做一个强制类型转化,此时能正常操作,但会导致行锁升级为表锁。示例如下以student表为例,表字段类型:表内容如下: 打开两个...
  • 行锁是建立在索引字段的基础上,如果行锁定的列不是索引列则会升级为表锁,我这两个测试的查询条件有重复字段,其实用两个值来测试也是同样的效果。 四、创建索引的情况下进行测试 主要测试两个方面:一是索引...

空空如也

空空如也

1 2 3 4 5 6
收藏数 107
精华内容 42
关键字:

mysql行锁升级为表锁

mysql 订阅