维基教科书

MySQL/Lock

< MySQL

操作之前先上锁，称为悲观锁(Pessimistic Lock)。

InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。表级锁适合于以查询为主，只有少量按索引更新数据的应用，如Web应用；而行级锁适合于有大量按索引并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

InnoDB默认的读取方式

编辑

一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本并发控制(MVVC)读取当前数据库中行数据的方式。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB会去读取行的一个快照。InnoDB是通过undo log来实现MVVC。undo log本身用来在事务中回滚数据，因此快照数据本身是没有额外开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

事务隔离级别为“提交读”和“可重复读”时，InnoDB使用一致性非锁定读。二者区别在于：“提交读”事务隔离级别下，一致性非锁定读总是读取被锁定行的最新一份快照数据。而在“可重复读”事务隔离级别下，则读取本事务开始时的行数据版本。

MyISAM表锁

编辑

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。 默认情况，select语句自动获得读锁，update与delete语句自动获得写锁。语法为： 

Lock tables {Table_i_Name read|write [local]}+

在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表（会报错）；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作（会报错）。实际上在自动加锁情况下也基本如此，总是一次获得SQL语句所需要的全部锁。这保证了不会出现死锁（Deadlock Free）。

LOCK TABLES的“local”选项，其作用就是允许其他用户在表尾并发插入记录。

使用LOCK TABLES时，同一个表在SQL语句中用别名出现多少次，就要通过与SQL语句中相同的别名来锁定多少次，否则也会报错。例如： 

 lock table actor as a read,actor as b read;

 unlock table

数据库的状态变量table_locks_waited（不能立即获取表级锁而需要等待的锁请求次数）和table_locks_immediate（能够立即获得表级锁的锁请求次数）

默认的表锁调度机制是写优先。

InnoDB的行锁

编辑
  • 行锁
    • 共享锁（S），又称读锁：阻止其他事务加排他锁。其他事务只能再加共享锁。当前事务不能对行做任何修改。
    • 排他锁（Ｘ），又称写锁：允许获取排他锁的事务更新数据，阻止其他事务加共享读锁和排他写锁。由于普通select语句默认不会加任何锁类型，所以仍然可以读。
  • 表锁：意向锁（Intention Locks）
    • 意向共享锁（IS）：事务打算给数据行加共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
    • 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
共享表锁(TR) 排他表锁(TW) 意向共享锁(IS) 意向排他锁(IX)
共享表锁(TR) 兼容 冲突 兼容 冲突
排他表锁(TW) 冲突 冲突 冲突 冲突
意向共享锁(IS) 兼容 冲突 兼容 兼容
意向排他锁(IX) 冲突 冲突 兼容 兼容

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。事务可以通过以下select语句显式给记录集加共享锁或排他锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作：

  • 共享锁（S），事务不会修改该行：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
  • 排他锁（X），事务可能会修改该行：SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。访问不同行的记录，但如果是使用相同的索引键，会出现锁冲突。MySQL会优化查询的执行方式，未必会使用index的行锁，需要用explain查看一条语句的实现方式。

下述语句查询行锁的冲突情况： 

show status like 'innodb_row_lock%';

间隙锁（Next-Key锁）

编辑

当用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，并对键值在条件范围内但并不存在的记录（间隙GAP）也会被加锁，这种锁机制就是间隙锁 （Next-Key锁）。 这防止了幻影读，即一个事务A第一次查询获得的结果后，别的事务插入了新事物，事务A第二次查询时发现结果不同了。还有满足事务恢复和复制的需求。

在实际应用开发中，尤其是并发插入的事务比较多的应用，要尽量优化业务逻辑，尽量使用相等条件来访问、更新数据，避免使用范围条件。

检索自“https://zh.wikibooks.org/w/index.php?title=MySQL/Lock&oldid=122436”