关于MySQL InnoDB存储引擎中的锁


最近碰到很多锁问题，所以解决了后，仔细再去阅读了关于锁的书籍，整理如下：

1，锁的种类

Innodb存储引擎实现了如下2种标准的行级锁： 

  1. 共享锁(S lock)，允许事务读取一行数据。
  2. 排它锁(X lock)，允许事务删除或者更新一行数据。

当一个事务获取了行r的共享锁，那么另外一个事务也可以立即获取行r的共享锁，因为读取并未改变行r的数据，这种情况就是锁兼容。但是如果有事务想获得行r的排它锁，则它必须等待事务释放行r上的共享锁—这种情况就是锁不兼容，二者兼容性如下表格所示： 

排它锁和共享锁的兼容性

 

X 排它锁

S 共享锁

X 排它锁

冲突

冲突

S 共享锁

冲突

兼容

2，锁的扩展

Innodb存储引擎支持多粒度锁定，这种锁定允许在行级别上的锁和表级别上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，就是意向锁。意向锁是表级别的锁，其设计目的主要是为了在一个事务中揭示下一行将被请求的锁的类型。它也分为两种：

  1. 意向共享锁（IS Lock），事务想要获得一个表中某几行的共享锁。
  2. 意向排它锁（IX Lock），事务想要获得一个表中某几行的排它锁。

由于InnoDB支持的是行级别锁，所以意向锁其实不大会阻塞除了全表scan以下的任何请求。共享锁、排它锁、意向共享锁、意向排它锁相互之前都是有兼容/互斥关系的，可以用一个兼容性矩阵表示(y表示兼容，n表示不兼容)，如下所示:

 

X 排它锁

S 共享锁

IX 意向排它锁

IS 意向共享锁

X 排它锁

冲突

冲突

冲突

冲突

S 共享锁

冲突

兼容

冲突

兼容

IX 意向排它锁

冲突

冲突

兼容

兼容

IS 意向共享锁

冲突

兼容

兼容

兼容

解析：X和S的相互兼容关系step1描述过了，IX和IS的相互关系全部是兼容，这也很好理解，因为它们都只是“有意”，还处于YY阶段，没有真干，所以是可以兼容的；

剩下的就是X和IX，X和IS, S和IX， S和IS的关系了，我们可以由X和S的关系推导出这四组关系。

简单的说：X和IX的=X和X的关系。为什么呢？因为事务在获取IX锁后，接下来就有权利获取X锁。如果X和IX兼容的话，就会出现两个事务都获取了X锁的情况，这与我们已知的X与X互斥是矛盾的，所以X与IX只能是互斥关系。其余的三组关系同理，可用同样的方式推导出来。

MySQL InnoDB存储引擎锁机制实验

InnoDB存储引擎的启动、关闭与恢复

MySQL InnoDB独立表空间的配置

MySQL Server 层和 InnoDB 引擎层 体系结构图

InnoDB 死锁案例解析

MySQL Innodb独立表空间的配置

3，模拟锁场景

在InnoDB Plugin之前，我们只能通过SHOW FULL PROCESSLIS和SHOW ENGINE INNODB STATUS来查看当前的数据库请求，然后再判断事务中锁的情况。新版本的InnoDB Plugin中，在information_schema库中添加了3张表，INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS。通过这3个表，可以更简单的监控当前的事务并且分析可能存在的锁问题。如果数据库正常运行，这3个表都是空的，没有任何记录。

3.1，开启事务t1、t2，模拟锁

开启2个session窗口，并且开启2个事务t1和t2。

在第一个窗口开启事务t1执行一个锁定操作，如下t1事务窗口界面：

 

mysql> set autocommit =0;

Query OK, 0 rows affected (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

  开始执行锁定操作

mysql> select * from test.t1 where a<5 for update;

+---+----+----+

| a | b  | c  |

+---+----+----+

| 1 | c2 | c2 |

| 2 | a  |    |

| 3 | r5 | r3 |

| 4 | r4 | r5 |

+---+----+----+

4 rows in set (0.00 sec)

 

mysql>

这个时候，事务t1已经锁定了表t1的所有a<5的数据行，然后去第二个窗口开启第二个事务t2，如下，会看到update语句一直在等待事务t1释放锁资源，过了几秒后，会有报错信息，如下t2事务窗口界面：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> update test.t1 set b='t2' where a=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql>

 

3.2，通过3个系统表来查看锁信息

l  1，INNODB_TRX表

先看下表的重要字段以及记录的信息

a)        trx_id：innodb存储引擎内部事务唯一的事务id。

b)        trx_state：当前事务的状态。

c)        trx_started：事务开始的时间。

d)        trx_requested_lock_id：等待事务的锁id，如trx_state的状态为LOCK WAIT，那么该值代表当前事务之前占用锁资源的id，如果trx_state不是LOCK WAIT的话，这个值为null。

e)        trx_wait_started：事务等待开始的时间。

f)          trx_weight：事务的权重，反映了一个事务修改和锁住的行数。在innodb的存储引擎中，当发生死锁需要回滚时，innodb存储引擎会选择该值最小的事务进行回滚。

g)        trx_mysql_thread_id：正在运行的mysql中的线程id，show full processlist显示的记录中的thread_id。

h)        trx_query：事务运行的sql语句，在实际中发现，有时会显示为null值，当为null的时候，就是t2事务中等待锁超时直接报错（ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction）后，trx_query就显示为null值

……

因为前面模拟了事务锁场景，开启了t1和t2事务，现在去查看这个表信息，会有2条记录如下：

mysql> select * from INNODB_TRX\G

*************************** 1. row ***************************

                    trx_id: 3015646

                trx_state: LOCK WAIT

              trx_started: 2014-10-07 18:29:39

    trx_requested_lock_id: 3015646:797:3:2

          trx_wait_started: 2014-10-07 18:29:39

                trx_weight: 2

      trx_mysql_thread_id: 18

                trx_query: update test.t1 set b='t2' where a=1

      trx_operation_state: starting index read

        trx_tables_in_use: 1

        trx_tables_locked: 1

          trx_lock_structs: 2

    trx_lock_memory_bytes: 376

          trx_rows_locked: 1

        trx_rows_modified: 0

  trx_concurrency_tickets: 0

      trx_isolation_level: READ COMMITTED

        trx_unique_checks: 1

    trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

 trx_adaptive_hash_latched: 0

 trx_adaptive_hash_timeout: 10000

          trx_is_read_only: 0

trx_autocommit_non_locking: 0

*************************** 2. row ***************************

                    trx_id: 3015645

                trx_state: RUNNING

              trx_started: 2014-10-07 18:29:15

    trx_requested_lock_id: NULL

          trx_wait_started: NULL

                trx_weight: 2

      trx_mysql_thread_id: 17

                trx_query: NULL

      trx_operation_state: NULL

        trx_tables_in_use: 0

        trx_tables_locked: 0

          trx_lock_structs: 2

    trx_lock_memory_bytes: 376

          trx_rows_locked: 4

        trx_rows_modified: 0

  trx_concurrency_tickets: 0

      trx_isolation_level: READ COMMITTED

        trx_unique_checks: 1

    trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

 trx_adaptive_hash_latched: 0

 trx_adaptive_hash_timeout: 10000

          trx_is_read_only: 0

trx_autocommit_non_locking: 0

2 rows in set (0.00 sec)

 

mysql>

这里只是记录了一些当前正在运行的事务，比如事务t2正在运行trx_query: update test.t1 set b='t2' where a=1的sql语句，t1先执行，所以是trx_state: RUNNING先申请的资源一直在运行，而t2后run的所以是trx_state: LOCK WAIT一直在等待t1执行完后释放资源。 但是并不能仔细判断锁的一些详细情况，我们需要再去看INNODB_LOCKS表数据。

更多详情见请继续阅读下一页的精彩内容：

  • 1
  • 2
  • 3
  • 4
  • 下一页

相关内容