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

    2019-07-14 18:20:25
    1、 多个事务操作同一行数据时,后来的事务处于阻塞等待状态。...并且该索引不能失效,否则都会从行锁升级表锁。没有索引的字段更新会变为表锁。 3、间歇锁 是指update where id >6 大于6 这个间隙。危害...

    1、 多个事务操作同一行数据时,后来的事务处于阻塞等待状态。这样可以避免了脏读等数据一致性的问题。后来的事务可以操作其他行数据,解决了表锁高并发性能低的问题

     

    2、InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。并且该索引不能失效,否则都会从行锁升级为表锁。没有索引的字段更新会变为表锁。

     

    3、间歇锁 是指update  where id >6   大于6 这个间隙。危害(坑):若执行的条件是范围过大,则InnoDB会将整个范围内所有的索引键值全部锁定,很容易对性能造成影响

     4 for update 排他锁

     

     

    1 InnoDB 支持表锁和行锁,使用索引作为检索条件修改数据时采用行锁,否则采用表锁。
    2 InnoDB 自动给修改操作加锁,给查询操作不自动加锁
    3 行锁可能因为未使用索引而升级为表锁,所以除了检查索引是否创建的同时,也需要通过explain执行计划查询索引是否被实际使用。
    4 行锁相对于表锁来说,优势在于高并发场景下表现更突出,毕竟锁的粒度小。
    5 当表的大部分数据需要被修改,或者是多表复杂关联查询时,建议使用表锁优于行锁。
    6 为了保证数据的一致完整性,任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库的并发处理能力和性能。

     

    参考地址:https://www.cnblogs.com/itdragon/p/8194622.html

    转载于:https://www.cnblogs.com/brucetie/p/8259853.html

    展开全文
  • 目录一、建表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)可为数据库加全局锁:flush tables with read lock;释放全局锁...

    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-行锁表锁

    2019-06-28 16:16:24
    为日常整理,可能会有些重复. 行锁表表锁 : 1> 多个事务操作同一行数据时,后来的事务处于阻塞等待状态。这样可以避免了脏读等数据...并且该索引不能失效,否则都会从行锁升级表锁。 3> 间隙锁 : 当我...

    为日常整理,可能会有些重复.

    行锁表表锁 : 
          1> 多个事务操作同一行数据时,后来的事务处于阻塞等待状态。这样可以避免了脏读等数据一致性的问题。后来的事务可以操作其他行数据,解决了表锁高并发性能低的问题。
          2> InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。并且该索引不能失效,否则都会从行锁升级为表锁。
          3> 间隙锁 : 
    	 当我们用范围条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做”间隙(GAP)”。InnoDB也会对这个”间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。
             危害(坑):若执行的条件是范围过大,则InnoDB会将整个范围内所有的索引键值全部锁定,很容易对性能造成影响。
          4> 排他锁 : 
             排他锁,也称写锁,独占锁,当前写操作没有完成前,它会阻断其他写锁和读锁。
          5> 共享锁 : 
             共享锁,也称读锁,多用于判断数据是否存在,多个读操作可以同时进行而不会互相影响。当如果事务对读锁进行修改操作,很可能会造成死锁。
    
    行锁优化 : 
          1> 尽可能让所有数据检索都通过索引来完成,避免无索引行或索引失效导致行锁升级为表锁。
          2> 尽可能避免间隙锁带来的性能下降,减少或使用合理的检索范围。
          3> 尽可能减少事务的粒度,比如控制事务大小,而从减少锁定资源量和时间长度,从而减少锁的竞争等,提供性能。
          4> 尽可能低级别事务隔离,隔离级别越高,并发的处理能力越低。
    
    表锁 : 
         表锁的优势:开销小;加锁快;无死锁
    	 表锁的劣势:锁粒度大,发生锁冲突的概率高,并发处理能力低
    	 加锁的方式:自动加锁。查询操作(SELECT),会自动给涉及的所有表加读锁,更新操作(UPDATE、DELETE、INSERT),会自动给涉及的表加写锁。也可以显示加锁
    	    共享读锁:lock table tableName read;
    	    独占写锁:lock table tableName write;
    	    批量解锁:unlock tables;
    
    共享读锁 :  对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读操作,但会阻塞对同一表的写操作。只有当读锁释放后,才能执行其他进程的写操作。在锁释放前不能取其他表
    独占写锁 :  对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作。在锁释放前不能写其他表
          
    即便你在条件中使用了索引字段,MySQL会根据自身的执行计划,考虑是否使用索引(所以explain命令中会有possible_key 和 key)。如果MySQL认为全表扫描效率更高,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。
    第一种情况:全表更新。事务需要更新大部分或全部数据,且表又比较大。若使用行锁,会导致事务执行效率低,从而可能造成其他事务长时间锁等待和更多的锁冲突。
    第二种情况:多表查询。事务涉及多个表,比较复杂的关联查询,很可能引起死锁,造成大量事务回滚。这种情况若能一次性锁定事务涉及的表,从而可以避免死锁、减少数据库因事务回滚带来的开销。
    
    行锁的劣势:开销大;加锁慢;会出现死锁
    行锁的优势:锁的粒度小,发生锁冲突的概率低;处理并发的能力强
    加锁的方式:自动加锁。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁;对于普通SELECT语句,InnoDB不会加任何锁;当然我们也可以显示的加锁:
    共享锁:select * from tableName where … + lock in share more
    排他锁:select * from tableName where … + for update
    
    InnoDB和MyISAM的最大不同点有两个:
         1> InnoDB支持事务(transaction)
    	 2> 默认采用行级锁。
    
    for update : 
          1> 当for update的字段为索引或者主键的时候,只会锁住索引或者主键对应的行。
          2> 当for update的字段为普通字段的时候,Innodb会锁住整张表。
    
    展开全文
  • 1.查看存储引擎 show engines; 2.数据结构 ...4.根据非索引条件更新,A事务产生表锁,B事务执行被阻塞。 总结:InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。当更新条件没有使用索
  • 表锁(偏读) 特点: 案例分析: ...无索引 (索引失效)导致 行锁升级表锁 在有索引的列操作,导致索引失效,那么行锁就会升级为表锁 间隙锁的危害 如何锁定一行 行锁分析 总结 ...
  • 众所周知,MySQL 的 InnoDB 存储引擎支持事务,支持行级锁(innodb的行锁是通过给索引项加锁实现的)。得益于这些特性,数据库支持高并发。如果 InnoDB 更新数据使用的不是行锁,而是表锁呢?是的,InnoDB 其实很...
  • MySql类型转换导致行锁升级表锁

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

空空如也

空空如也

1 2 3 4 5 ... 7
收藏数 130
精华内容 52
关键字:

mysql行锁升级表锁

mysql 订阅