MySQL中的锁-海口c网

MySQL中有哪些锁?

全局锁(FTWRL)

含义：Flush Table with Read Lock的缩写，它会锁定整个数据库实例，让所有表都处于只读状态。

使用全局锁，要执行的命令:

flush tables with read lock

之后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

对数据增删改操作，比如insert,update,delete等语句；

对表结构的更改操作，比如alter table,drop table等语句。

insert into student(id,name,sex,birthday,class)values(110,'zz','男','1977-10-11','95034');

如果释放全局锁，则要执行这条命令:

unlock tables

应用场景:

全局锁主要应用与全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

缺点

加上全局锁后，整个库都是只读状态，备份耗时常，业务无法更新数据，导致业务停滞。

替代方式

支持可重复读隔离级别的引擎(如InnoDB)，用mysqldump --single - transaction开启事务备份；不支持事务的引擎(如MylSAM)只能用全局锁。

数据库备份时，想要替代全局锁，关键在于利用事务特性和多版本并发控制(MVCC);

原理基础:在可重复读隔离级别下，事务开启时会生成一个Read View(数据快照)。之后事务执行期间，都依据这个Read VIew去判断数据的可见性。即使其他事务在此期间对数据进行了修改并提交，基于这个Read View读取的数据依旧是事务开启时的状态，保证了数据的一致性。这就是MVCC的作用机制。
操作方法:用mysqldump --single-transaction备份时，InnoDB 通过事务快照（可重复读隔离级别 + MVCC）固定数据版本，备份期间业务新操作生成独立版本，两者互不干扰，所以不用锁也能保证备份数据一致；MyISAM 不支持事务和 MVCC，只能用全局锁强制冻结数据。

表级锁

MySQL里面表级别的锁有这几种:

表锁:
元数据锁(MDL);
意向锁；
AUTO-INC锁；

表锁

使用方法，对学生表加表锁，使用以下命令:

#对学生表加读锁
lock tables student read;#对学生表加写锁
lock tables student write;
#解开当前会话所有锁
unlock tables;
#查操作
select * from student;
#插入操作
insert into student(id,name,sex,birthday,class)values(111,'gzz','男','1977-10-11','95035');

加完读锁之后执行插入操作会造成以下现象（当前会话）

lock tables student read;（给student表加读锁）

当前会话限制：只能对student表执行读操作（如SELECT ），不能执行写操作（如INSERT、UPDATE、DELETE ），也不能对其他表进行读写操作。
其他会话限制：能对student表执行读操作，但不能执行写操作，写操作会被阻塞，直到当前会话释放读锁。

lock tables student write;（给student表加写锁）

当前会话权限：可对student表进行读和写操作。
其他会话限制：不能对student表进行读或写操作，读写操作都会被阻塞，直至写锁被释放。两种锁释放均用 unlock tables; 语句，且会话断开时锁也会自动释放。

释放表锁，使用unlock tables命令，则会释放当前会话的所有表锁；

会话退出后，也会释放所有表锁。

在更细粒度锁出现前，表锁是处理并发常用手段。InnoDB 引擎场景下表锁弊端：InnoDB 引擎若使用表锁，因其锁粒度大（锁定整张表），会影响并发性能。比如多个事务要操作同表不同行数据，表锁会让其他事务等待，降低并发处理能力。InnoDB 优势：实现行级锁，锁粒度更细。仅锁定相关行数据，不同事务可同时操作同表不同行，极大提升数据库并发处理能力。

元数据锁

加锁机制：操作数据库表时自动添加。执行 CRUD 操作加读锁；表结构变更时加写锁。
功能作用：防止在对表进行操作期间，其他线程对表结构进行变更，保证数据操作的一致性。
阻塞规则：读锁期间，阻止其他线程申请写锁；写锁期间，阻止其他线程申请读锁。
释放时机：事务提交后释放，事务执行过程中一直持有。
长事务影响：长事务持有读锁不提交，会阻碍表结构变更的写锁申请，进而阻塞后续 CRUD 操作，可能导致数据库线程资源紧张。
应对措施：表结构变更前，排查并处理长事务。

意向锁

InnoDB 会在有人加「行锁」之前，先在表级别标记一个「我要锁某些行」的意向锁。这样当你想给整个表加锁时，只需看表上有没有这个标记，就能快速知道「表里面有没有行被锁住」，无需逐行检查。

使用InnoDB引擎的表里对某些记录加上共享锁之前，需要先在表级别加上一个意向共享锁；
使用InnoDB引擎的表里对某些记录加上独占锁之前，需要先在表级别加上一个意向独占锁；

与 select 关系

普通 select 靠 MVCC 实现一致性读，不加行级锁。但 select ... lock in share mode 会先加意向共享锁，再给记录加共享锁；select ... for update 先加意向独占锁，再给记录加独占锁。

冲突规则

意向共享锁和意向独占锁是表级锁，和行级共享锁、独占锁不冲突，意向锁之间也不冲突，只和共享表锁（lock tables ... read ）、独占表锁（lock tables ... write ）冲突。表锁和行锁满足 “读读共享、读写互斥、写写互斥” 。

作用原理

没意向锁时，加独占表锁得遍历全表看有无记录加独占锁，效率低。有了意向锁，加独占锁前先加意向独占锁，加独占表锁时，查看有无意向独占锁，就能快速判断表里记录是否被加锁。

意向锁是表级的「占位符」，用来快速告诉你表中是否有行已被锁定，避免全表扫描。

AUTO-INC锁

表里的主键通常会设置为自增的，这是通过对主键字段声明AUTO-INCREMENT属性实现。

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要时通过AUTO-INC锁实现的。

用途：用于实现数据库表主键等字段自增。插入数据时，数据库利用它自动给被AUTO_INCREMENT 修饰字段赋递增的值。

锁机制：是特殊表锁，执行完插入语句立即释放，非事务提交后释放。持有锁时，阻塞其他事务插入，确保自增值连续递增，但影响大量数据插入性能。

改进：MySQL 5.1.22 起，InnoDB 引入轻量级锁。通过innodb_autoinc_lock_mode 控制：

= 0 ，用 AUTO - INC 锁，语句执行完释放。

= 1 ，普通insert 申请后即释放，insert ... select 等批量插入语句结束后释放。

= 2 ，用轻量级锁，申请自增主键后就释放，性能高，但搭配statement 格式binlog 用于主从复制时会有数据不一致问题。

行级锁

InnoDB引擎是支持行级锁的，而MylSAM引擎并不支持行级锁。

在查询时对记录加行锁，可以使用这下面两个方式，这种查询会加锁的语句称为锁定读。

#对读取的记录加共享锁
select ... lock in share mode#对读取的记录加独占占领
select ... for update

上面这两条语句必须在一个事务中，因为当事务提交了，所就会被释放，所以在使用这两条语句的时候，要加上begin、start transaction或者set autocommit = 0。

共享锁(S锁)满足读读共享，读写互斥。独占锁(X锁)满足写写互斥、读写互斥。

行级锁的主要有三类:

Record Lock,记录锁，也就是仅仅把一条记录锁上；
Grap Lock,间隙锁，锁定一个范围，但是不包含记录本身；
Next-Key Lock:Record Lock+ Grap Lock的组合，锁定一个范围，并且锁定记录本身。

Record Lock（记录锁）

含义：针对数据库表中某一条具体记录进行加锁。例如在含有主键 id 的表中，使用 SELECT * FROM table WHERE id = 1 FOR UPDATE; 语句，就会对 id 为 1 的这条记录添加记录锁，其他事务不能对该记录执行修改、删除等操作。
作用：保障单条记录在被操作过程中的完整性与一致性，有效避免因多个事务并发修改同一条记录而产生的数据问题。

Gap Lock（间隙锁）

含义：锁定的是数据库表中记录之间的间隙范围，并不包含记录本身。比如表中存在记录 id 为 1、3 ，那么 1 和 3 之间的间隙就能够被间隙锁锁定。当对这个间隙加锁后，若有插入 id 为 2 记录的操作，会因涉及到已锁定的间隙范围而被阻塞。
作用：主要用于阻止在锁定的间隙范围内插入新记录，防止因新记录的插入导致数据逻辑出现混乱，是应对幻读问题的常用手段。

Next - Key Lock（临键锁）

含义：它是记录锁与间隙锁的组合形式。不仅会锁定某条特定记录，还会将该记录前后的间隙范围一并锁定。举例来说，若对 id 为 3 的记录添加临键锁，那么 id 为 3 的这条记录本身以及其前后的间隙范围都会被锁定，新记录无法插入到这个被锁定的范围之内。
作用：在确保单条记录操作安全性的同时，也能防止因在相关范围内插入新记录而引发的数据不一致问题，是 InnoDB 默认的行锁算法，在防止幻读方面效果显著。