轻松掌握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

锁的作用，就是把权限归为私有，其它人用不了。你自已把表锁了，自已当然还能用。

1、表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

2、数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。

3、对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。

4、MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。

5、总的来说，MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。

---