MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5113字，纯文字阅读大概需要8分钟。
内容图文
Mysql中不同的存储引擎支持不同的锁机制。比如MyISAM和MEMORY存储引擎采用的表级锁，BDB采用的是页面锁，也支持表级锁，InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。
Mysql3中锁特性如下：
表级锁：开销小，加锁块；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发性也最高。
页面锁：开销和加锁界于表锁和行锁之间，会出现死锁；锁定粒度界与表锁和行锁之间，并发一般。
MyISAM表索
1.查询表级锁争用情况
通过检查table_locks_waited和table_locks_immediate状态变量分析系统上表锁争夺情况
table_locks_waited锁定等待时间越长，则说明存在较严重的表级别锁争用情况。
2.锁模式
mysql的表锁有两种模式：表共享读锁（table read lock）和表独占写锁（table write lock）
说明 1.myISAM表的读操作，不会阻塞其他用户对同一个表的读请求，但会阻塞对同一个表的写请求。 2.myISAM表的写操作，会阻塞其他用户对同一个表的读和写操作。 3.myISAM表的读、写操作之间、以及写操作之间是串行的。 实例如下，打开了两个会话，当t3处于读锁定时候，会话二可以检索t3数据。当t3处于写锁定时候，会话二只有等到解锁后，才能显示数据（可以对比检索时间）。
3.加表锁
MyISAM在执行查询前，会自动执行表的加锁、解锁操作，一般情况下不需要用户手动加、解锁，但是有的时候也需要显示加锁。 比如：检索某一个时刻t1，t2表中数据数量。 常用代码如下： select count(t1.id1) as ‘sum‘ from t1; select count(t2.id1) as ‘sum‘ from t2;其实这是不标注的，很有可能当你在检索t1的那个时间点，t2的数据已经发生了变化，也就是说你检查出的t1和t2数据结果不是在同一个时间点上。 正确的做法是：
lock table t1 read, t2 read; select count(t1.id1) as 'sum' from t1; select count(t2.id1) as 'sum' from t2; unlock tables;
当然也可使用union,这样写：
SELECT COUNT(t1.`id1`) AS dadasum,'t1' AS tablename FROM t1 UNION ALL SELECT COUNT(t2.`id1`)AS dadasum ,'t2' AS tablename FROM t2 ;注意事项 1.在锁定表时候，如果加上关键字local，满足myISAM表的并发插入问题。eg： lock table t3 read local; 2.使用locak tables 给表加锁时候，必须同时给所有涉及到的表加锁，因为加锁之后，当前会话，就不能操作没有加锁的表。
4.并发插入问题
myISAM存储引擎有一个系统变量，concurrent_insert，专门用来控制并发插入行为的，值可以为0，1，2. concurrent_insert为0时候，不允许插入concurrent_insert为1时候，如果mysql没有空洞（中间没有被删除的行），myISAM运行一个进程读表的时候，另一个进程从表尾插入记录，这也是mysql默认设置。 concurrent_insert为2时候，无论MyISAM表中有没有空洞，都允许在表尾并行的插入。
5.myISAM锁调度问题
MyISAM存储引擎的读锁和写锁是互斥的，读写操作室串行的，那么如果读写两个进程同时请求同一张表，Mysql将会使写进程先获得锁。不仅仅如此，即使读请求先到达锁等待队列，写锁后到达，写锁也会先执行。因为mysql因为写请求比读请求更加重要。这也正是MyISAM不适合含有大量更新操作和查询操作应用的原因。 调节办法： 1)通过指定启动参数low-priority-updates,使MyISAM引擎默认给与读请求优先的权限 2）通过执行set low_PRIORITY_UPDATES=1，降低更新请求的优先级。 3）指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性。InnoDB锁
1.InnoDB与MyISAM最大不同有两点：
1）.支持事务 2）.采用行级锁2.查看InnoDB行锁争用情况
3.innodb行锁模式以及加锁方法
innoDB实现了以先两种类型的行锁： 共享锁（S）：允许一个事务去读一行，阻止其他事务获取相同数据集的排他锁。 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。 先两种意向表锁：意向同享锁 意向排他锁
如果一个事务请求的锁模式与当前的锁模式兼容，innodb就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。意向锁是Innodb自动加的，不需要用户干预。对于UPDATE、DELETE、INSERT语句，Innodb会自动给涉及的数据集加排他锁（X）；对于普通SELECT语句，Innodb不会加任何锁。
显示添加锁 共享锁（S） ： SELECT * FROM table_name WHERE .... LOCK IN SHARE MODE 排他锁（X）： SELECT * FROM table_name WHERE .... FOR UPDATE. 使用select ... in share mode获取共享锁，主要用在需要数据依存关系时，确认某行记录是否存在，并确保没有人对这个记录进行update或者delete。
4.InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过再数据块中，对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，innoDB才使用行级锁，否则InnoDB将使用表锁，在实际开发中应当注意。 实例一： 建立t1表如下：CREATE TABLE `t1` ( `id1` int(5) DEFAULT NULL, `id2` int(3) unsigned zerofill NOT NULL DEFAULT '000' ) ENGINE=InnoDB DEFAULT CHARSET=utf8
<span style="font-size:18px;">insert into t1 valuses(1,1),(2,2);</span>
因为没有创建索引，当给第一个会话添加索引时候，其实添加的是表索引，而非行索引，因为第二会话在查询其他信息时候，一直处于等待状态，最后超时，直到第一个会话事务提交后，方可查询。（需要先设置 set autocommit=0） 实例二： 修改上面t1表中数据，数据如下
给id1添加索引ALTER TABLE t1 ADD INDEX id1(id1);
有此可以看出此时，mysql使用的是行索引。 但是还有一个需要我们注意
很明显两个会话查询的不是同一行记录，为什么会话2仍然需要等待会话1提交之后才能查询呢？还是因为Mysql行锁是针对索引加的锁，不是针对记录加的锁，索引虽然访问不同的记录，但是他们的索引相同，是会出现冲突的，在设计数据库时候需要注意这一点。上面只有将字段id2，也添加上索引才能解决冲突问题。这也是mysql效率低的一个原因。
参考：《MySQL数据库开发、优化与管理维护》
MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解
标签：
本文系统来源：http://blog.csdn.net/hsd2012/article/details/51112009
内容总结
以上是互联网集市为您收集整理的MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解全部内容，希望文章能够帮你解决MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。