文章目录一. MySQL中锁的作用二. MySQL中有哪些锁三. 全局锁3.1 介绍全局锁3.2 全局锁语法3.3 全局锁特点3.4 不加锁备份四. 表级锁4.1 表级锁介绍4.2 表锁4.3 元数据锁4.4 意向锁五. 行锁5.1 行锁的分类5.2 行锁5.3 间隙锁临键锁一. MySQL中锁的作用用来保护并发访问数据库资源的线程安全问题防止多个进程同时操作数据库导致数据库的错误操作。二. MySQL中有哪些锁全局锁锁定数据库中所有表。表级锁每次操作锁住整张表。行级锁每次操作锁住对应的行数据。三. 全局锁3.1 介绍全局锁定义全局锁就是对整个数据库实列加锁加锁后整个实列变成只读状态已经更新操作的事务提交语句都将被阻塞。使用场景做全库的逻辑备份对于所有的表进行锁定从而获取一致性视图保证数据的完整性。如上操作不加锁的话就可能会导致备份的数据库有问题比如上面有新的订单了但是库存没有减少因此我们备份时需要加上全局锁。3.2 全局锁语法全局锁加锁flushtableswithreadlock;数据备份mysqldump-uroot –p123456 itcastitcast.sql这个不是SQL语句mysqldump是一个备份的工具该指令需要在终端命令行中执行。全局锁释放unlocktables;3.3 全局锁特点如果在主库上备份那么备份期间不能执行更新业务基本上停摆。如果在从库上备份那么备份期间从库不能执行主库同步过来的二进制日志binlog会导致主从延迟。3.4 不加锁备份在innoDB引擎中我们可以在备份时加上参数 --single-transaction 来完成不加锁的一致性数据备份。mysqldeump--single-transaction -uroot -p123456 itcast itcast.sql四. 表级锁4.1 表级锁介绍表级锁每次操作锁住整张表。锁定粒度大发生锁冲突的概率最高并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。表级锁有三类表锁元数据锁意向锁4.2 表锁表锁有两类表共享读锁加锁后该线程和其他线程可以读取表中数据但都不能对表数据做写操作。语法locktables表名read;表独享写锁加锁后该线程可对表进行读和写其他线程不能对该表进行操作直到释放锁。语法locktables表名write;4.3 元数据锁元数据锁简称MDL。MDL加锁是系统自动进行的不需要显式调用。但对表进行增删改查时会加上MDL锁。在表上有活动事务的时候不可以对元数据进行写入操作。为了避免DML与DDL冲突保证读写的正确性。这里的元数据可以理解为表的结构也就是说当表涉及未提交事务时是不能修改表的结构的。在MySQL5.5中引入了MDL对表进行增删改查SQL操作时表会加上DML共享锁共享对表结构修改时会加上DML排他锁排他。常见的SQL操作时所添加的元数据锁对应SQL锁类型说明lock tables xxx read / writeSHARED_READ_ONLY / SHARED_NO_READ_WRITEselect 、select … lock in share modeSHARED_READ与SHARED_READ、SHARED_WRITE兼容与EXCLUSIVE互斥insert 、update、delete、select … for updateSHARED_WRITE与SHARED_READ、SHARED_WRITE兼容与EXCLUSIVE互斥alter table …EXCLUSIVE与其他的MDL都互斥我们可以通过下面的SQL来查看数据库中的元数据锁的情况selectobject_type,object_schema,object_name,lock_type,lock_durationfromperformance_schema.metadata_locks;4.4 意向锁介绍在DML操作时会加上行锁此时加表锁会一行一行检查是否存在行锁。而意向锁就是加行锁时给表加上意向锁从而根据意向锁的类型判断能否加表锁。意向锁分类意向共享锁(IS): 由语句select … lock in share mode添加 。 与 表锁共享锁 (read)兼容与表锁排他锁(write)互斥。意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥意向锁之间不会互斥。一旦事务提交了意向共享锁、意向排他锁都会自动释放。可以通过以下SQL查看意向锁及行锁的加锁情况selectobject_schema,object_name,index_name,lock_type,lock_mode,lock_datafromperformance_schema.data_locks;五. 行锁5.1 行锁的分类行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于行级锁主要分为以下三类行锁Record Lock锁定单个行记录的锁防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。间隙锁Gap Lock锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持。临键锁Next-Key Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持.5.2 行锁InnoDB实现了以下两种类型的行锁共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排它锁。排他锁X允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁。同一行索引共享锁兼容共享锁不兼容排他锁而排他锁不兼容任何锁。常见的SQL语句在执行时所加的行锁如下可以通过以下SQL查看意向锁及行锁的加锁情况selectobject_schema,object_name,index_name,lock_type,lock_mode,lock_datafromperformance_schema.data_locks;无索引行锁升级为表锁当我们对表中没有索引的字段进行操作时上面表格中的锁会升级为表锁。当我们对该字段创建索引就又可以正常加行锁了。5.3 间隙锁临键锁防止数据幻读的情况。索引查询唯一值给不存在的值上锁时锁为间隙锁。如图当我们查询19时innoDB引擎会在29加上间隙锁。索引查询非唯一值向右遍历时最后一个值不满足查询需求时next-key lock 退化为间隙锁。如图我们查询18时因为18是非唯一的所以我们需要给18加上临键锁和29加上间隙锁。索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。查询的条件为id21并添加共享锁。 此时我们可以根据数据库表中现有的数据将数据分为三个部分(18,29](29,∞]所以数据库数据在加锁是29的临键锁包含29及29之前的间隙正无穷的临键锁(正无穷及之前的间隙)。