MySQL 锁概述

欢迎来到蓝队云技术小课堂，每天分享一个技术小知识。今天介绍关于mysql锁的相关概念和发生的原因。

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

本文他对于将着重讨论MySQL锁机制的特点，常见的锁问题，以及解决MySQL锁问题的一些方法或建议：↓

- - -

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

比如：

　　MyISAM：和 MEMORY存储引擎采用的是表级锁（table-level locking）；

　　BDB：存储引擎采用的是页面锁（page-level locking），但也支持表级锁；

　　InnoDB：存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL 这 3 种锁的特性可大致归纳如下：↓ ↓ ↓

开销、加锁速度、死锁、粒度、并发性能

l 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

l 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

l 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！

仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；

而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

一切取决于应用程序，应用程序的不同部分可能需要不同的锁类型。

为了确定是否想要使用行级锁定的存储引擎，应看看应用程序做什么并且混合使用什么样的选择和更新语句。

例如，大多数Web应用程序执行许多选择，而很少进行删除，只对关键字的值进行更新，并且只插入少量具体的表。

基本MySQL MyISAM 设置已经调节得很好。

在 MySQL 中对于使用表级锁定的存储引擎，表锁定时不会死锁的。

InnoDB：

　　对于普通SELECT语句，InnoDB不会加任何锁；

　　InnoDB 行锁是通过给索引项加锁实现的：这就意味着只有通过索引条件检索数据时，InnoDB才使用行锁，否则使用表锁。

　　在mysql中，如果查询条件带有主键，会锁行数据，如果没有，会锁表。

　　当where条件中的字段没有加索引时，会锁住整张表。

　　在有索引的情况下，更新不同的行，innodb 默认的行锁是不会阻塞的。

　　where 后面的索引失效时，行锁变表锁，其他事物操作会有阻塞。

注意：在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

　　行锁建议：↓

　　　　尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

　　　　合理设计索引，尽量缩小索引的范围

　　　　尽可能较少检索条件，避免间隙锁

　　　　尽量控制事务大小，减少锁定资源量和时间长度

　　　　尽可能低级别事务隔离

　　　　varchar 不加单引号会引发行锁。

导致锁表原因：↓

1、锁表发生在insert update 、delete 中

2、锁表的原理是 数据库使用独占式封锁机制，当执行上面的语句时，对表进行锁住，直到发生commite 或者 回滚 或者退出数据库用户

　　第一、 A程序执行了对 tableA 的 insert ，并还未 commite时，B程序也对tableA 进行insert 则此时会发生资源正忙的异常 就是锁表

　　第二、锁表常发生于并发而不是并行（并行时，一个线程操作数据库时，另一个线程是不能操作数据库的，cpu 和i/o 分配原则）

3、减少锁表的概率：

　　减少insert 、update 、delete 语句执行 到 commite 之间的时间。

　　具体点批量执行改为单个执行、优化sql自身的非执行速度 如果异常对事物进行回滚

范例：↓ ↓ ↓

1）：使用 update

　　假设kid 是表table 的 一个索引字段 且值不唯一：

　　1）：如果kid 有多个值为12的记录那么：

　　　　update table set name=’feie’ where kid=12; #会锁表

　　2）：如果kid有唯一的值为1的记录那么：

　　　　update table set name=’feie’ where kid=1; #不会锁

总结：用索引字段做为条件进行修改时， 是否表锁的取决于这个索引字段能否确定记录唯一，当索引值对应记录不唯一，会进行锁表，相反则行锁。

2）：使用 delete

　　如果有两个delete ： kid1 与 kid2 是索引字段

　　1）：语句1 delete from table where kid1=1 and kid2=2;

　　2）：语句2 delete from table where kid1=1 and kid2=3;

　　　　 # 这样的两个delete 是不会锁表的

　　1）：语句1 delete from table where kid1=1 and kid2=2;

　　2）：语句2 delete from table where kid1=1 ;

　　　　 # 这样的两个delete 会锁表

总结：同一个表，如果进行删除操作时，尽量让删除条件统一，否则会相互影响造成锁表

以上就是蓝队云的全部分享，蓝队云官网上拥有完善的技术支持库可供参考，大家可自行查阅，更多技术问题，可以直接咨询。同时，蓝队云整理了运维必备的工具包免费分享给大家使用，需要的朋友可以直接咨询。

更多技术知识，蓝队云期待与你一起探索。