MySQL数据库锁

存在并发操作的时候，必然需要一种机制来保证数据的完整性和一致性。锁就是这样一种技术实现。

当多个用户并发地存取数据时，数据库会存在多个事务同时存取同一数据的情况。如果对此不加控制，就很有可能会读取和存储不正确的数据，破坏数据库的完整性和一致性。当事务在对某个数据进行操作前，先向系统发出请求，对其加锁。加锁后事务就对数据对象有了一定的控制。

在MySQL中存在多种锁，根据概念，可分为悲观锁和乐观锁。

• 悲观锁，也叫悲观并发控制，顾名思义就是对事务操作很悲观，认为每次拿数据都会对数据进行修改，因此每次读取数据都会加锁。当事务A对某行数据应用了锁，只有当事务A把锁释放以后，其他事务才能执行与该锁冲突的操作，这里的事务A施加的锁就叫悲观锁。

• 乐观锁，也叫乐观并发控制，顾名思义就是对事务操作比较乐观，认为多用户并发的事务在处理时不会互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了数据。如果有，那么当前正在提交的事务会进行回滚。

在MySQL数据库中，悲观锁依靠数据库提供的锁机制来实现的，乐观锁需要使用者自己去实现。

从锁的功能上看，可以分为共享锁、排他锁（独占锁）、意向锁等。共享锁也称S锁，排他锁也被称为X锁。

从锁的粒度来看，又可以分为表锁、页锁、行锁。在MySQL中，最常见的是表锁和行锁。其中MyISAM引擎只有表锁，而InnoDB既有表锁也有行锁。

表锁的特点是开销小，加锁块，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。相反，行锁的加锁开销比较大，发生冲突的概率比较低，但是并发度也高，带来的副反应就是死锁。而页锁则界于表锁和行锁之间。

1. MySQL表锁

MySQL的表锁有两种模式：表共享锁和表独占锁。

• 对于MyISAM的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 对于MyISAM的写操作，则会阻塞其他用户对于同一表的读和写请求；
• MyISAM的读和写操作之间，以及写和写操作之间是串行的。

如何加表锁

MyISAM表在执行查语句之前，会自动给涉及的所有表加读锁，在执行更新操作（增删改）前，会自动给涉及的表加写锁，不需要用户显式加锁。

给MyISAM表显式加锁，一般是为了一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。比如一个订单表order有total字段，有订单明细表order_detail记录一个订单多个产品的金额小计money。如果你要判断这两个表的金额是否相等，可能需要分别查询两个表的金额：

SELECT SUM(`money`) FROM `order_detail`;
SELECT SUM(`total`) FROM `order`;

如果不加锁，在执行一个表的查询过程中，order_detail表可能已经发生了改变，因此正确的做法是：

LOCK TABLES `order` READ LOCAL,`order_detail` READ LOCAL;
SELECT SUM(`money`) FROM `order_detail`;
SELECT SUM(`total`) FROM `order`;
UNLOCK TABLES;

需要说明的是，在锁表的同时加了local选项，其作用就是在满足MySQL表并发插入条件的情况下，允许其他用户在表尾插入数据。

在用LOCK TABLES给表显示加锁时，必须同时取得所有涉及表的锁，在执行LOCK TABLES后，当前会话只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能操作更新操作，非当前会话可以执行UPDATE操作，但会被阻塞，可以在满足一定条件时执行INSERT操作，不受阻塞。被加锁的会话不会读取到其他会话的INSERT数据，自动加锁的情况下也基本如此，这也是MyISAM表不会出现死锁的原因。

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制并发插入行为，因此上述提到的“满足一定条件”就是：

• concurrent_insert设置为0时，不允许并发插入。
• concurrent_insert设置为1时，如果MyISAM允许线程读表的同时，另一个线程从表尾插入记录。这是默认设置。
• concurrent_insert设置为2时，无论MyISAM有没有空间，都允许在表尾插入记录。

无论是读锁还是写锁，两者的机制和上面提到的差不多。前面提到，MyISAM表的读锁和写锁是互斥的，读之间是串行的。

那么如果一个进程请求某个MyISAM表既有读锁又有写锁时，MyISAM的默认设置是写优先，可以修改LOW_PRIORITY_UPDATES参数，也可以在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的竞争。

另外，MySQL还提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL暂时将写请求的优先级降低，给读进程一定获得锁的机会。

2. MySQL行锁

MySQL行锁主要存在于InnoDB引擎中。InnoDB主要实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排它锁（X）：允许获得排他锁的事务更新数据，阻塞其他事务取得相同数据集的共享读锁和排他写锁。

InnoDB还存在一种意向锁，分为意向共享锁和意向排他锁，这两种锁都由系统自动添加和自动释放，无需人工干预。

依然使用上面的两张表，把引擎修改为InnoDB。注意：使用悲观锁时，必须关闭MySQL默认开启的自动提交模式。默认情况下，当你执行更新操作后，MySQL会立刻将结果进行提交。

读共享锁：

SET autocommit = 0;
SELECT `money` FROM `order_detail` WHERE `id` = 2 LOCK IN SHARE MODE;
UPDATE `order_detail` SET `money` = 14 WHERE `id` = 2;
SELECT `money` FROM order_detail WHERE `id` = 2;
COMMIT;

写排他锁（独占锁）：

SET autocommit = 0;
SELECT `money` FROM `order_detail` WHERE `id` = 2 FOR UPDATE;
UPDATE `order_detail` SET `money` = 14 WHERE `id` = 2;

如果事务A先获得了某行的写共享锁，事务B可以获得该记录，但不能对数据加写锁（可以加S锁），必须等待事务A提交或回滚后才可以加X锁。

需要注意的是，InnoDB并不总是给数据加行锁，有时候也会加表锁。原因在于，InnoDB行锁是通过给索引上的索引项加锁来实现的。这也意味着只有通过索引条件检索数据，InnoDB才使用行锁，否则将使用表锁。另外要注意，因为MySQL的行锁是针对索引加的锁，所以虽然可能访问的是不同的行记录，但是如果条件中用了相同的索引建，还是会造成锁冲突。比如order_detail表的order_id是加了索引的，下面查询了两条订单明细，会出现锁冲突：

select * from order_detail where order_id = 1 and good_id = 1;
select * from order_detail where order_id = 1 and good_id = 2;

此外，还存在一种“间隙锁”的概念。当我们使用范围查询而非相等条件查询，并请求共享或排他锁时，InnoDB会自动对符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，我们称之为“间隙”（GAP），InnoDB也会对这个间隙加锁，这种机制就是所谓的“间隙锁”。

InnoDB表大部分情况下都应该使用行锁，因为事务和行锁往往就是我们选择InnoDB表的理由。但个别情况下，我们也可以考虑使用表锁：

• InnoDB使用表锁的情况一：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，效率比较低不说，还可能造成其他事务长时间的等待和锁冲突，建议用表锁提高事务执行速度。
• InnoDB使用表锁的情况二：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况可以考虑使用表锁以避免死锁、减少数据库因事务回滚等带来的开销。

InnoDB下使用表锁需要注意以下两点。

• 1. 使用LOCK TABLES虽然也可以加表锁，但由于表锁并不是由InnoDB引擎管理，而是由MySQL Server负责的，因此仅当autocommit = 0、innodb_table_locks = 1(默认设置)时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁。这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则InnoDB无法自动检测并处理死锁。
• 2. 在LOCK TABLES对InnoDB表加锁时要注意将AUTOCOMMIT设为0，否则MySQL不会给表加锁；在事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐式地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表锁，必须要用UNLOCK TABLES释放表锁。

正确写法举例，写表t1并从表t2读：

SET AUTOCOMMIT = 0;
LOCK TABLES t1 WRITE,t2 READ;
-- todo ... [do sth with t1 and t2 here];
COMMIT;
UNLOCK TABLES;