Mysql锁机制与优化

Mysql锁机制与优化及MVCC原理

锁机制

在数据库中,除了传统计算机资源(如,CPU,RAM,I/O等) 在竞争,数据也是一种用户共享的资源。如何保证数据并发情况下的一致性和有效性，是所有数据库必须考虑的问题，锁冲突，也是数据库性能必须考虑的问题。

锁分类

1.从性能上分为 乐观锁(版本对比和CAS机制) 和 悲观锁。乐观锁适合读操作较多的业务场景。
悲观锁适合写操作较多的业务场景。当写操作较多时，使用乐观素偶会导致次数多，影响性能。
2.从操作粒度上 分为 行锁 ，页锁和 表锁；
3.从数据库操作上 分为 读锁 和 写锁(都属于悲观锁), 另外 还有 意向锁;

读锁

读锁也称为 共享锁,S锁(share): 针对同一份数据,多个读操作,可以同事进行而不会相互影响;

select * from User where id =1 lock in share mode;

写锁

写锁也称为排他锁,X锁(eXclusive): 当前写操作没有完成前,写锁会和其他写锁和读锁互斥.数据 的 写入/修改 操作都会加写锁. 查询数据时,也可以通过for update 添加写锁

select * from user where id =1 for update;

意向锁

意向锁也称为 I锁(Intention Lock): 针对表锁,主要是为了提高加表锁的效率. Mysql内部自行添加,当事务给表的数据添加了读锁或者写锁时,Mysql会同时给表添加一个标志. 表示已经有锁,此时如果其他事务需要添加表锁,会根据标志判断能否添加表锁.降低了添加表锁时的性能开销.减少逐行判断是否存在行锁的判断过程.

意向锁还分为 意向共享锁  和 意向排他锁
1.意向共享锁: IS锁,对整个表添加共享锁前,需要先获取意向共享锁;
2.意向排他锁: IX锁,对整个表添加排他锁前,需要先获取意向排他锁;

间隙锁
锁住两个值中间的空隙.记录与记录之间的间隙;如一张表只有 id =1 ,id=2,id=3,id=10,id=20.

间隙有 (3,10),(10,20),(20,+∞)

设置隔离机制为可重复读,对这些间隙进行加锁,可以解决幻读问题。如: update account set name =“lily” where id >8 and id<18 ,这个8,18落在了(3,20]这个范围中，那么这两个范围里面添加/修改数据就会被加锁。

select * from user where ID=18 for update;

这段sql 因为id=18处于 (10,20) 这个间隙中,其他事务无法插入数据.
或者

select * from user where id =22 for update ;

在(20,+∞)这个范围内的数据,其他事务也无法正常插入数据.

临键锁

Next-Key Locks 是行锁与间隙锁组合. 不仅包含间隙锁的范围,还包含行锁对应的id.

无索引的行锁会升级成为表锁
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁.并且该索引不能失效,否则都会从行锁升级成为表锁。
Innodb 和MyISAM 引擎的区别
Innodb 存储引擎 由于实现了行级锁定,在锁机制上的性能损耗会比表级锁定高,但是在并发处理场景上,要优于MyISAM的表锁.
但是Innodb 的行锁 也有可能导致整体性能下降.如,行锁的死锁;
行锁分析
show status like ‘innodb_row_lock%’;
Innodb_row_lock_current_waits: 当前正在等待锁定的数量 ;
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度 ;
Innodb_row_lock_time_avg: 每次等待所花平均时间 ;
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间;
Innodb_row_lock_waits:系统启动后到现在总共等待的次数;

锁优化:
1.尽量让数据的搜索通过索引完成.避免无索引行锁升级成为表锁;
2.设计合理的索引;
3.减少搜索条件范围,避免间隙锁;
4.控制事务的大小,尽量将事务加锁的操作放到最后;
5.尽可能减低事务隔离级别(建议使用可重复读);