炸裂！MySQL 82 张图带你飞

这篇文章我们来了解一下 MySQL 中的高级内容。

其他文章：

138 张图带你 MySQL 入门

47 张图带你 MySQL 进阶！！！

本文思维导图如下。

事务控制和锁定语句

我们知道，MyISAM 和 MEMORY 存储引擎支持表级锁定(table-level locking)，InnoDB 存储引擎支持行级锁定(row-level locking)，BDB 存储引擎支持页级锁定(page-level locking)。各个锁定级别的特点如下

页级锁：销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

表级锁：表级锁是对整张表进行加锁，MyISAM 和 MEMORY 主要支持表级锁，表级锁加锁快，不会出现死锁，锁的粒度比较粗，并发度最低

行级锁：行级锁可以说是 MySQL 中粒度最细的一种锁了，InnoDB 支持行级锁，行级锁容易发生死锁，并发度比较好，同时锁的开销也比较大。

MySQL 默认情况下支持表级锁定和行级锁定。但是在某些情况下需要手动控制事务以确保整个事务的完整性，下面我们就来探讨一下事务控制。但是在探讨事务控制之前我们先来认识一下两个锁定语句

锁定语句

MySQL 的锁定语句主要有两个 Lock 和 unLock，Lock Tables 可用于锁定当前线程的表，就跟 Java 语法中的 Lock 锁的用法是一样的，如果表锁定，意味着其他线程不能再操作表，直到锁定被释放为止。如下图所示

lock table cxuan005 read;

我们锁定了 cxuan005 的 read 锁，然后这时我们再进行一次查询，看看是否能够执行这条语句

select * from cxuan005 where id = 111;

可以看到，在进行 read 锁定了，我们仍旧能够执行查询语句。

现在我们另外起一个窗口，相当于另起了一个线程来进行查询操作。

select * from cxuan005;

这是第二个窗口执行查询的结果，可以看到，在一个线程执行 read 锁定后，其他线程仍然可以进行表的查询操作。

那么第二个线程能否执行更新操作呢？我们来看一下

update cxuan005 set info='cxuan' where id = 111;

发生了什么？怎么没有提示结果呢？其实这个情况下表示 cxuan005 已经被加上了 read 锁，由于当前线程不是持有锁的线程，所以当前线程无法执行更新。

解锁语句

现在我们把窗口切换成持有 read 锁的线程，来进行 read 锁的解锁

unlock tables;

在解锁完成前，进行更新的线程会一直等待，直到解锁完成后，才会进行更新。我们可以看一下更新线程的结果。

可以看到，线程已经更新完毕，我们看一下更新的结果

select * from cxuan005 where id = 111;

如上图所示，id = 111 的值已经被更新成了 cxuan。

事务控制

事务(Transaction) 是访问和更新数据库的基本执行单元，一个事务中可能会包含多个 SQL 语句，事务中的这些 SQL 语句要么都执行，要么都不执行，而 MySQL 它是一个关系型数据库，它自然也是支持事务的。事务同时也是区分关系型数据库和非关系型数据库的一个重要的方面。

在 MySQL 事务中，主要涉及的语法包含 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等。

自动提交

在 MySQL 中，事务默认是自动提交(Autocommit)的，如下所示

show variables like 'autocommit';

在自动提交的模式下，每个 SQL 语句都会当作一个事务执行提交操作，例如我们上面使用的更新语句

update cxuan005 set info='cxuan' where id = 111;

如果想要关闭数据库的自动提交应该怎么做呢？

其实，MySQL 是可以关闭自动提交的，你可以执行

set autocommit = 0;

然后我们再看一下自动提交是否关闭了，再次执行一下 show variables like 'autocommit' 语句

可以看到，自动提交已经关闭了，再次执行

set autocommit = 1;

会再次开启自动提交。

这里注意一下特殊操作。

在 MySQL 中，存在一些特殊的命令，如果在事务中执行了这些命令，会马上强制执行 commit 提交事务；比如 DDL 语句(create table/drop table/alter/table)、lock tables 语句等等。

不过，常用的 select、insert、update 和 delete命令，都不会强制提交事务。

手动提交

如果需要手动 commit 和 rollback 的话，就需要明确的事务控制语句了。

典型的 MySQL 事务操作如下

start transaction;
... # 一条或者多条语句
commit;

上面代码中的 start transaction 就是事务的开始语句，编写 SQL 后会调用 commit 提交事务，然后将事务统一执行，如果 SQL 语句出现错误会自动调用 Rollback 进行回滚。

下面我们就通过示例来演示一下 MySQL 的事务，同样的，我们需要启动两个窗口来演示，为了便于区分，我们使用 mysql01 和 mysql02 来命名。

我们用 start transaction 命令启动一个事务，然后再 cxuan005 表中插入一条数据，此时 mysql02 不做任何操作。涉及的 SQL 语句如下。

start transaction;

然后执行

select * from cxuan005;

查询一下 cxuan005 中的数据

嗯。。。很多长度太长了，现在我们把所有的 info 数据都更新为 cxuan 。

update cxuan005 set info='cxuan';

更新完毕后，我们先不提交事务，分别在 mysql01 和 mysql02 中进行查询，发现只有 mysql01 窗口中的查询已经生效，而 mysql02 中还是更新前的数据