附录:前段时间学习了下innodb锁的相关知识,对锁和事务有了大体理解,这里做个小总结。
1.Innodb事务和锁的关系。
Innodb区别于MyISAM的两个特点就是Innodb对于事务的支持和对行锁的支持。事务要求了一组SQL语句的ACID特性,同时为了避免对一行记录的并发更新,innodb本身会在一定情况下加锁,然后等语句所在的事务退出后(rollbak或者commit)释放锁。其实在autocommit=true时,一个sql本身就是一个事务。
Innodb在执行Update,Delete,Insert时会对记录加写锁(排他锁,加上排他锁后,不能再加共享锁和排他锁)。而Select语句不会对记录加锁。
共享锁:SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。
举例:
如图,左侧事务加了排他锁(for update),右侧事务直接查询不会等待(不加锁),加排他锁失败,加共享锁失败(lock in share mode)。左侧事务提交或者回滚后,释放锁。
2.Innodb的行锁特性。
Innodb使用行锁加锁记录,也就是说会在加锁条件下对访问的具体行进行加锁,而不会锁住全表。这样能够大大提高表访问的吞吐量,减少不必要的记录锁。而Innodb的行锁是使用索引实现的,因此有一下几点需要注意:
- 如果对已一条加锁语句访问的数据走不了索引或者没加索引,其实他锁的是全表,这点需要注意。
- 如果索引使用的是范围,那么他会锁住命中的记录,同时会锁住不存在的记录间隙防止插入(这一行并不存在),gap lock。
如图
左边事务锁住了age>3的记录,那么很自然age=4的记录会被锁住。而在右边事务中,插入一条age=5个记录,而插入操作被hang住,这就是间隙锁。间隙锁是为了避免幻象读的发生,即A事务批量update condition=x的记录,同时B事务插入了一条condition=X的记录,那么A事务提交后,发现仍然存在一条没有更新的记录,貌似出现了幻觉,这就是幻读。
因为间隙锁似乎为了防止插入,所以update age=5并不锁表,因为记录并不存在- -。
- 如果两个查询走不同索引,但是却需要处理同一条记录,会竞争锁。
- 虽然两个查询到的记录不同,但是走的是同一个索引,那么依然会竞争锁。
例如:
learntransaction表在age加了索引,左边窗口查询age=122 AND id=6的记录,右边窗口查询age=122 and id=3的记录,可以发现两者的记录并不同,但是却出现了锁竞争,因为两个公用了一个索引age=122。
-
当where语句的查询字段涉及到多个索引时,mysql会优化sql决定走的索引,所以如果id也加了索引,那么索然在where语句里age在前面,但不一定走age的索引而走了id的索引。所以这个时候对于怎么加锁需要看具体索引的使用情况,建议使用explain工具查询执行计划判断。
这里需要注意的是,慢查询导致数据库hang住并不一定是因为竞争锁,慢查询可能因为要更新的记录太多,导致sql迟迟无法完成,而innodb默认32个槽,也就是32个并发工作线程,当32个线程都在运行而无法接收新的sql时,数据就可能被hang住了,而与锁无关。
参考资料:
《深入浅出MySQL——数据库开发、优化与管理维护》 http://book.51cto.com/art/200803/68127.htm
相关推荐
MySQL Innodb 索引原理详解
辛星笔记之MySQL部分之InnoDB引擎的索引部分。
MySQL的引擎简介,InnoDB的锁机制与事务隔离级别
mysql_innoDB 事务与锁详解
InnoDB事务-锁-MVCC.pdf
NULL 博文链接:https://dinglin.iteye.com/blog/1682188
InnoDB事务、锁、多版本分析,希望对大家有用
InnoDB索引与MyISAM索引实现的区别是什么?.mp4 InnoDB索引与MyISAM索引实现的区别是什么?.mp4 InnoDB索引与MyISAM索引实现的区别是什么?.mp4 InnoDB索引与MyISAM索引实现的区别是什么?.mp4 InnoDB索引与MyISAM...
InnoDB表的索引有哪些特性,以及索引组织结构是怎样的 1、InnoDB聚集索引特点 我们知道,InnoDB引擎的聚集索引组织表,必然会有一个聚集索引。 行数据(row data)存储在聚集索引的叶子节点(除了发生overflow的列,...
MySQL锁和事务、InnoDB架构分析.zipMySQL锁和事务、InnoDB架构分析.zipMySQL锁和事务、InnoDB架构分析.zipMySQL锁和事务、InnoDB架构分析.zipMySQL锁和事务、InnoDB架构分析.zipMySQL锁和事务、InnoDB架构分析....
MySQL技术内幕InnoDB存储引擎-读书笔记.pdf
MyISAM和InnoDB都使用B+树来实现索引: • MyISAM的索引与数据分开存储 • MyISAM的索引叶子存储指针,主键索引与普通索引无太大区别 • InnoDB的聚集索引和数据行统一存储 • InnoDB的聚集索引存储数据行本身,普通...
辛星笔记对MySQL部分的InnoDB引擎的锁部分的介绍。
InnoDB 事务/锁/多版本分析 InnoDB 事务/锁/多版本分析
主要给大家介绍了关于MySQL Innodb索引原理的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用mysql具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
1.1.6 从innodb的索引结构分析,为什么索引的 key 长度不能太长
Mysql 的InnoDB引擎的相关笔记 1.0.MySQL架构到innoDB架构.md 1.1.0.InnoDB——简介.md 1.1.1.InnoDB——关键特性.md 1.2.0.InnoDB内存结构——缓冲池.md 1.2.1.InnoDB内存结构——log buffer.md 1.2.2.InnoDB内存...
下面小编就为大家带来一篇浅谈innodb的索引页结构,插入缓冲,自适应哈希索引。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
BTree索引的基本概念,优劣势,分裂问题,explain输出解释,如何使用optimizer trace
关于mysql中的innodb存储引擎的索引机制简介,详细介绍了锁机制