锁分类
1.全局锁
2.表级锁
3.行锁
全局锁
命令:Flush tables with read lock
让这个库处于只读的状态
使用场景:做全库的逻辑备份的时候
表级锁
(1)表锁
语法:lock tables .. read/write
(2)元数据锁(meta data lock,MDL)
不需要显示使用,在访问表时自动加上。用于保证读写的正确性,避免因为表结构变更导致的数据不一致.
注意:
事务中的MDL锁,是在语句开始的时候申请,需要等到整个事务提交后再释放,而不是语句结束后立即释放,所以要避免大事务导致的长时间锁表.
其次再大表新增字段或者索引的时候也需要慎重
行锁
mysql 的MVCC
锁具表中的一行记录,可能有多个版本,每个版本都有自己的row_trx_id(按申请顺序严格递增)
在可重复读的隔离级别下,事务启动的瞬间，会有一个视图
处于绿色部分,即小于自己的事务id的事务,它是可见的.
处于橙色部分,它自己修改的是可见的,其它事务(其它瞬间,可能有多个事务还处于活跃状态,即未提交状态)修改的是不可见的.
处于红色部分,它是不可见的.
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`k` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);
查询都是一致性读,更新都是当前读
当然select 加update的话也是当前读
A返回的的就应该是1(此时B还没提交),B返回的就是3,因为C先加一并且提交了
加锁规则
这个规则中，包含了两个“原则”、两个“优化”和一个“bug”
- 原则1:加锁的基本单位是next-key lock 且是前开后闭区间
- 原则2:查找过程中访问到的对象才会加锁
- 优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁(只锁一行)
- 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁(锁一个范围)
- 一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止
锁的监控
在Information_schema架构下有三个和锁相关的表INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS
表INNODB_TRX的结构
| 字段名 | 说明 |
|---|---|
| trx_id | InnoDB存储引擎内部唯一的事务ID |
| trx_state | 当前事务的状态 |
| trx_started | 事务的开始时间 |
| trx_requested_lock_id | 等待事务的锁id.如果trx_state的状态为LOCK_WAIT,那么该值代表当前的事务等待之前事务占用锁资源的id.若trx_state不是LOCK WAIT,该值为NULL |
| trx_wait_started | 事务等待开始的时间 |
| trx_weight | 事务的权重,反映了一个事务修改和锁住的行数.在Innodb存储引擎中,当发生死锁需要回滚时,innodb存储引擎会选择该值最小的进行回滚 |
| trx_mysql_thread_id | mysql中的线程id,show processlist显示的结果 |
| trx_query | 事务运行的SQL语句 |
SELECT * from information_schema.INNODB_TRX;
表INNODB_LOCKS的结构
| 字段名 | 说明 |
|---|---|
| lock_id | 锁的ID |
| lock_trx_id | 事务的id |
| lock_mode | 锁的模式 |
| lock_type | 锁的类型,表锁还是行锁 |
| lock_table | 要加锁的表 |
| lock_index | 锁住的索引 |
| lock_space | 锁对象的space id |
| lock_page | 事务锁定页的数量.若是表锁,则该值为null |
| lock_rec | 事务锁定行的数量,若是表锁,则该值为null |
| lock_data | 事务锁定记录的主键值,若是表锁,则该值为null |
SELECT * from information_schema.INNODB_LOCKS;
表INNODB_LOCK_WAITS的结构
| 字段名 | 说明 |
|---|---|
| requesting_trx_id | 申请资源的事务id |
| requesting_lock_id | 申请的锁id |
| blocking_trx_id | 阻塞的事务id |
| blocking_trx_id | 阻塞的锁的id |
SELECT * from information_schema.INNODB_LOCK_WAITS;
两阶段锁协议
innodb事务中,行锁时在需要的时候才加上,但并不是不需要了就立刻释放,而是要等到事务结束时才释放.
结论
如果事务中会锁多行,要把嘴可能造成锁冲突,最可能影响并发度的锁往后面放.
死锁检测
两种策略
(1)设置超时时间,innodb_lock_wait_timeout,在innodb中,默认50s
(2)发起死锁检测,发现死锁后,主动回滚死锁链条中的某一事务,可以设置innodb_deadlock_detect=on
