轻松掌握MySQL数据库锁机制的相关原理[1]

在一个update和insert *** 作频繁的表中 少量数据测试的时候运行良好 在实际运营中 因数据量比较大( 万条记录) 会出现死锁现象 用show processlist查看 可以看到一个update语句状态是Locked 一个delete语句状态是Sending data 查看了一下参考手册 把锁定相关的资料整理下来 以便自己记录和追踪该问题的解决情况

MySQL 支持对MyISAM和MEMORY表进行表级锁定 对BDB表进行页级锁定 对InnoDB 表进行行级锁定 在许多情况下 可以根据培训猜测应用程序使用哪类锁定类型最好 但一般很难说出某个给出的锁类型就比另一个好 一切取决于应用程序 应用程序的不同部分可能需要不同的锁类型 为了确定是否想要使用行级锁定的存储引擎 应看看应用程序做什么并且混合使用什么样的选择和更新语句 例如 大多数Web应用程序执行许多选择 而很少进行删除 只对关键字的值进行更新 并且只插入少量具体的表 基本MySQL MyISAM设置已经调节得很好

在MySQL中对于使用表级锁定的存储引擎 表锁定时不会死锁的 这通过总是在一个查询开始时立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理

对WRITE MySQL使用的表锁定方法原理如下

◆ 如果在表上没有锁 在它上面放一个写锁

◆否则 把锁定请求放在写锁定队列中

对READ MySQL使用的锁定方法原理如下

◆如果在表上没有写锁定 把一个读锁定放在它上面

◆否则 把锁请求放在读锁定队列中

当一个锁定被释放时 锁定可被写锁定队列中的线程得到 然后是读锁定队列中的线程

这意味着 如果你在一个表上有许多更新 SELECT语句将等待直到没有更多的更新

如果INSERT 语句不冲突 可以自由为MyISAM 表混合并行的INSERT 和SELECT 语句而不需要锁定

InnoDB 使用行锁定 BDB 使用页锁定 对于这两种存储引擎 都可能存在死锁 这是因为 在SQL语句处理期间 InnoDB 自动获得行锁定 BDB 获得页锁定 而不是在事务启动时获得

行级锁定的优点

· 当在许多线程中访问不同的行时只存在少量锁定冲突

· 回滚时只有少量的更改

· 可以长时间锁定单一的行

行级锁定的缺点

· 比页级或表级锁定占用更多的内存

· 当在表的大部分中使用时 比页级或表级锁定速度慢 因为你必须获取更多的锁

· 如果你在大部分数据上经常进行 GROUP BY *** 作或者必须经常扫描整个表 比其它锁定明显慢很多

· 用高级别锁定 通过支持不同的类型锁定 你也可以很容易地调节应用程序 因为其锁成本小于行级锁定

在以下情况下 表锁定优先于页级或行级锁定

· 表的大部分语句用于读取

· 对严格的关键字进行读取和更新 你可以更新或删除可以用单一的读取的关键字来提取的一行

&# UPDATE tbl_name SET column = value WHERE unique_key_col = key_value

&# DELETE FROM tbl_name WHERE unique_key_col = key_value

· SELECT 结合并行的INSERT 语句 并且只有很少的UPDATE或 DELETE 语句

· 在整个表上有许多扫描或 GROUP BY *** 作 没有任何写 *** 作

lishixinzhi/Article/program/MySQL/201311/29594

以下面的表为例子进行说明

间隙锁的产生来自于 InnboDB 引擎在可重复读的级别基础上执行当前读时出现的幻读问题。下面来分析一下幻读的例子， 假如没有间隙锁的话 ，那么会出现下面的现象：

如上表如示，是基于没有间隙锁的假设，sessionA 事务内执行两次相同的当前读返回的数据不一样，出现幻读的现象。因为(2,2,10)这条记录在原本的数据并不存在，行锁就锁不住，因此诞生间隙锁。

首先看看sessionA的执行计划，发现用到覆盖索引

场景 1：插入到索引 a 时，要插入是索引是(11,5)，属于(a=10,id=10)和(a=30,id=30)之间的锁范围，所以阻塞

场景 2、3、4 同理分析得出结论

本例子跟《查询条件走二级索引例子》区别在于 sessionA 是 select * ，因此需要回到主键索引查询所有字段，扫描了主键索引，所以也会在扫描到的索引进行加 next-key lock。该语句回表一次，扫描到是行是 id=10，所以加锁是(0,10],(10,20)，因此 sessionA 一共加了锁是索引 a 的(10,30)和主键索引的（0,20）。

---