同乐学堂2.1 锁和事务
MySQL服务器有内部机制来避免用户损坏其他用户插入的数据。尽管通常情况下，这些内部机制默默而有效地运转着，以至于人们没有意识到这些安全机制也会引发你的应用程序或他人应用程序问题。因此，我先简单介绍MySQL服务器使用的并发控制机制。MySQL服务器用锁和事务来处理对其表的并发访问。我将先简单介绍锁的类型和事务处理，然后介绍排错的技术。当线程请求数据集的时候就会加锁。在MySQL中，这可以是表、行、页或者元数据。当线程结束处理特定的数据集之后，它就会释放锁。2.2节和2.1.4节详细介绍MySQL的锁设置。不同的章节分别介绍元数据锁，是因为这是一个新特性，并且根据MySQL服务器处理并发方式的不同而有所不同。如果你熟悉旧的表锁机制但不了解元数据锁，那么下面的章节可以帮你判断是否在特定情况下遇到了元数据锁。数据库事务是处理一致性和可靠性工作的最小单位，这使得用户可以避免与其他事务交互时可能产生的风险。事务的隔离等级控制其他并发操作中的变化对本事务是否可见。2.3小节将详细讨论MySQL的事务机制。
2.2 锁
MySQL服务器和独立的存储引擎都可以设置锁。一般来说，读写操作的锁不同。读锁（或叫共享锁）允许并发线程读取加锁的数据，但禁止写数据。相反，写锁（或叫排他锁）阻止其他线程的读写操作。在存储引擎里，这些锁的实现方式可以不同，不过这些规则的基本原理是稳定的，并且几乎在任何地方都是相同的。当用SELECT语句从表中查询数据或者通过LOCK TABLE ... READ语句显式加锁的时候，数据库将会设置读锁。当修改表或者用LOCK TABLE ...WRITE语句显式加锁的时候，数据库将会设置写锁。 提示InnoDB引擎使用简写的S代表读锁/共享锁，用X代表写锁/排它锁。你会在它的调试数据中看到这些缩写。如前所述，MySQL有4种类型的锁：表锁、行锁、页锁和元数据锁。顾名思义，表锁会锁住整个表，因此没有人可以访问表中任何行，直到持有锁的线程解锁该表。行锁的粒度更细一些，仅会锁住一行或者正在被线程访问的任何几行，因此同一个表中的其他行可以被其他并发线程访问。页锁会锁住一页，不过页锁仅在比较少见的BDB存储引擎中存在，因此我不会对其进行过多介绍。然而，解决锁问题一般推荐的方法对此类型的锁同样适用。元数据锁是MySQL 5.5版本中的新特性。该锁仅对表中的元数据启用，当有线程开始使用表的时候，元数据锁会锁住表的所有元数据。元数据是DDL（数据定义语言或叫数据描述语言）语句的更改信息，如CREATE、DROP和ALTER等修改方案的语句。在老版本的MySQL中引入元数据锁是为了解决线程可以在其他线程中的并发事务使用相同表的情况下修改表定义或是删除表的问题。在下面的章节会介绍表锁、行锁和元数据锁，以及你的应用程序可能由这些锁引发的问题。
2.2.1 表锁
当设置表锁的时候，整个表都被锁住。这意味着并发线程不能使用表，例如，如果设置的是读锁那么写访问是禁止的，如果设置的是写锁，那么访问读和写访问都是禁止的。当访问访问表并且该表所使用的存储引擎支持表锁的时候，即会产生表锁，比如MyISAM引擎。也可以在任何引擎上显式调用LOCK TABLES来产生表锁，在5.5之前的MySQL版本上也可使用DDL操作来产生表锁。我一贯喜欢用示例来说明概念，这里有一个关于表锁的效果的示例：
获取两行记录需要3分钟？当我在讲座上展示这个示例的时候，我停下来提问是否有人知道原因。那时候，上网本刚开始流行，所有的听众都高喊：“它运行在Atom CPU上！”不过这种延迟对于现代处理器来说实在太大了。首先看一下表的定义，然后再次执行同一个请求：
现在它几乎没有耗时！为了查明究竟发生了什么，我们需要在查询运行缓慢的时候执行SHOW PROCESSLIST命令。 提示在实际的应用程序环境中，你要么在忙时手动执行诊断查询，要么采用定时作业来帮助你保存结果。
输出中的字段解释如下。
Id
MySQL服务器中运行的连接线程的ID。
User、Host和db
客户端连接到服务器时使用的连接选项。
Command
线程中当前执行的的命令。
Time
从线程开始执行命令到现在消耗的时间。
State
线程的内部状态。
Info
表明线程当前正在进行的工作。如果展示的是查询语句，表明该语句正在执行；如果值是NULL，表明线程正在休眠，并等待下一条用户命令。
为了查明查询究竟发生了什么，我们需要找到Info输出中包含查询文本的行，然后检查查询的状态。在输出信息的最顶端，可以看到查询的状态是锁定的(Locked)，这意味着该查询无法执行，因为其他线程持有该线程正在等待的锁。下面的语句：
访问相同的表，并且已经执行了36秒。因为是同一个表并且再没有其他线程使用该表，所以我们可以推断该更新阻止该查询开始执行。事实上，该查询需要等待200秒，直到另一个查询执行完成。你刚刚学到一个新的重要调试技巧：当你怀疑是并发线程影响了查询的时候，使用SHOW PROCESSLIST命令查看状态。
2.2.2 行锁
行锁会锁住一些行，而不是整张表。因此，可以修改表中没有被行锁锁住的行。行锁在存储引擎的级别进行设置。InnoDB是当前使用行锁的主要的存储引擎。为了展示行锁和表锁的区别，我们把之前使用过的示例稍作修改：
再次运行同样含有休眠的UPDATE查询，比较一下行锁和表锁的不同效果：
当休眠语句执行的时候，我们有充分的时间去使用其他客户端执行查询操作：
这次立即得到结果。现在尝试更新行：
更新行操作也没有被锁，运行良好。如果我们使用与休眠的UPDATE语句中相同的WHERE条件更新行，会发生什么？
新查询等待innodb_lock_wait_timeout参数里设置的时间（默认值是50秒），然后报错退出。由于数据一致性，我们得到跟表锁一样的结果，不过除非并发线程试图访问正好被锁住的行，否则InnoDB的锁不会对并发线程产生影响。
提示
事实上，行锁比我描述的复杂得多。例如，如果我们使用无法通过唯一键（UNIQUE）解析的WHERE条件访问表，我们就无法并行修改任何行，因为存储引擎无法判断其余线程是否要更新相同的行。这并不是我在讨论两种级别的锁的时候为节省排错技巧的篇幅略过的唯一细节。可以查阅附录以获取更多关于MySQL锁的信息和资料。现在，查看如何更改进程列表的输出信息。在这个例子中，我会使用INFORMATION_ SCHEMA.PROCESSLIST表。实际上，该表包含的信息与SHOW PROCESSLIST命令展示的信息相同，不过由于这里是保存在表中，因此可以根据需要排序查询结果。在你有很多并发线程的时候，这会带来很大的便利：
这里你可以看到另一个与之前示例不同的地方：该查询的状态是Updating，而不是Locked。为了确定在InnoDB中一个请求是否阻塞，可以执行SHOW ENGINE INNODB STATUS命令，该命令是InnoDB监控器机制的一部分。该命令在分析并发多语句事务的作用的时候尤为有用，我们将在本章晚些时候讨论。这里不会展示该工具所有的输出，而仅给出与当前示例有关的部分。2.8.2节和第6章将详细讨论该工具。
我们需要重点关注的部分如下所示。
上述信息表明该查询在等待锁。在回到锁之前，先详细介绍上面的输出信息。
这是事务的ID。
事务有效的秒数。
执行事务的MySQL线程ID。
表明事务正在做什么工作。
表明有多少表被使用和被锁住。
锁的相关信息。
MySQL线程的相关信息，包括：线程ID、查询ID、用户凭证和MySQL状态。
当前执行的查询。下面是关于锁的详细信息。
该信息表明InnoDB表空间中阻塞事务的详细信息，包括锁的类型（排他锁，因为我们要进行更新操作）和物理记录的二进制内容。最后，查看关于执行锁住行的查询的事务的信息：
既然情况明了，就可以考虑如何修复它。你刚刚学到了另一个重要的排错工具：InnoDB监控器，该工具可以通过SHOW ENGINE INNODB STATUS命令调用。2.8.2节会详细介绍该工具。关于1.6节中的性能排错问题，我有一点需要说明。那一节提到索引会降低插入的性能，因为在插入数据的同时需要更新索引文件。不过，当使用行锁的时候，索引会提升整个应用程序的性能，尤其当索引唯一的时候，因为当更新这种类型的索引字段的时候，插入不会阻塞对整个表的访问。下面将暂时从锁的介绍转向事务的介绍。然后将会回到元数据锁。本章如此编排内容，是因为在讨论元数据锁之前我们需要对事务有所了解。
微信扫一扫，打赏作者吧～
