mysql锁相关介绍

msyql锁问题：

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定争夺：

mysql> show status like 'Table%';

可以通过检查 Innodb_row_lock 状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';

 

什么情况下使用表锁：

表级锁在下列几种情况下比行级锁更优越：

1.  很多操作都是读表。

2.  在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：

3.  UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

4.  DELETE FROM tbl_name WHERE unique_key_col=key_value;

5.  SELECT 和 INSERT 语句并发的执行，但是只有很少的 UPDATE 和 DELETE 语句。

6.  很多的扫描表和对全表的 GROUP BY 操作，但是没有任何写表。

什么情况下使用行锁：

行级锁定的优点：

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

2.  回滚时只有少量的更改。

3.  可以长时间锁定单一的行。

行级锁定的缺点：

1.  比页级或表级锁定占用更多的内存。

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

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

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

什么情况下使用表锁：

表级锁在下列几种情况下比行级锁更优越：

1.  很多操作都是读表。

2.  在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：

3.  UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

4.  DELETE FROM tbl_name WHERE unique_key_col=key_value;

5.  SELECT 和 INSERT 语句并发的执行，但是只有很少的 UPDATE 和 DELETE 语句。

6.  很多的扫描表和对全表的 GROUP BY 操作，但是没有任何写表。

什么情况下使用行锁：

行级锁定的优点：

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

2.  回滚时只有少量的更改。

3.  可以长时间锁定单一的行。

行级锁定的缺点：

1.  比页级或表级锁定占用更多的内存。

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

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

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

当使用 insert...select...进行记录的插入时，如果 select 的表是 innodb 类型的，不论 insert 的表是什么类型的表，都会对 select 的表的纪录进行锁定。对于那些从 oracle 迁移过来的应用，需要特别的注意，因为 oracle 并不存在类似的问题，所以在 oracle 的应用中 insert...select...操作非常的常见。例如：有时候会对比较多的纪录进行统计分析，然后将统计的中间结果插入到另外一个表，这样的操作因为进行的非常少，所以可能并没有设置相应的索引。如果迁移到 mysql数据库后不进行相应的调整，那么在进行这个操作期间，对需要 select 的表实际上是进行的全表扫描导致的所有记录的锁定，将会对应用的其他操作造成非常严重的影

响。究其主要原因，是因为 mysql 在实现复制的机制时和 oracle 是不同的，如果不进行 select 表的锁定，则可能造成从数据库在恢复期间插入结果集的不同，造成主从数据的不一致。如果不采用主从复制，关闭binlog 并不能避免对 select 纪录的锁定，某些文档中提到可以通过设置 innodb_locks_unsafe_for_binlog 来避免这个现象，当这个参数设置为 true 的时候，将不会对 select 的结果集加锁，但是这样的设置将可能带来非常严重的隐患。如果使用这个 binlog 进行从数据库的恢复，或者进行主数据库的灾难恢复，都将可能和主数据库的执行效果不同。因此，我们并不推荐通过设置这个参数来避免 insert...select...导致的锁，如果需要进行可能会扫描大量数据的 insert...select 操作，我们推荐使用 select...into outfile 和 load data infile 的组合来实现，这样是不会对纪录进行锁定的。

如何减少锁的冲突：

1.  对 Myisam 类型的表：

 1)  Myisam 类型的表可以考虑通过改成 Innodb 类型的表来减少锁冲突。

 2)  根据应用的情况，尝试横向拆分成多个表或者改成 Myisam 分区对减少锁冲突也会

 有一定的帮助。

2.  对 Innodb 类型的表：

 1)  首先要确认，在对表获取行锁的时候，要尽量的使用索引检索纪录，如果没有使用

 索引访问，那么即便你只是要更新其中的一行纪录，也是全表锁定的。要确保 sql

 是使用索引来访问纪录的，必要的时候，请使用 explain 检查 sql 的执行计划，判

 断是否按照预期使用了索引。

 2)  由于 mysql 的行锁是针对索引加的锁，不是针对纪录加的锁，所以虽然是访问不同

 行的纪录，但是如果是相同的索引键，是会被加锁的。应用设计的时候也要注意，

 这里和 Oracle 有比较大的不同。

 3)  当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，当表有主

 键或者唯一索引的时候，不是必须使用主键或者唯一索引锁定纪录，其他普通索引

 同样可以用来检索纪录，并只锁定符合条件的行。

 4)  用 SHOW INNODB STATUS 来确定最后一个死锁的原因。查询的结果中，包括死锁的

 事务的详细信息，包括执行的 SQL 语句的内容，每个线程已经获得了什么锁，在等

 待什么锁，以及最后是哪个线程被回滚。详细的分析死锁产生的原因，可以通过改

 进程序有效的避免死锁的产生。

 5)  如果应用并不介意死锁的出现，那么可以在应用中对发现的死锁进行处理。

 6)  确定更合理的事务大小，小事务更少地倾向于冲突。

 7)  如果你正使用锁定读，（SELECT ... FOR UPDATE 或 ... LOCK IN SHARE MODE），

 试着用更低的隔离级别，比如 READ COMMITTED。

 8)  以固定的顺序访问你的表和行。则事务形成良好定义的查询并且没有死锁。