MySQL进阶篇4——锁+InnoDB引擎+MySQL管理

锁

概述

保证并发访问数据库数据的一致性和有效性等。

全局锁-库锁

​ 加锁后，整个数据库实例就处于只读状态，后续的DML语句，DDL语句，以及更新操作的事务提交语句都将会被阻塞。

典型使用场景：

​ 对全库做逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

flush tables with read lock;

widnows：命令行，mysqldump -h localhost -uroot -p 1234 userDB > userDB.sql;

unlock tables;

特点：

​ 如果在主库上备份，那么备份期间都不能执行更新，业务基本上就得停摆。

​ 如果在从库上备份，那么在备份期间，从库不能执行从主库同步过来的二进制日志binlog，会导致主从延迟。

所以：在InnoDB引擎中，我们可以在备份时，加上参数 --single-transaction来完成不加锁的一致性数据备份。【通过快照读来实现。】

mysqldump --single-transaction -uroot -p 1234 userDB > d:/userDb.sql;

表级锁

表级锁在InnoDB、MyISAM、BDB等存储引擎中，都是支持的。

表锁：

​ 1、表读锁

lock tables 表名 read/write；

unlock tables;

当前会话，报错。其他会话，阻塞到解锁。

​ 2、表写锁

当前会话：

lock tables score write;

select * from score;没问题

update score set chinese = 100 where id = 1; 没问题

=================================================

其他窗口会话：

select * from score ；会阻塞；

update 会阻塞到表锁释放。

元数据锁（meta data lock,MDL）：

​ 元数据锁是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。【事务开启的时候，没有元数据锁。只有执行了增删改查之后，才有了元数据锁。】[避免DML语句和DDL语句的冲突。]

​ 当对一张表进行增删改查的时候，增加MDL读锁（共享锁）；当对表结构进行变更的时候，加MDL写锁（排他）。

​ 【会话1开启事务查询之后，会话2修改表结构，阻塞。当事务1提交之后，阻塞解除。因为会话1查询数据之后，会产生共享锁，与会话2的排他锁冲突。】

查看元数据锁：

select object_type,object_schema,object_name,lock_type,lock_duration from performance_shema.metadata_locks;

意向锁：

​ 作用，某行记录会话1加了行锁，会话2想要添加表锁，需要一行行检查记录。为了避免DML语句在执行时，加的行锁和表锁冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

​ 加入意向锁：

​ 会话1开始事务，给表记录增加行锁，同时对表增加意向锁。

​ 会话2要给表增加表锁，需要检查表锁和意向锁冲不冲突。

意向共享锁IS：由语句 select … lock in share mode添加。

意向排他锁IX：由insert 、update、delete、 select for upate添加。

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks