Mysql锁机制与优化实践以及MVCC底层原理
一、锁机制详解
1.1 概述
锁是计算机协调多个进程或线程并发访问某一资源的机制
在数据库中,除了传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供需要用户共享的 资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库 并发访问性能的一个重要因素。
1.2 锁分类
- 从性能上分为乐观锁(用版本对比或CAS机制)和悲观锁,乐观锁适合**
读操作较多的场景
,悲观锁适合写操作较多的场景
**,如果在写操作较多的场景使用乐观锁会导致比对次数过多,影响性能 - 从对数据操作的粒度分,分为**
表锁、页锁、行锁
** - 从对数据库操作的类型分,分为读锁和写锁(都属于悲观锁),还有意向锁
1.2.1 读锁(共享锁,S锁(Shared))
针对同一份数据,多个读操作可以同时进行而不会互相影响,比如
例:
select * from T where id=1 lock in share mode
1.2.2 写锁(排它锁,X锁(eXclusive))
当前写操作没有完成前,它会阻断其他写锁和读锁,数据修改操作都 会加写锁,查询也可以通过for update加写锁
例:
select * from T where id=1 for update
1.2.3 意向锁(Intention Lock)
又称I锁,针对表锁,主要是为了提高加表锁的效率,是mysql数据库自己 加的。当有事务给表的数据行加了共享锁或排他锁,同时会给表设置一个标识,代表已经有行锁了,其他事 务要想对表加表锁时,就不必逐行判断有没有行锁可能跟表锁冲突了,直接读这个标识就可以确定自己该不 该加表锁。特别是表中的记录很多时,逐行判断加表锁的方式效率很低。而这个标识就是意向锁。
意向锁主要分为:
- 意向共享锁,IS锁,对整个表加共享锁之前,需要先获取到意向共享锁。
- 意向排他锁,IX锁,对整个表加排他锁之前,需要先获取到意向排他锁。
1.2.4表锁
每次操作锁住整张表。开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最 低;一般用在整表数据迁移的场景。
‐‐手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
‐‐查看表上加过的锁
show open tables;
‐‐删除表锁
1.2.5 页锁(了解)
页锁只有BDB存储引擎支持页锁,页锁就是在页的粒度上进行锁定,锁定的数据资源比行锁要多,因为一个页中 可以有多个行记录。当我们使用页锁的时候,会出现数据浪费的现象,但这样的浪费最多也就是一个页上的 数据行。页锁的开销介于表锁和行锁之间,会出现死锁。锁定粒度介于表锁和行锁之间,并发度一般。