006 mysql锁

 

step1 MySQL锁介绍(悲观锁)

        mysql在处理并发时采用锁机制；

• 乐观锁：默认无问题，当问题出现后再加锁；

• 悲观锁：默认会出现问题，未出现问题的时候就加锁；

 

step2 MySQL表级锁

        表级锁由MySQL SQL layer层实现，表级锁锁定的是表，开销小加锁快，但是锁定粒度大，出现锁冲突的概率高(死锁)；

        sql layer主要负责权限判断、sql解析、执行计划优化、query cache的处理等操作，这些操作都是在数据库系统处理底层数据之前的工作；

• MySQL的表级锁有三种

  -1- 表锁

  -2- 元数据锁(meta data lock,MDL) 

  -3- 意向锁(由Innodb引擎实现)

• MySQL 实现的表级锁定的争用状态变量

how status like 'table%';

table_locks_immediate：产生表级锁定的次数；
table_locks_waited：出现表级锁定争用而发生等待的次数；

step3 表锁

1、表锁介绍

        表锁有两种表现形式，表共享读锁（Table Read Lock）与表独占写锁(排他写)（Table Write Lock）；

--手动增加表锁
lock table 表名称 read(write),表名称2 read(write)，其他;
--查看表锁情况
show open tables;
--删除表锁
unlock tables;

2、表锁演示

--新建表
CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
);
INSERT INTO mylock (id,NAME) VALUES (1, 'a');
INSERT INTO mylock (id,NAME) VALUES (2, 'b');
INSERT INTO mylock (id,NAME) VALUES (3, 'c');
INSERT INTO mylock (id,NAME) VALUES (4, 'd');

2.1表读锁

 

--session1（mysql终端1）、session2（mysql终端2）
1、session1: lock table mylock read; -- 给mylock表加读锁
2、session1: select * from mylock; -- 可以查询
3、session1：select * from tdep; --不能访问非锁定表
4、session2：select * from mylock; -- 可以查询 没有锁
5、session2：update mylock set name='x' where id=2; -- 修改阻塞,自动加行写锁
6、session1：unlock tables; -- 释放表锁
7、session2：Rows matched: 1 Changed: 1 Warnings: 0 -- 修改执行完成
8、session1：select * from tdep; --可以访问

2.2表写锁

--session1（mysql终端1）、session2（mysql终端2）
1、session1: lock table mylock write; -- 给mylock表加写锁
2、session1: select * from mylock; -- 可以查询
3、session1：select * from tdep; --不能访问非锁定表
4、session1：update mylock set name='y' where id=2; --可以执行
5、session2：select * from mylock; -- 查询阻塞
6、session1：unlock tables; -- 释放表锁
7、session2：4 rows in set (22.57 sec) -- 查询执行完成
8、session1：select * from tdep; --可以访问

step4 元数据锁

1、元数据锁介绍

        MDL (metaDataLock) 元数据(即表结构),在 MySQL 5.5 版本中引入了 MDL，当对一个表做(CRUD)增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

--session1（mysql终端1）、session2（mysql终端2）
1、session1: begin;--开启事务
select * from mylock;--加MDL读锁
2、session2: alter table mylock add f int; -- 修改阻塞
3、session1：commit; --提交事务 或者 rollback 释放读锁
4、session2：Query OK, 0 rows affected (38.67 sec) --修改完成
Records: 0 Duplicates: 0 Warnings: 0

step5 意向锁

        InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预；

        意向锁的主要作用是为了【全表更新数据】时的性能提升，否则在全表更新数据时，需要先检索该表是否某些记录上面有行锁。

1.意向共享锁（IS）

        事务在给一个数据行加共享锁前必须先取得该表的IS锁。

2.意向排他锁（IX）

        事务在给一个数据行加排他锁前必须先取得该表的IX锁。