mysql-innodb存储引擎锁详解

锁机制用于管理对共享资源（并不仅仅是‘行记录’）的并发访问

如：在操作缓冲池中的LRU列表（删除、添加、移动列表中的元素），为了保证一致性，也可以加锁

innodb实现了两种标准的行级锁：

共享锁（S）：允许事务读一行数据

排他锁（X）：允许事务删除或者更新一行数据

说明:当一个事务已经获得共享锁，另外的事务可以立即获得共享锁，但是想要获得排他锁就必须要等共享锁释放之后才可以获得。

innodb还支持多粒度锁定，支持两种意向锁：

意向共享锁（IS）：事务想要获得一个表中某几行的共享锁

意向排他锁（IX）：事务想要获得一个表中某几行的排他锁

说明：innodb支持的是行级别的锁，所以意向锁不阻塞除全表扫之外的任何请求

查看锁的信息方法：

1.通过show engine innodb status查看当前请求锁的信息

2.通过information_schema下面的innodb_trx、innodb_locks、innodb_lock_waits三张表监控当前事务和可能存在的锁

innodb_trx（当前运行的innodb的事务）的组成：

trx_id：innodb存储引擎内部唯一的事务ID

trx_state：当前事务的状态

trx_started：事务的开始时间

trx_requested_lock_id：等待事务的锁ID；如trx_state的状态为lock wait则代表当前的事务等待之前事务占用所资源的ID，不是lock wait则为NULL

trx_wait_started：事务等待开始的时间

trx_weight：事务的权重，反映一个事务修改和锁住的行数。当发生死锁需要回滚时，该值越小则先回滚

trx_mysql_thread_id：mysql中的线程ID，通过show processlist显示

trx_query：事务运行的SQL语句（有时会显示NULL）

innodb_locks（查看锁）的组成：

lock_id：锁ID

lock_trx_id：事务ID

lock_mode：锁的模式

lock_type：锁的类型，表锁还是行锁

lock_table：要加锁的表

lock_index：锁的索引

lock_space：innodb存储引擎表空间的ID号

lock_page：被锁住的页的数量。若是表锁，则该值为NULL

lock_rec：被锁住的行的数量。若是表锁，则该值为NUll

lock_data：被锁住的行的主键值。当是表锁，则该值为NULL

innodb_lock_waits（当前的等待）的组成

requesting_trx_id：申请锁资源的事务ID

requesting_lock_id：申请的锁的ID

blocking_trx_id：阻塞的事务ID

blocking_trx_id：阻塞的锁的ID

一致性的非锁定读操作

一致性非锁定读操作（consistent nonlocking read）：innodb通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据

如：当读取的行正在执行update、delete操作，这是读取操作不会因此而等待行上锁的释放，而是会去读取行的一个快照数据。如图，

说明：快照数据的实现是通过undo段（用来在事务中回滚数据）来实现的，且读取快照数据不用加锁。大大提高了数据读取的并发性

注：在不同的隔离级别下读取的方式是不同的，且对于快照数据的定义也不同。

在RC隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据

在RR隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本

innodb对于select语句支持两种的加锁操作：

select...for update:对读取的行记录加一个X锁，其他事务想在这些行上加任何锁都要被堵塞

select...lock in share mode:对读取的行记录加上一个S锁，其他事务可以向被锁定的记录加S锁，但是对于加X锁，则会被阻塞

注：以上两个语句必须在同一事务中，当事务提交了，锁也就释放了。因此在使用上面两个语句是必须使用begin、start transaction或者set autocommit=0

自增长和锁

auto-inc locking（特殊的表锁）的实现方式：每个含有自增长的表都有一个自增长计数器，当对该表进行插入时，这个计数器会被初始化，通过（select max(auto_inc_col) from t for update）得到计数器的值，插入操作会依据这个自增长的计数器值加1赋予自增长列。

该锁不是在一个事务完成后才释放的，而是在完成自增长值插入的SQL语句后立即释放的（为了提高插入的性能）

auto-inc locking存在的问题：

1.对于有自增长值得列的并发插入性能较差，必须等待前一个插入的完成（虽然不用等待事务的完成）

2.对于insert...select的大数据量的插入，会影响插入性能（因为另一个事务中的插入会被阻塞）

自增长插入的分类：

insert-like：所有的插入语句，如insert、replace、insert...select、replace...select、load data

simple inserts：在插入前就确定行数的语句（包括insert、replace等）。不包含insert...on duplicate key update这类SQL语句

bulk inserts：插入前不能确定插入行数的语句。如insert...select,replace...insert,load data

mixed-mode inserts：插入中有一部分的值是自增长的，有一部分是确定的。

参数innodb_autoinc_lock_mode的可选值：

0：通过表锁的auto-inc locking方式来实现自增长实现方式

1（默认值）：对simple inserts会用互斥量对内存中的计数器进行累加；对bulk inserts使用传统的auto-inc locking；如果不进行回滚的操作，对于自增长的增长还是连续的。注意:已经使用auto-inc locking 的方式产生自增长，这时需要进行simple inserts的操作时，需要等待auto-inc locking的释放

2：所有insert-like自增长值的产生都是通过互斥量，而不通过auto-inc locking （性能最高）。但是每次插入时，自增长的值可能不是连续的（因为并发插入的存在），同时基于statement-base replication会出现问题（使用该模式，任何时候都需要使用row-base replication ）。

注：innodb存储引擎下，自增长值得列必须是索引，并且是索引的第一列

外键和锁

在innodb中，对于一个外键列，如果没有显示的对这个列加索引，innodb会自动对其加一个索引（避免表锁）

对于外键值得插入和更新，首先需要查询父表中的记录，即select表（通过select... lock in share mode方式）主动给父表加上一个S锁

锁的算法

innodb有3种行锁：

record lock：单个行记录上的锁

gap lock：间隙锁，锁定一个范围，但不包含记录本身

next-key lock：gap lock+record lock 锁定一个范围，并且锁定记录本身

共享next-key lock;排他next-key lock

record lock总会去锁住索引记录，如果没有设置任何一个索引，innodb会使用隐式的主键来进行锁定

在RR隔离级别下，next-key lock是默认的行记录锁定

译者介绍：家华，从事mysqlDBA的工作，记录自己对mysql的一些总结