MySQL InnoDB下的锁问题

背景知识

    InnoDB相比较MyISAM一是支持事务，二是支持了行级锁，提到InnoDB锁问题就不得不提到事务，所以在这之前先了解下事务的一些知识

事务及其ACID属性

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

• 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

• 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

• 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

• 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

并发事务处理带来的问题

    相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但是并发事务处理也会带来以下问题。

• 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

• 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

• 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事务隔离级别

    MySQL数据库实现事务隔离的方式，基本上可分为两种。

• 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改

• 不加锁，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同意数据的多个版本，因此，这种技术叫做数据多版本并发控制简称MVCC。

四种事务隔离级别：

• READ UNCOMMITTED（读未提交）：事务A和B操作同一数据，事务A能够读到事务B未提交的数据，会产生幻读，不可重复读，脏读

• READ COMMITTED（读已提交）：事务A和B操作同一数据，事务A能够读到事务B更新的数据，会产生幻读和不可重复度

• REPEATABLE READ（可重复读）：事务A和事务B操作同一数据，事务A不能读到事务B已经插入的数据，会产生幻读

• SERIALIZABLE（串行化）：所有事务都必须保证串行执行，不会产生脏读，幻读，不可重复度

获取InnoDB行锁争用情况

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

mysql> 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.04 sec)

复制代码

如果锁争用比较严重，Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比较高，可以通过查询information_schema数据库中相关的表来查看锁情况，或者通过设置InnodDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析其原因。

（1）通过查询information_schema数据库中的innodb_locks表了解锁的等待情况：

mysql> use information_schema;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

  


Database changed

mysql> select * from innodb_locks;

Empty set, 1 warning (0.01 sec)

  


mysql>

复制代码

（2）通过设置InnoDB Monitors观察锁冲突情况：

mysql> create table innodb_monitor(a INT) ENGINE=INNODB;

Query OK, 0 rows affected (0.05 sec)

复制代码

然后通过下面语句来进行查看：

mysql> show engine innodb status;

| Type | Name | Status

| InnoDB | |

...

------------

TRANSACTIONS

------------

Trx id counter 6076

Purge done for trx's n:o < 6071 undo n:o < 0 state: running but idle

History list length 0

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 421657844005624, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421657844004704, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421657844006544, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

--------

FILE I/O

--------

...

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

0 read views open inside InnoDB

Process ID=1, Main thread ID=140182422546176, state: sleeping

Number of rows inserted 55250, updated 1240, deleted 376, read 22512

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

==================