MySQL数据库锁—(二)Innodb锁定模式

总的来说,Innodb的锁定机制和Oracle数据库有不少相似之处。Innodb的行级锁定同样分为两种类型,共享锁和排他锁,而在锁定机制的实现过程中为了让行级锁定和表级锁定共存,Innodb也同样使用了意向锁(表级锁定)的概念,也就有了意向共享锁和意向排他锁这两种。

1、共享锁—读锁
共享锁,简称(S锁),用于不更改或不更新数据的操作(只读操作),例如select
当事务对数据加上读锁后,其他事务只能对该数据加读锁,不能做任何修改操作,也就是不能添加写锁

2、排他锁—写锁
排他锁,简称(X锁),用于数据修改操作(写操作),例如insert、update、delete
当事务对数据加上写锁后,其他事务既不能对该数据添加读锁,也不能对该数据添加写锁,写锁与其他锁都是互斥的。只有当前数据写锁被释放后,其他事务才能对其添加写锁或者是读锁

3、意向锁
意向锁分为:意向共享锁(IS)、意向排他锁(IX)
意向锁的作用就是当一个事务在需要获取资源锁定的时候,如果遇到自己需要的资源已经被排他锁占用的时候,该事务可以需要锁定行的表上面添加一个合适的意向锁。
如果自己需要一个共享锁,那么就在表上面添加一个意向共享锁。
如果自己需要的是某行(或者某些行)上面添加一个排他锁的话,则先在表上面添加一个意向排他锁。

感觉意向锁是一种预约?不知道对不对

4、Innodb各事务隔离级别下锁定及死锁
Innodb实现的在ISO/ANSI SQL92规范中所定义的Read UnCommited,Read Commited,Repeatable Read和Serializable这四种事务隔离级别。同时,为了保证数据在事务中的一致性,实现了多版本数据访问。

在Innodb的事务管理和锁定机制中,有专门检测死锁的机制,会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当Innodb检测到系统中产生了死锁之后,Innodb会通过相应的判断来选择产生死锁的两个事务中较小的事务来回滚,而让另外一个较大的事务成功完成。那Innodb是以什么来为标准判定事务的大小的呢?MySQL官方手册中也提到了这个问题,实际上在Innodb发现死锁之后,会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多,在死锁中就越不会被回滚掉。但是有一点需要注意的就是,当产生死锁的场景中涉及到不止Innodb存储引擎的时候,Innodb是没办法检测到该死锁的,这时候就只能通过锁定超时限制来解决该死锁了。

5、Innodb行锁优化建议
要想合理利用Innodb的行级锁定,做到扬长避短,我们必须做好以下工作:
a)尽可能让所有的数据检索都通过索引来完成,从而避免Innodb因为无法通过索引键加锁而升级为表级锁定;
b)合理设计索引,让Innodb在索引键上面加锁的时候尽可能准确,尽可能的缩小锁定范围,避免造成不必要的锁定而影响其他Query的执行;
c)尽可能减少基于范围的数据检索过滤条件,避免因为间隙锁带来的负面影响而锁定了不该锁定的记录;
d)尽量控制事务的大小,减少锁定的资源量和锁定时间长度;
e)在业务环境允许的情况下,尽量使用较低级别的事务隔离,以减少MySQL因为实现事务隔离级别所带来的附加成本;

由于Innodb的行级锁定和事务性,所以肯定会产生死锁,下面是一些比较常用的减少死锁产生概率的的小建议:
a)类似业务模块中,尽可能按照相同的访问顺序来访问,防止产生死锁;
b)在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;
c)对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率;

6、系统锁定争用情况查询
MySQL实现的表级锁定的争用状态变量:


show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 105   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 1     |
| Table_open_cache_misses    | 6     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.01 sec)

● Table_locks_immediate:产生表级锁定的次数;
● Table_locks_waited:出现表级锁定争用而发生等待的次数;
两个状态值都是从系统启动后开始记录,没出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高,那么说明系统中表级锁定争用现象比较严重,就需要进一步分析为什么会有较多的锁定资源争用了。

Innodb所使用的行级锁定状态变量:


show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.01 sec)

● 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(等待总时长)这三项。尤其是当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手指定优化计划。

7、Innodb的monitor功能
show engine innodb status;
会输出很多信息。。。