先说下数据库的常用存储引擎
存储引擎
InnoDB:
支持主外键;行锁,只锁住某一行;不仅缓存索引还缓存真实数据,对内存要求高,内存大小对性能有影响;关注的是事务。
MyISAM:
不支持主外键;表锁,即使操作一条记录也会锁住整个表;只缓存索引,不缓存真实数;关注的是性能。
如何选择:除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该选择InnoDB引擎。
锁分类:读锁(共享锁)、写锁(独占锁)
读写锁的区别:读锁会阻塞写,但是不会阻塞读;而写锁则会把读和写都阻塞。
表锁
- 手动加锁:
lock table [表名] read(wite), [表名2] read(write),....;
- 查看表上加过的锁:
show open tables;
- 释放表锁:
unlock tables;
- 如何分析表锁定:
show status like 'table%'; Table_locks_immediate;
表示产生表级锁定的次数,表示可以立即获取锁的查询次数,每次加1; Table_locks_waited
表示出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁加1),此值很高说明存在着较严重的表级锁争用情况。
结论:MyISAM在执行查询语句前,会自动给设计的所有表加读锁;在执行增删改操作前,会自动给设计的表加写锁。
MyISAM的读写锁调度是写优先,这也是MyISAM不适合做写为主表的引擎。因为写锁后,其他线程不能做任何操作,大量的更新会是查询很难得到锁,从而造成永远的阻塞。MyISAM 偏读!
行锁
InnoDB是行锁,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- 索引失效行锁升级为表锁:
比如修改的时候varchar类型没有加单引号,此时mysql会做隐式转换导致索引失效,此时行锁变表锁。 - 间隙锁:
当我们用范围条件而不是相等条件检索数据并请求共享或排他锁时,InnoDB会给复合条件的已有数据记录的索引项添加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙”;InnoDB会对这个“间隙”也加锁。这种锁叫间隙锁。 - 如何锁定一行:
begin; select * from tableA where id=8 for update; commit;
- 查看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:系统启动后到现在总共等待的次数
重要的参数:等待总次数、等待总时长、等待平均时长。
优化建议:
- 尽可能让所有数据检索都通过索引来完成,避免无索引导致行锁升级为表锁;
- 合理设计索引,尽量缩小锁的范围;
- 尽可能减少检索条件,避免间隙锁;
- 尽量控制事务大小,减少锁定资源量和时间长度;
- 尽可能低级别事务隔离。
死锁
一般是事务相互等待对方资源,最后形成环路造成的。
- 不同表相同记录行锁冲突
- 相同表记录行锁冲突:jobA处理的的id列表为[1,2,3,4],而job处理的id列表为[8,9,10,4,2],这样就造成了死锁。
- 不同索引锁冲突:这种情况比较隐晦,事务A在执行时,除了在二级索引加锁外,还会在聚簇索引上加锁,在聚簇索引上加锁的顺序是[1,4,2,3,5],而事务B执行时,只在聚簇索引上加锁,加锁顺序是[1,2,3,4,5],这样就造成了死锁的可能性。