MySQL——数据库锁

一、锁的定义？

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

 

二、数据库锁的分类

（1）从数据的操作类型可分为：读锁、写锁。

• 读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。

• 写锁(排它锁)：当前写操作没有完成前，它会阻断其他写锁和读锁。

（2）从数据的操作粒度可分为：表锁、行锁。

三、数据库事务

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

原子性 (Atomicity)：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

一致性(Consistent)：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改以保持数据的完整性；事务结束时，所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。

隔离性 (lsolation)：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性 (Durable)：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

 

四、并发事务导致的问题

• 更新丢失（Lost Update）

• 脏读（Dirty Reads）

• 不可重复读（Non-Repeatable Reads）

• 幻读（Phantom Reads）

（1）更新丢失（Lost Update）

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题一一最后的更新覆盖了由其他事务所做的更新。

（2）脏读

事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

（3）不可重复读
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读取的数据已经发生了改变、或某些记录已经被删除了，这种现象就叫做“不可重复读”。即事务A读取到了事务B已经提交的修改数据，不符合隔离性。

（4）幻读

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。即事务A读取到了事务B提交的新增数据，不符合隔离性。

读和脏读有点类似：脏读是事务A读取事务B里面修改的数据，幻读是事务A读取事务B里面新增的数据。

 

五、事务的隔离级别

 

六、间隙锁

（1）什么是间隙锁？

当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做“间隙 (GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 (Next-Key锁)。

（2）间隙锁危害

因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范

围键值之后，即使某些不存在的键值也会被无辜的锁定，从而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

示例：

#session1会话页面执行SQL

# b为字符串类型
UPDATE test_innodb_lock set b = '6666' where a >1 and a < 6;

#commit之前，session2会话页面新增SQL语句会一直处于阻塞状态
commit;

#session2会话页面

insert
	into
	test_innodb_lock
values(3, '3');

commit;

 

七、如何锁定一行？

（1）新建一个session会话页面

begin;

SELECT * from test_innodb_lock where a = 7 for UPDATE ;

#在commit之前，session2会话页面会一直处于阻塞状态
UPDATE test_innodb_lock set b = '7007' where a = 7;

commit;

（2）同时新建另一个session2会话页面，执行以下操作

SELECT * from test_innodb_lock where a = 7;

UPDATE test_innodb_lock set b = '7008' where a = 7;

commit;

八、行锁失效升级为表锁

（1）session1会话页面执行SQL

# session1会话页面执行sql
SELECT * from test_innodb_lock where a = 7 ;

# b为字符串类型
UPDATE test_innodb_lock set a = 7777 where b = 7777;

commit;

（2）session2会话页面执行SQL

# 索引失效导致行锁升级为表锁

SELECT * from test_innodb_lock ;

UPDATE test_innodb_lock set b = '999900' where a = 9;

commit;

注：字段“a”、“b”均为索引列，a为int类型，b为字符串。

mysql> show index from test_innodb_lock;
+------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_innodb_lock |          1 | test_innodb_a_ind |            1 | a           | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_innodb_lock |          1 | test_innodb_b_ind |            1 | b           | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

执行结果：session1会话中字段“b”做类型转换，导致索引失效，造成行锁升级为表锁，session2会话页面执行update 语句时会一直处于阻塞状态，直至session1会话页面 commit。

 

九、如何分析行锁

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

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 93652 |
| Innodb_row_lock_time_avg      | 15608 |
| Innodb_row_lock_time_max      | 51019 |
| Innodb_row_lock_waits         | 6     |
+-------------------------------+-------+

Innodb_row_lock_current_waits: 当前正在等待锁定的数量;

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

Innodb_row_lock_time_avg: 每次等待所花平均时间;

Innodb _row_lock time max: 从系统启动到现在等待最常的一次所花的时间

Innodb row_lock waits: 系统启动后到现在总共等待的次数:

 

其中，比较重要的三个参数：

• Innodb_row_lock_time_avg: 每次等待所花平均时间;

• Innodb row_lock waits: 系统启动后到现在总共等待的次数:

• Innodb_row_lock_time: 从系统启动到现在锁定总时间长度;

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

 

十、行锁优化建议

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

（2）合理设计索引，尽量缩小锁的范围。

（3）尽可能用较少的检索条件，避免间隙锁。

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

（5）尽可能低级别事务隔离。