一. MySQL 锁分类
表级锁: 开销小, 加锁块; 不会出现死锁, 锁定粒度大, 发生锁冲突的概率最高, 并发度最低.
行级锁: 开销大, 加锁慢; 会出现死锁; 锁定粒度最小, 发生锁冲突的概率最低, 并发性也最高.
页面锁: 开销和加锁界于表锁和行锁之间, 会出现死锁; 锁定粒度界与表锁和行锁之间, 并发一般.
二. MyISAM
1. 开门见山
1.MyISAM 不支持事物;
2.MyISAM 只支持表锁;
不能在表损坏后恢复数据;
MyISAM 表锁有两种模式: 表共享读锁 (table read lock) 和表独占写锁(table write lock), 锁的解释如下:
myISAM 表的读操作, 不会阻塞其他用户对同一个表的读请求, 但会阻塞对同一个表的写请求.
myISAM 表的写操作, 会阻塞其他用户对同一个表的读和写操作.
myISAM 表的读, 写操作之间, 以及写操作之间是串行的.
适合在以下几种情况下使用:
做很多 count 的计算
查询非常频繁
MyISAM 在执行查询前, 会自动执行表的加锁, 解锁操作, 一般情况下不需要用户手动加, 解锁, 但是有的时候也需要显示加锁. 当然也可以用 union 做关联查询代替
- lock table t1 read, t2 read;
- select count(t1.id) as 'total' from t1;
- select count(t2.id) as 'total' from t2;
- unlock tables;
注意事项
1. 在锁定表时候, 如果加上关键字 local, 满足 myISAM 表的并发插入问题 .eg: lock table t3 read local;
2. 使用 locak tables 给表加锁时候, 必须同时给所有涉及到的表加锁, 因为加锁之后, 当前会话, 就不能操作没有加锁的表.
2. 并发插入问题
MyIAM 表的读和写是串行的, 但是这是就总体而言, 在一定条件下 MyISAM 表也支持查询和插入操作的并发进行.
MyISAM 存储引擎有一个系统变量 concurrent_insert, 专门用于充值其并发插入的行为, 其值分别可以为 0,1 和 2
当 concurrent_insert = 0 时, 不允许并发插入
当 concurrent_insert = 1 时, 如果 myisam 表中没有空洞(即表的中间没有被删除的行),myisam 允许在一个进程读表的同时, 另一个进程从表尾插入记录
当 concurrent_insert = 2 时, 无论 myisam 表中有没有空洞, 都允许在表尾并发插入记录
3.MyISAM 锁调度问题
MyISAM 存储引擎的读锁和写锁是互斥的, 读写操作室串行的, 那么如果读写两个进程同时请求同一张表, MySQL 将会使写进程先获得锁. 不仅仅如此, 即使读请求先到达锁等待队列, 写锁后到达, 写锁也会先执行. 因为 MySQL 因为写请求比读请求更加重要. 这也正是 MyISAM 不适合含有大量更新操作和查询操作应用的原因.
调节办法:
通过指定启动参数 low-priority-updates, 使 MyISAM 引擎默认给与读请求优先的权限
通过执行 set low_PRIORITY_UPDATES=1, 降低更新请求的优先级.
指定 INSERT,UPDATE,DELETE 语句的 LOW_PRIORITY 属性.
三. InnoDB 存储引擎
1. 开门见山
具有提交, 回滚和崩溃恢复能力的事务安全 (ACID 兼容) 存储引擎.
支持事物和外键
默认是行锁
支持数据恢复
注意: 行锁在某些情况下会变成表锁, 比如 SQL 的更新 (update) 或者删除 (delete) 语句中未使用到索引, 导致在 InnoDB 在对数据进行相应操作的时候必须把整个表锁起来进行检索(表锁). 而如果使用了索引的话, InnoDB 只会通过索引条件检索数据, 而只锁住索引对应的行(行锁).
2. 实例分析
问题描述:
系统中有一个实时的定时任务, 当有条件触发的时候, 会更新对应的 A 表;
但是同时有另外一个对 A 表的写操作, 因此当进行测试的时候, 有时候会不固定的出 "Lock wait timeout exceeded" 的错误.
当出现这个问题的时候, 从很多的地方进行了分析, 然后都无法得到正确的解决方案(因为描述 1 模块不是我写的, 所以没有去查看更新表的代码操作)
可能的原因:
在描述 1 中定时任务更新表 A 的时候, 更新条件中没有使用索引, 导致当进行定时任务更新表的时候形成了表锁. 然后因为表 A 数据量比较大, 检索较慢, 然后导致了描述 2 中对表 A 的写操作的等锁超时.
3. 适用的场景
更新和查询都相当的频繁, 多重并发
要求事务, 或者可靠性要求比较高
外键约束, MySQL 支持外键的存储引擎只有 InnoDB
一般来说, 如果需要事务支持, 并且有较高的并发读取频率, InnoDB 是不错的选择.
来源: http://www.bubuko.com/infodetail-2967048.html