MySql必须知道的一些知识（二）锁机制与事务问题

最新推荐文章于 2024-06-15 16:09:53 发布

陈大侠在江湖

最新推荐文章于 2024-06-15 16:09:53 发布

阅读量217

点赞数 4

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_39207177/article/details/87800299

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

锁机制

锁机制

概述

数据库的锁机制目的是在并发访问下能够保持数据的一致性。mysql数据库有多种引擎，每个引擎根据使用场景设计了不同的锁类型。锁类型有：表级锁，行级锁，页级锁。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低, 并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

表级锁

使用了表级锁的主要是MyISAM这类非事务性的引擎。因为是对表进行锁定，不会出现死锁现象。表级锁分为两种模式：表共享读锁（Table Read Lock），表独占写锁（Table Writer Lock）。

表共享读锁：对一个表进行读，不会阻塞其它线程对该表的读操作。会阻塞写操作
表独占写锁：对一个表进行写，会阻塞其它线程的读和写操作。
由此我们发现读和读之间并行，读和写、写和写之间是串行。

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 103   |
| Table_locks_waited         | 0     |

Table_locks_immediate：表级锁的次数
Table_locks_waited：表级锁争用而发生等待的次数
操作锁

//给表加读锁/写锁
lock table 表1名 read/write,表2名 read/write...;
// 释放所有表
unlock tables;
//查看哪些表加锁
show open tables where in_use>0;

这里所有的表引擎都是MyISAM，给表加读锁时，只能访问显式加锁的这些表，不能访问未加锁的表。并且不能对被锁表和未被锁表（所有表）进行更新。

mysql> lock table user read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from order1;
ERROR 1100 (HY000): Table 'order1' was not locked with LOCK TABLES

mysql> insert into user(name) values ('Tom');
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be update

给表加写锁时，可以对被锁的表进行更新操作

mysql> lock table user write;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user (name) values ('jack');
Query OK, 1 row affected (0.00 sec)

如何让MyISAM存储器进行并行操作？MyISAM也是可以进行并发插入（ConcurrentInsert）的。

当设置concurrent_insert = 0 时，不允许并发插入
当设置concurrent_insert = 1（默认）时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录，
当设置concurrent_insert = 2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录

mysql> show variables like 'concurrent_insert';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | AUTO  |
+-------------------+-------+

修改concurrent_insert =0

mysql> set global concurrent_insert=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'concurrent_insert';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | NEVER |
+-------------------+-------+

我们对一个user表加上读锁。然后在打开一个mysql客户端进行插入，此时我们发现一直处于插入命令，无法插入。当我们在第一个客户端释放锁后才能插入成功。如果值不是concurrent_insert不为0时，即使第一个客户端对表上了锁，第二个客户端也是能立刻插入数据的。
当我们在lock table 表名 read/write local;也是可以进行并发插入的。

行级锁（重点）

InnoDB引擎支持事务与行级锁

事务（transaction）

事务是一组sql语句组成的逻辑处理单元，事务具有常说的ACID特点；

原子性（A）：指一组sql对数据修改要么全都执行成功，要么就全部撤销（会被回滚（Rollback）到事务开始前的状态）。
一致性（C）：指结束事务后，数据库的完整性没有被破坏。比如一个学校收了一个学生，这个学生属于新开的专业，而这个专业数据库并没有添加。这时会出现无专业学生。
隔离性（I）：指事务之间相互隔离，互不影响。
持久性（D）：更新的操作，数据永久保存到数据库（硬盘）上。

事务并发与隔离性问题

事务并发问题：

更新丢失：
多个事务选择同一数据，然后对其更新，最后提交的事务更新会覆盖其它事务更新。
脏读：当一个事务对数据进行了更新，但是还未提交，而其它事务读取了这些脏数据。
不可重复读：当一个事务读取了某些数据，而其它事务对这些数据发生了更新。这时事务再次读取获取的数据和第一次获取的数据不一致。
幻读：当事务不是独立执行时发生的一种现象，例如一个事务对一个数据集进行了修改。另一个事务也对相同满足条件的该数据集进行了插入或删除，此时在查询会发现有读取的记录不一样。

如何解决事务并发：
事务并发出现了很多问题，并且是相当严重的。事务并发问题是通过事务的隔离性来解决各种问题。
隔离级别：

未提交读（READ_UNCOMMITTED）：最低事务隔离，可以查看其它事务未提交数据，解决更新丢失问题。
已提交读（READ_COMMITTED）：当一个事务进行提交后，另一个事务才可以读取该数据。解决丢失更新和脏读问题。
可重复读（REPEATABLE_READ）：保证一个事务相同条件下前后两次获取的数据是一致的。解决丢失更新、脏读、不可重复读。默认级别
可序列化（SERIALIZABLE）：解决所有的问题。

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读	语句级	否	是	是
可重复读	事务级	否	否	是
序列化	最高级别，事务级	否	否	否

事务操作

查询默认事务隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

修改隔离级别：1.会话级，2.全局级

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)
mysql> set global transaction isolation read uncommitted;
Query OK, 0 rows affected (0.01 sec)

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
我们可以打开两个mysql客户端进行上面事务并发问题的模拟。我偷个懒不写了。

行级锁

前面说了事务，其实也是为了行级锁进行铺垫。其只对当前所操作的行进行加锁，行级锁发生冲突的概率很低，其粒度最小，但是加锁的代价最大，InnoDB实现两种行级锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；且共享锁是读锁，即其它事务可以查看但无法修改和删除的锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

创建共享锁：
select * from 表名 where … lock in share mode;
创建排他锁
select * from 表名 where … for update;

模拟共享锁：
mysql客户端1：

mysql> desc user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id=1 lock in share mode;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+

mysql客户端2：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update user set name='张三2' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

结果：当客户端1执行了共享锁（S）且事务未提交，客户端2的事务无法对该数据集加上排他锁，可以加共享锁。并且无法修改数据，只可以查看。

模拟排他锁：
mysql客户端1：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=1 for update;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+

mysql客户端2：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting trans
action

mysql> select * from user where id=1 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update user set name='张三2' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

结果：客户端1加上排他锁后，客户端2无法加任何锁且无法更新数据；

InnoDB为了允许行锁和表锁共存，内部也实现了两种意向索，意向共享锁（IS）与意向排他锁（IX），这两种锁是表级锁。意向锁是InnoDB自动加的。对于修改语句会加排他锁，查询语句加共享锁
意向共享锁（IS）：如果事务要加S锁，在加共享锁之前必须加IS锁
意向排他锁（IX）：如果事务要加X锁，在加X锁之前必须加IX锁。

不得不提的一点是在oracle中是锁定数据行，而mysql中是通过锁定数据行的索引。也就是说mysql的InnoDB中只有通过索引条件检索数据，InnoDB才会使用行级锁。否则使用表锁。下面证明这一说法。
mysql客户端1:

mysql> desc user2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user2 where id=1 for update;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+

mysql客户端2:

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update user2 set name='tom1' where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

结果：user2表没有任何的索引，客户端1用X锁锁定了id=1的那一行数据，客户端2中的id=2的数据行无法更新，由此可见，user2整个表是被锁定的。

死锁(Deadlock)
相互等待资源造成互相堵塞。因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。
如何预防死锁问题：让不同的session加锁有次序

死锁几种情况以及解决方法请参考这篇博客，也提到了悲观锁和乐观锁。个人觉得写的不错。
https://blog.csdn.net/qq_16681169/article/details/74784193

其它锁：
1. 自增锁（Auto-inc Locks）
听其名知其意，该锁时解决事务之间对自增列的问题。想一下这样一个场景：事务1要插入一行数据，其id自增+1，未提交，其它事务插入一个数据，此时id自增+1，就造成出现相同id情况。这是我们不想见的，则自增锁解决了这个问题，还有一点就是自增锁肯定是表锁。不可能是行锁。
2. 间隙锁（Gap Lock）
网上一般都是这么说的：InnoDB会给符合条件的已有数据的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制不是所谓的间隙锁（Next-Key锁）。
我们理解起来可能有些地方不明白，我来举个例子：
mysql客户端1：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.02 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  4 | 李四   |
|  6 | 王五   |
+----+--------+
5 rows in set (0.00 sec)

mysql> update user set name='李四2' where id>3;
Query OK, 2 rows affected (0.00 sec)

mysql客户端2：

mysql> insert into user (id,name) values (5,'name5');
Query OK, 1 row affected (0.00 sec)

mysql客户端1；

mysql> select * from user;
+----+---------+
| id | name    |
+----+---------+
|  4 | 李四2   |
|  5 | name5   |
|  6 | 李四2   |
+----+---------+
6 rows in set (0.02 sec)

         user表没有id=5的数据，此时如果一个事务在改动3<id数据集的数据后，另一个事务插入了id=5的数据，此时我们发现id=5的数据此时并没有和其它数据集进行一样的更改。因为是先更改后插入的。那这就出现了幻读。
         间隙锁就是为了解决这种问题，防止幻读的发生，间隙锁如何解决呢？它是通过锁定了范围间隙，这里就是把id在4~6之间的范围进行锁定,不让新数据插入。
         间隙锁阻塞了符合条件范围内并发插入，会造成严重的锁等待，所以尽量使用相等条件来访问更新数据，避免使用范围条件。

3. 记录锁（Record Locks）
记录锁是的单个行记录上的锁，会阻塞其他事务对其插入、更新、删除；

4.临键锁（Next-Key Lock）
临建锁是记录锁与间隙锁的组合，即：既包含索引记录，又包含索引区间，主要是为了解决幻读。

陈大侠在江湖

关注

4
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySql必须知道的一些知识（二）锁机制与事务问题

文章目录锁机制概述表级锁行级锁（重点）事务（transaction）锁机制概述数据库的锁机制目的是在并发访问下能够保持数据的一致性。mysql数据库有多种引擎，每个引擎根据使用场景设计了不同的锁类型。锁类型有：表级锁，行级锁，页级锁。表级锁使用了表级锁的主要是MyISAM这类非事务性的引擎。因为是对表进行锁定，不会出现死锁现象。表级锁分为两种模式：表共享读锁（Table Read Lo...
复制链接

扫一扫