postgresql脏读查询写法 mysql脏读如何解决

转载

IT剑客风云 2024-03-28 22:47:59

文章标签 postgresql脏读查询写法 MySQL InnoDB 表锁行锁 文章分类 云原生云计算

在正式讲解各种锁之前，先来预习事务的四种隔离级别，因为MySQL事务隔离级别不同，所用到的锁也有差别。各种事务隔离级别可以对应解决不同类型的事务并发带来的问题，事务隔离级别越高，可以解决的事务并发问题越多，同时性能事务性能也就越低，所以并非事务隔离级别越高越好。

一、事务并发带来的问题

1、脏读：脏读发生在一个事务A读取了被另一个事务B修改，但是还未提交的数据。假如B回退，则事务A读取的是无效的数据。

postgresql脏读查询写法 mysql脏读如何解决_InnoDB

2、不可重复读：在基于锁的并行控制方法中，如果在执行select时不添加读锁，就会发生不可重复读问题。

postgresql脏读查询写法 mysql脏读如何解决_InnoDB_02

3、幻读：幻读发生在当两个完全相同的查询执行时，第二次查询所返回的结果集跟第一个查询不相同。

postgresql脏读查询写法 mysql脏读如何解决_表锁_03

二、事务四种隔离级别

1、Read Uncommitted（未提交读）：事务未提交对其他事务也是可见的，此时可能发生脏读。

2、Read Committed（提交读）：一个事务开始之后，只能看到自己提交的事务所做的修改，解决了脏读问题，但可能会发生不可重复读问题。

3、Repeatable Read (可重复读) ：在同一个事务中多次读取同样的数据结果是一样的，解决了不可重复读问题，但未能解决幻读的问题。

4、Serializable（串行化）：最高的隔离级别，通过强制事务的串行执行。

三、Innodb引擎行锁、表锁

锁是用于管理不同事务对共享资源的并发访问。那么行锁到底锁住了什么，InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则InnoDB将使用表锁（锁住索引的所有记录）。

常见的MySQL InnoDB锁类型包括：共享锁（S锁、行锁）、排它锁（X锁、行锁）、意向共享锁（IS锁、表锁）、意向排它锁（IX锁、表锁）、记录锁（Record Locks）、间隙锁（Gap Locks）、临键锁（Next-key Locks）。

共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有该获取了排他锁的事务是可以对数据行进行读取和修改。

下面写一个简单的示例，DDL如下：

CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `name` varchar(32) DEFAULT NULL COMMENT '姓名',
 `age` int(8) DEFAULT NULL COMMENT '年龄',
 `lastUpdate` datetime DEFAULT NULL COMMENT '更新时间',
 PRIMARY KEY (`id`),
 UNIQUE KEY `user_name` (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='人员信息';

set session autocommit=OFF
update user set lastUpdate=now() where id=1;

执行上述语句时，Innodb引擎会自动添加排它锁（X锁），同时对该表添加意向排它锁(IX)，告诉其它事务该表上有排它锁，不能再对该表进行表锁操作。

此时执行语句update user set lastUpdate=now() where name='王飞';可以论证上述结论，由于user表没有基于name的索引，所有执行该语句时会对全表进行锁定（表锁），在对表user进行表锁时，会发现该表上有意向排它锁(IX)存在，所以此时进行表锁失败，只能进行事务等待。

临键锁Next-key Lock，当sql执行按照索引进行数据的检索时,查询条件为范围查找（between and、<、>等）并有数据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+区间。为了解决幻读问题，也就是在进行范围查询时，使用临键锁把相邻的下一个区间锁住，进而防止了新插入数据。Next-key Lock = Gap Lock + Record Lock。

间隙锁Gap Locks，当sql执行按照索引进行数据的检索时，范围查询或等值查询的数据不存在，这时SQL语句加上的锁即为Gap locks，锁住索引不存在的区间。

临键锁Next-key Locks，当sql执行按照唯一性（Primary key、Unique key）索引进行数据的检索时，查询条件等值匹配且查询的数据是存在，这时SQL语句加上的锁即为记录锁Record locks。