数据库——MySQL之锁

数据库—MySQL之锁

brycezou@163.com

        在深入阅读《MySQL技术内幕》的基础上，结合实践整理得到本文，希望对大家有所帮助。

1、锁概述

• 开发多用户OLTP应用程序的难点：

  • 最大程度地利用数据库进行并发访问
  • 确保每个用户能以一致的方式读取和修改数据

• 锁用于协调对共享资源的访问。锁是数据库系统区别于文件系统的一个关键特性，数据库系统使用锁是为了支持对共享资源的并发访问、提供数据的完整性和一致性。

• 不同数据库中锁机制的实现方式大不相同。在Microsoft SQL Server中，锁是一种稀有的资源，锁越多，开销就越大，因此会有锁升级，行锁升级到表锁。而InnoDB存储引擎提供了一致性非锁定读、行级锁支持，且行级锁没有相关的开销，可以同时得到并发性和一致性。

• InnoDB存储引擎实现了两种标准的行级锁：共享锁 S Lock，允许事务读一行数据；排它锁 X Lock，允许事务删除或更新一行数据。当一个事务已经获得了某一行的共享锁时，那么其它事务可以立即同时获得该行的共享锁；但如果有事务想获得该行的排它锁，则必须等待所有事务释放该行上的共享锁。

2、查询锁和事务的状态

• 可以通过 information_schema 库下的 INNODB_LOCK_WAITS、INNODB_LOCKS和INNODB_TRX 这三张表来监控当前的事务和锁状态，下面给出一个例子。

• 这是第1个mysql客户端：开始一个事务，但不提交

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set b='888' where a=5;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

• 这是第2个mysql客户端：开始一个共享模式的查询，该语句一直处于等待状态，直到超时

mysql> select * from t where a=5 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

• 在上面的查询超时前，在第3个mysql客户端中查询当前锁和事务的状态（结果只保留了部分列）

mysql> use information_schema;
Database changed

mysql> select * from INNODB_TRX;
+--------+-----------+-----------------------+----------------------------------------------+
| trx_id | trx_state | trx_requested_lock_id | trx_query                                    |
+--------+-----------+-----------------------+----------------------------------------------+
| 35610  | LOCK WAIT | 35610:103:3:13        | select * from t where a=5 lock in share mode |
| 35609  | RUNNING   | NULL                  | NULL                                         |
+--------+-----------+-----------------------+----------------------------------------------+
2 rows in set (0.01 sec)

mysql> select * from INNODB_LOCKS;
+----------------+-------------+-----------+-----------+------------+------------+-----------+
| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_data |
+----------------+-------------+-----------+-----------+------------+------------+-----------+
| 35610:103:3:13 | 35610       | S         | RECORD    | `test`.`t` | PRIMARY    | 5         |
| 35609:103:3:13 | 35609       | X         | RECORD    | `test`.`t` | PRIMARY    | 5         |
+----------------+-------------+-----------+-----------+------------+------------+-----------+
2 rows in set (0.00 sec)

mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 35610             | 35610:103:3:13    | 35609           | 35609:103:3:13   |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.01 sec)

• 上述结果中主要字段的含义为

在 INNODB_LOCK_WAITS 中，
    requesting_trx_id：申请锁资源的事务ID 
    requested_lock_id：申请的锁的ID
    blocking_trx_id：阻塞的事务ID 
    blocking_lock_id：阻塞的锁的ID 
在 INNODB_LOCKS 中，
    lock_id：锁的ID 
    lock_trx_id：事务ID 
    lock_mode：锁的模式，共享锁或排它锁 
    lock_type：锁的类型，表锁或行锁 
    lock_table：要加锁的表 
    lock_index：锁的索引
    lock_data：被锁住的行的主键值。当是表锁时，该值为NULL。该值不完全可信 
在 INNODB_TRX 中，
    trx_id：InnoDB存储引擎内部唯一的事务ID 
    trx_state：当前的事务状态 
    trx_requested_lock_id：等待事务的锁ID 
    trx_query：事务运行的SQL语句 
    trx_weight：事务的权重，反映了一个事务修改和锁住的行数。在InnoDB存储引擎中，当发生死锁要回滚时，会选择该值最小的进行回滚

        显然，事务 35609 目前正在运行，而事务 35610 正处于 LOCK_WAIT 状态，运行的SQL语句是 select * from t where a=5 lock in share mode。详细过程可理解为：

        首先，在事务 35609 中，由于要更新记录，因此，对主键值 a=5 的记录加上了排它锁 X ，锁 ID 为 35609:103:3:13。

        其次，在事务 35610 中，由于要以共享模式查询，因此向表中主键值 a=5 的记录申请了一个共享锁 S。但由于该记录已经在事务 35609 中获得了一个锁 X ，且事务没有提交，锁 X 还没有被释放，所以事务 35610 只能继续等待锁。

3、一致性非锁定读

• InnoDB存储引擎通过行的多版本控制来实现一致性的非锁定行读（consistent nonblocking read）。如果要读取的行被加上了排它锁 X（正在执行UPDATE、DELETE操作），这时并不会等待锁被释放，而是会读取行的一个快照数据。

• 快照数据是指当前行的历史版本数据，它是通过Undo段来实现的，而Undo段用来在事务中回滚数据，因此快照数据本身没有额外的开销。此外，快照数据是不会被上锁的，因为没有必要修改历史数据。

• 一个行可能有多个历史版本，即有多份快照数据。

• 非锁定读的机制大大提高了数据读取的并发性，这是InnoDB默认的读取方式。但并不是每个事务隔离级别下都是一致性读，而且，就算都是一致性读，快照数据的定义也不同：

  • 在Read Committed隔离级别下，读取被锁定行的最新一份快照数据。
  • 在Repeatable Read（InnoDB默认）隔离级别下，读取被锁定行在事务开始时的快照数据。

• 下面是两个时序有重合的事务，用来模拟并发情况。在2种不同的事务隔离级别下，给出 Session A 的查询结果
  

  Time Line	Session A	Session B	Repeatable Read	Read Committed
  ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓	begin; select * from t where a=5;		555	555
  		begin; update t set b=8888 where a=5;
  	select * from t where a=5;		555	555
  		commit;
  	select * from t where a=5; commit;		555	8888

• 默认情况下，InnoDB存储引擎的 select 操作使用一致性非锁定读。但是在某些情况下，我们需要对读取操作进行加锁。InnoDB支持如下两种加锁操作，它们必须存在于事务中，当事务提交时，锁也就释放了。使用这两种操作时必须使用begin、start transaction或set autocommit=0。

  • select … for update：对读取的行记录加 X 锁，其它事务想在此行上加任何锁都会被阻塞。
  • select … lock in share mode：对读取的行记录加 S 锁，其它事务可以向被锁定的记录加 S 锁，但 X 锁会被阻塞。

4、锁的算法

        在 Repeatable Read 隔离级别，InnoDB存储引擎对于行的锁定默认使用共享的或者排它的 Next-Key Lock 算法： 锁定一个范围，并且锁定记录本身。下面的例子中，查询锁定的数值区间是 $(-\infty,6]$，因此，更新3和6都会被阻塞，而更新8则会成功。其它两种行锁算法分别是 Record Lock 和 Gap Lock。

Time Line	Session A	Session B
↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓	begin; select * from t where a < 6 lock in share mode;
		begin; update t set b=333 where a=3; ERROR 1205 (HY000):Lock wait timeout exceeded;
		update t set b=666 where a=6; ERROR 1205 (HY000):Lock wait timeout exceeded;
		update t set b=888 where a=8; Query OK, 1 row affected (0.00 sec) commit;
	commit;

5、锁的问题

        通过锁可以实现事务的隔离性要求，使事务可以并发地工作，但锁同样会带来问题。受事务隔离性的限制，锁只会带来3种问题：丢失更新、脏读、不可重复读。如果可以避免这3种情况，将不会产生并发异常。

• 丢失更新：要避免丢失更新，可以使用排它锁将并发事务改为串行事务。
• 脏读：读到了脏数据，即一个事务可以读到另一个事务中未提交的数据，这显然违反了事务的隔离性原则。脏数据是指，在缓冲池中已被修改但还未提交的数据。脏读现象在生产环境中并不常发生，因为脏读的必要条件是隔离级别设置为READ UNCOMMITED，而目前主流数据库其默认的事务隔离级别至少都设置为READ COMMITED：InnoDB是READ REPEATABLE，Microsoft SQL Server和Oracle都是READ COMMITED。
• 不可重复读：在一个事务内多次读同一数据，读到的结果不相同。和脏读不同的是，不可重复读，读到的确实是已经提交的数据，但其违反了事务的一致性要求。在隔离级别
  READ COMMITED下容易出现不可重复读，但一般来说，不可重复读是可以接受的。InnoDB存储引擎默认的事务隔离级别是READ REPEATABLE，避免了不可重复读。

6、阻塞

        当一个资源已被一个事务占有时，另一个事务执行mutex_enter函数会发生等待，这就是阻塞。阻塞可以保证事务并发且正常地运行。 默认情况下，InnoDB存储引擎不会回滚超时引发的错误异常(默认值innodb_rollback_on_timeout=OFF)，参数innodb_lock_wait_timeout用来设置等待时间，默认为50秒。 当事务发生超时异常时，既没有commit，也没有rollback，这是十分危险的，用户必须进行善后处理。

7、死锁

        如果程序是串行的，则不可能发生死锁。死锁只发生于并发情况，死锁的根本原因是互相等待对方的资源。InnoDB存储引擎有一个后台的锁监控线程，可以自动侦测到大多数死锁，并且在检测到死锁后，会马上回滚一个事务，不需要人为干预。 InnoDB会自动为外键添加索引，避免由此产生的死锁。