MySQL中事务和锁的示例分析

共计 5933 个字符，预计需要花费 15 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 MySQL 中事务和锁的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

MySQL 数据库是一个多用户访问系统，那么就要面临当多个用户同时读取和更新数据时，数据不会被破坏，所以就诞生了锁，锁一种并发控制技术，当一个用户尝试修改数据库中的记录时，首先要获取锁，那么持有这个锁的用户还在修改时，其他用户就不能对这些记录进行修改了。

MySQL 中的锁

但是相对其他数据库而言，MySQL 的锁机制比较简单，MySQL 不同的存储引擎有不同的锁机制，MylSAM 和 MEMORY 存储引擎采用的是表级锁，BDB 存储引擎采用的是页面锁，而常用的 InnoDB 存储引擎支持行级锁、表级锁，默认情况下是采用行级锁。

这 3 种锁的特性如下：

表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低, 并发度也最高。

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

MyISAM

MyISAM 表锁

MySQL 为表提供了两种类型的锁，它们是：

READ LOCK：允许用户仅从表中读取数据。

WRITE LOCK：允许用户对表进行读取和写入操作。

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

MyISAM 在执行查询语句 (SELECT) 前，会自动给使用到的所有表加读锁，在执行更新操作 (UPDATE、DELETE、INSERT 等) 前，会自动给涉及的表加写锁，这个过程并不需要我们手动干预，所以我们一般不需要用 LOCK TABLE 命令给 MyISAM 表显式加锁，但是显示加锁也没有什么问题。

还有在用 LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及表的锁，因为在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表，否则会出错，同时，如果加的是读锁，那么只能执行查询操作，不能执行更新操作，否则也会报错，在自动加锁的情况下也是如此，这也正是 MyISAM 表不会出现死锁的原因。

下面看一个列子。

1、创建一张表

CREATE TABLE test_table ( 
 Id INT NOT NULL AUTO_INCREMENT, 
 Name VARCHAR(50) NOT NULL, 
 Message VARCHAR(80) NOT NULL, 
 PRIMARY KEY (Id) 
);

2、会话 1 获取写锁

mysql  lock table test_table write;
Query OK, 0 rows affected (0.01 sec)

3、会话 2 读取。

我们知道在某个会话持有 WRITE 锁时，所有其他会话都无法访问该表的数据，所以在第二个会话执行下面语句时，会一直处于等待状态。

mysql  select * from test_table;

4、会话 1 解锁

unlock table;

并发插入

在 MyISAM 里读写操作是串行的，但是可以根据 concurrent_insert 的设置，让 MyISAM 支持并行查询和插入。

concurrent_insert 取值如下：

0：不允许并发插入功能。

1：允许对没有空洞的表使用并发插入，新数据位于数据文件结尾（缺省）。

2：不管表有没有空洞，都允许在数据文件结尾并发插入。

空洞指的是表的中间没有被删除的行。

InnoDB

InnoDB 不同于 MyISAM，他有两个特点，一是支持事务，二是采用了行级锁，行级锁和表锁有很多不同的地方。

事务特性

原子性

事务是一个原子操作单元，对数据的修改，要么全部执行，要么全都不执行。

一致性

在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性。

隔离性

数据库系统保证事务在不受外部并发操作影响，可以 独立 环境执行，这意味着事务处理过程中的中间状态对外部是不可见的。

持久性

事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

相对于串行处理来说，虽然提高了资源利用率，可以支持更多的用户，但并发事务处理也会带来些问题，主要包括以下几种情况。

更新丢失

由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，也就是最后的更新覆盖了由其他事务所做的更新。

脏读

脏读又称无效数据的读出，当事务 1 将某一值修改后，然后事务 2 读取该值，后面事务 1 又因为一些原因撤销对该值的修改，这就导致了事务 2 所读取到的数据是无效的。

不可重复读

指的是一个事务在读取某些数据后，再次读取之前读过的数据，却发现读出的数据已经发生了改变。

幻读

当事务 1 按相同的查询条件重新读取以前查询过的数据时，却发现其他事务插入了满足这个条件的新数据。

事务隔离级别

上面说的 更新丢失 是应该完全避免的，但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁。

而脏读、不可重复读、幻读，都是数据库读一致性问题，必须由数据库提供事务隔离机制来解决。数据库实现事务隔离的方式，可分为以下两种，一种是在读取数据前加锁，阻止其他事务对数据进行修改，另一种不需要锁，通过 MVCC 或 MCC 来实现，这种技术叫做数据多版本并发控制，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别的一致性读取。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行。

InnoDB 有四个事务隔离级别：READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ，和 SERIALIZABLE。默认隔离级别是 REPEATABLE READ。

隔离级别脏读不可重复性幻读读未提交√√√读已提交×√√可重复读取××√可序列化（serializable）×××

查询 / 更改隔离级别

显示隔离级别
show global variables like  %isolation% 
select @@transaction_isolation;
设置隔离级别
set global transaction_isolation = read-committed 
set session transaction isolation level read uncommitted;

READ UNCOMMITTED（读未提交）

在这个隔离级别，所有事务都可以看到其他未提交事务的执行结果。这种隔离级别在实际应用中很少使用，读取未提交的数据也称为脏读。

例子

启动两个会话，并设置隔离级别为 READ UNCOMMITTED。

mysql  select * from user;
+-----------+---------+
| user_name | balance |
+-----------+---------+
|  张三  | 100 |
|  李四  | 100 |
|  王五  | 80 |
+-----------+---------+

时间事务 1 事务 2T1begin;begin;T2select * from user where user_name= 张三
此时张三余额 100
T3
select * from user where user_name= 张三
此时张三余额 100T4update user set balance =80 where user_name = 张三
T4
select * from user where user_name= 张三
此时张三余额 80T5commitcommit

可以看到，在 T4 时刻，事务 1 没有提交，但是事务 2 可以看到被事务 1 锁更改的数据。

READ COMMITTED（读已提交）

这是大多数数据库系统的默认隔离级别，但不是 MySQL 的默认级别，他避免了脏读现象，因为在任何未提交的事务前，对任何其他事务都是不可见的，也就是其他事务看不到未提交的数据，允许不可重复读。

例子

将两个会话中隔离级别设置为读已提交
set session transaction isolation level read committed;

时间事务 1 事务 2T1begin;begin;T2select * from user where user_name= 张三
此时张三余额 100
T3
select * from user where user_name= 张三
此时张三余额 100T4update user set balance =80 where user_name = 张三
T4
select * from user where user_name= 张三
此时张三余额 100T5commit
T5
select * from user where user_name= 张三
此时张三余额 80

可以看到，在 T4 时刻，事务 1 没有提交，但是事务 2 读取到的数据还是 100, 当事务 1 提交后，事务 2 才可以看到。

REPEATABLE READ（可重复读）

这是 MySQL 的默认事务隔离级别，它确保同一事务读取数据时，将看到相同的数据行，但是会出现幻读，当事务 1 按条件进行查询后，另一个事务在该范围内插入一个新数据，那么事务 1 再次读取时，就会读到这个新数据。InnoDB 和 Falcon 存储引擎通过 mvcc（多版本并发控制）机制解决了这个问题。

例子

设置两个会话隔离级别为可重复读
set session transaction isolation level repeatable read;

时间事务 1 事务 2T1begin;begin;T2update user set balance =80 where user_name = 张三
T3commit;
T4
select * from user where user_name= 张三
张三余额为 100

可以看到，在 T3 时刻，事务 1 已经提交更改，但是在 T4 时刻的事务 2 中，还是读取到了原来的数据，但是如果事务 2 在原来的基础上再减 10 元，那么最终余额是 90 还是 70 呢？, 答案是 70。.

mysql  update user set balance=balance-10 where user_name= 张三 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql  select * from user where user_name= 张三 
+-----------+---------+
| user_name | balance |
+-----------+---------+
|  张三  | 70 |
+-----------+---------+
1 row in set (0.00 sec)

SERIALIZABLE（序列化）

他是最高的隔离级别，InnoDB 将所有普通 SELECT 语句隐式转换为 SELECT … LOCK IN SHARE MODE，所有事务按照顺序依次执行，因此，脏读、不可重复读、幻读都不会出现。但是，由于事务是串行执行，所以效率会大大下降，

例子

设置隔离级别为序列化
set session transaction isolation level serializable;

时间事务 1 事务 2T1begin;begin;T2select * from user where user_name= 张三
T3
update user set balance =80 where user_name = 张三

这一次，有趣的是，事务 2 在 T3 时刻更新被阻止了, 原因是在 serializable 隔离级别下，MySQL 隐式地将所有普通 SELECT 查询转换为 SELECT FOR SHARE，持有 SELECT FOR SHARE 锁的事务只允许其他事务对 SELECT 行进行处理，而不允许其他事务 UPDATE 或 DELETE 它们。

所以有了这个锁定机制，我们之前看到的不一致数据场景就不再可能了。

但是，这个锁具有超时时间，在等待一会后，如果其他事务在这段时间内没有提交或回滚释放锁，将抛出锁等待超时错误，如下所示：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB 行锁

InnoDB 的行级锁也分为共享锁和排他锁两种。

共享锁允许持有锁的事务读取行。

独占锁允许持有锁事务的更新或删除行。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁，这两种意向锁都是表锁。

意向共享锁 事务想要获得一张表中某几行的共享锁。

意向排他锁 事务想要获得一张表中某几行的排他锁。

InnoDB 行锁是通过锁定索引上的索引条目来实现的，因此，InnoDB 只有在通过索引条件检索到数据时才使用行级锁；否则，InnoDB 将使用表锁。

我们可以显示的加锁，但对于 update、delete、insert 语句，InnoDB 会自动给涉及的数据集加排他锁，对于普通的 select 语句，InnoDB 不会加任何锁，下面是显示的加锁方式：

共享锁：SELECT  FROM table_name WHERE … LOCK IN SHARE MODE

排他锁：SELECT * FROM table_name WHERE … FOR UPDATE

Next-Key 锁

当我们使用范围条件而不是相等条件检索数据，并请求其共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁，对于在条件范围内但并不存在的记录，叫做间隙(GAP), InnoDB 也会对这个 间隙 加锁，这种锁机制就是所谓的 Next-Key 锁。

举例来说，假如 user 表中只有 101 条记录，其 user_id 的值分别是 1.2. ..100. 101，当查找大于 100 的 user_id 时，使用下面 SQL。

select.* from emp where user_id   100 for update;

这就是一个范围条件的查询，InnoDB 不仅会对 user_id 为 101 的记录加锁，也会对 user_id 大于 101 的 间隙 加锁，虽然这些记录并不存在。

InnoDB 使用 Next-Key 锁的目的，一方面是为了防止幻读，另一方面，是为了满足恢复和复制的需要。

以上是“MySQL 中事务和锁的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！