MYSQL高级 -- 锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（ CPU  RAM、 I/O ）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有 效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个 角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL 中的锁，按照锁的粒度分，分为以下三类：

全局锁：锁定数据库中的所有表。全局锁：只能读，不能写

表级锁：每次操作锁住整张表。

行级锁：每次操作锁住对应的行数据。

一 全局锁
 

-- 备份数据信息   
-- 加全局锁 比较重的操作 
FLUSH tables with read lock;
-- 数据备份 在黑窗口中执行
mysqldump -uroot -p123456 mytext > d://user.sql
-- 不锁全表的情况下进行备份 快照技术
mysqldump -single-transaction -uroot -p123456 +数据库名 > d://user.sql放置的位置 （.sql）文件

-- 关闭全局
unlock tables ;

 

二 表级锁  
-- 表锁：
--     表共享读锁（read lock）读锁 自己和别人都可读，但是都不能写

--     表独占写锁（write lock）写锁  只有自己能读能写，其他人都不能

-- 元数据锁（meta data lock，MDL）

自己主动生效，不用自己操作，同时不能修改表机构（字段，类型等） 

-- 元数据锁 锁表结构 不影响其他人增删改 但是阻止其他人修改表结构  不需要手动加锁和释放 ，锁的是表结构

-- 意向锁  避免行锁一个个检查，影响性能；有了意向锁，可以当一个标志，当行锁完成后，就可以执行表锁。提高性能。（本来就有）

为了避免 DML 在执行时，加的行锁与表锁的冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

 

-- 意向锁 增删改自动有意向锁 查询需要手动加
-- 标志是否有行锁 来看是否加表锁  作用在整张表上，只是起提醒作用 

分类 意向共享锁 (IS): 由语句 select ... lock in share mode 添加 。 与 表锁共享锁 (read)兼容，与表锁排他锁 (write) 互斥。

意向排他锁 (IX): 由 insert 、 update 、 delete 、 select...for update 添加 。与表锁共 享锁(read) 及排他锁 (write) 都互斥，意向锁之间不会互斥。

-- 意向共享锁 读锁 和表读锁互相兼容
-- 意向排他锁 写锁  和表读锁 表写锁 都不兼容

-- 表读锁 只能查询自己和别人 谁都修改不了 分类意向共享锁(IS)
lock tables tb_user read
-- 释放锁
unlock tables
-- 表写锁  自己可以查询修改  但是会阻塞其他人查询修改 意向排他锁(IX)
lock tables tb_user write
unlock tables

例如：

BEGIN 
-- 查询语句 lock in share mode 得要声明意向锁  没有行锁
select * from tb_user where id =30 lock in share mode
-- 增删改 不用声明 有行锁
update tb_user set name ="123" where id =30
commit

三 行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。 InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的 锁。对于行级锁，主要分为以下三类：

行锁（ Record Lock ）：锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete 。在

RC 、 RR 隔离级别下都支持。

间隙锁（ Gap Lock ）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事

务在这个间隙进行 insert ，产生幻读。在 RR 隔离级别下都支持。

临键锁（ Next-Key Lock ）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap 。

在 RR 隔离级别下支持。

-- 行级锁：只有innoDB支持行锁

innoDB 支持事务 支持外键  支持 行锁
-- 锁的粒度最小
-- 聚集索引 == 聚集 
-- 行锁 间隙锁 临键锁

-- 行锁 事务还没有结束 锁的是聚集索引 指的是增删改---加上行锁
BEGIN
delete from tb_user where id=31;
COMMIT

-- 间隙锁 
BEGIN
-- 没有 id=30 要在 上下两边的 加上间隙锁  两个id之间不能（如果主键是小数）
-- 在进行操作（插入）
update tb_user set name="11111"
where id =30
COMMIT

--  临键锁
-- 只有在范围判断时才能
BEGIN
update student set name =123 where id >2 -- 加间隙锁 和行锁 不能添加和修改之后的数据信息 
COMMIT