数据库锁的介绍

锁的定义：

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

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

生活例子：

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？

这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

从操作数据类型上分类：

分为读写锁：

Lock table book read,mylock write; //为book上读锁为mylock上写锁

show open tables; //查看被锁定表

unlock tables; //释放所有被锁定的表

读锁（共享锁）针对同一份数据多个线程对其读操作互不干扰。

写锁（独享锁）当前写操作没完成前会阻止其他的写锁和读锁。

从操作数据粒度上分类：

表锁（偏读）偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行锁 (偏写) 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

例子：（表锁中的读锁）

开两个会话窗口session1和session2，

在session1中锁住book表Lock table book read;(读锁)

在session1中和session2中都可以读取到book表，session1不能操作其他的表，就是不能执行sql语句了。session2不可以更新book表，其他什么操作都可以。

例子：(表锁中的写锁)

在session1中给book表加写锁Lock table book write;

在session1中可以对book表增删改查都可以，但是不能查别的表（必须unlock之后才能动其他表）

session2不可以对book表增删改查。（会阻塞）

小总结：

MyISAM存储引擎偏向读，在执行（select）前会自动给所涉及到的表加读锁，在执行增删改的时候会自动给所涉及表加写锁。

总之加读锁会影响写但不会影响读，写锁即会影响读也会影响写。

表锁分析

show status like ‘table%’; //查询表被锁情况


主要看第二个参数，如果第二个参数特别大，代表存在很严重的锁竞争情况。

行锁（写操作）

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

//创建一个innodb存储引擎的mylock表。

create table mylock (

a int(11),

b varchar(16)

)engine=innodb;

使用innodb存储引擎，默认事务是自动提交的，如果session1设置事务不自动提交（set Autocommit = 0）执行对id = 4这行数据进行更新，更新之后自己查看是可以查到更新后的数据的，如果没有commit，session2是查不到更新后的数据的，并且session2不能更新id = 4这行数据，只能更新其他行数据。

行锁失效：

如果索引失效，行锁会变表锁。并发变低！

优化建议：

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。（行锁是加到索引项上的，因此无索引会变表锁。）

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

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

锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。

间隙锁的危害：

就是如果是范围更新，mysql会锁住这个范围的所有行，即使这行不存在。

例如：


补充：

如何锁定一行

begin；

select * from book where id = 8 for update;

例如：

如何分析行锁定

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

show status like ‘innodb_row_lock%’;

对各个状态量的说明如下：

Innodb_row_lock_current_waits：当前正在等待锁定的数量；

Innodb_row_lock_time：从系统启动到现在锁定总时间长度；

Innodb_row_lock_time_avg：每次等待所花平均时间；

Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

对于这5个状态变量，比较重要的主要是

 Innodb_row_lock_time_avg（等待平均时长）

 Innodb_row_lock_waits（等待总次数）

 Innodb_row_lock_time（等待总时长）这三项。

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

最后可以通过

SELECT * FROM information_schema.INNODB_TRX\G;

来查询正在被锁阻塞的sql语句。