数据库隔离级别设置,数据库的隔离级别

admin

作者

什么是事务？

事务是作为单个逻辑单元所执行的一系列操作，它是一个最小的不可再分的工作单元。比如新建一个数据库或表格，对表数据的插入、更新、删除，执行一个存储过程或函数等，这些都是一个事务。

事务的特性

事务有 ACID 四个基本要素。

A：Atomicity 原子性，事务是单独逻辑单元，是最小单元，是一个整体，不可再分。一个逻辑单元要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

C：consistency一致性，在事务开始之前和事务结束以后，数据库的完整性没有被破坏。一个事务改变了表中的数据，另一个事务查询出来的数据应该要是一致的

I： Isolation隔离性，数据库允许多个并发事务同时对其数据进行读写和修改的能力，但是多个事务之间是互相隔离，互不干扰的。

D：Durability持久性，事务一旦成功提交，数据就不会因为其数据库隔离级别设置他原因或者无缘无故的改变，即便系统故障也不会丢失。

每一个事务都应该同时满足以上四个特性。

事务控制

事务的控制有两种 *** ：

隐式事务：

在 MySQL 命令行的默认设置下，即执行 SQL 语句后就会马上执行 COMMIT 操作。事务都是自动提交的。

显式事务：

显式事务就不是由系统自动提交的，是需要用户手动提交的。

显示提交主要有两种 *** ：

用 BEGIN, ROLLBACK, COMMIT来实现：

begin; //开始事务//sql语句rollback; //事务回滚commit; //事务提交

直接用 SET 来改变 MySQL 的自动提交模式：

#开启自动提交set autocommit = on; #禁止自动提交set autocommit = off;

下面通过示例来直观感受

先打开两个会话

查看事务状态

show variables like 'autocommit';

默认是打开的。

查看student表

两个会话查到的都是一样的数据。现在事务自动提交是开启了的，会话一用begin开启一个事务，对一个数据进行修改

begin;update student set name="jack1" where id = 5;

注意，现在还没有提交。然后在会话二查表

发现数据并没有发生变换。然后两个会话都进行commit提交，然后会话二查表

现在数据发生了变化。

commit执行后才完成了一个事务。

如果在事务中执行了错误的操作，最后可以使用rollback回滚（提交后再回滚是不起作用的）

事务的隔离

事务的隔离主要用于解决,多个用户进行数据写入操作导致的问题

在数据库的使用过程中，无法避免的会有多个用户同时使用一个数据表的情况，所以就有了事务并发控制的概念，也就是每个用户的事务要隔离开来。

事务隔离级别（从低到高）

读未提交：一个用户执行一个事务但没提交，另一个用户可以读到没提交的数据。读未提交级别会导致“脏读”问题。

读提交（大多数数据库默认）：一个用户执行一个事务，只有提交了，另一个用户才可以读到提交后的数据。解决了“脏读”问题，但是会产生一个新的问题：“不可重读”问题。

可重读（ MySQL 默认）：提交一次数据后,其他用户也需要提交才能看到数据,它解决了不可重读的问题，但是会出现新的问题：“幻读”问题。

串行化读：不允许并发，也就是不允许多个用户同时操作一个表。最先使用事务修改表1的用户A就会把表1给占用了，其他用户只能等用户A提交事务完成后才能执行对表1的操作。串行化读虽然解决了许多存在的问题，但是这样做的坏处也很明显，也就是不能多用户同时操作，牺牲了用户的时间来保证数据的准确性和一致性。

当多个用户同时对一张表进行操作时根据事务的隔离级别可能会出现以下情况：

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

不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。（强调更新操作）

幻读：强调两次读取时发生读取结果不一致的情况（仅仅发生在当前读）

查看当前隔离级别

//5.7 版本: select @@session.tx_isolation;//8.0以上版本 select @@transaction_isolation;

mysql默认是可重读。

设置当前隔离级别//设置当前会话隔离级别为-读未提交。 set session transaction isolation level read uncommitted;//设置当前会话隔离级别为-读提交。 set session transaction isolation level read committed;//设置当前会话隔离级别为-可重读。set session transaction isolation level repeatable read;//设置当前会话隔离级别为-串行化读。 set session transaction isolation level serializable;

下面数据库隔离级别设置我们来看看脏读，不可重读，幻读究竟是怎么回事吧。首先我们需要关闭事务自动提交（两个会话都要关闭），并将隔离级别设置为读未提交（两个都要设置）然后我们在会话一修改一个数据（此时未提交），但是另一会话却读到了未提交的数据，这就是脏读。

set autocommit = off; set session transaction isolation level read uncommitted;

然后看看不可重读设置隔离级别为读提交（都要设置）然后我们在会话二修改一个数据（此时未提交）

set session transaction isolation level read committed;update student set age=18 where id=5;

此时未提交，然后会话一查表，读到数据并未修改，这就解决了脏读问题

然后会话二commit提交，会话一再查表，此时数据修改了，这就是不可重读，也就是说，明明会话一什么都没做，但是两次查表发现数据不一样了，这就没法重复读取了。

最后再来看看幻读问题设置隔离级别为可重复读（都要设置）然后我们在会话一修改一个数据，并提交。

set session transaction isolation level repeatable read;update student set age=25 where id=5;commit;

此时会话二查表，并没有读到修改后的数据，这就解决了不可重读问题。

那么如果查到提交后的数据呢，此时就需要会话二进行commit，提交后数据就会改变了。

那么什么是幻读呢？

在解释幻读之前，我们先来说一下锁的概念

什么是锁？

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性; 锁冲突也是影响数据库并发访问性能的一个重要因素

锁的类型

MySQL中不同的存储引擎所使用的锁是不同的：

表级锁：

多个用户不能同时使用一张表。一个用户在访问一个表时，对整张表进行加锁，另外一个用户就访问不了这个表了。 MyISAM 和 MEMORY 存储引擎采用表级锁。

页面锁：

将表分成许多页，只对某一页加锁。页面锁可以精确到表的一部分，只锁表里的一部分数据。 BDB 存储引擎采用页面锁。

行级锁：

对表中的某一行数据加锁，只锁定一行数据， innodb 存储引擎采用表级锁和行级锁，默认是行级锁。

MyISAM 表级锁

表级锁中的读锁和行锁：

read读锁（共享锁）：当MySQL的一个进程为某一表开启读锁之后，其他的进程包含自身都没有权利去修改这张表的内容。但是所有的进程还是可以读出表里面的内容的

write写锁（排他锁）：当某一个进程在对某一张表实施写锁后，在该进程如果完成了更新（insert、update、 delete）之后，如果不释放写锁，其他的进程连查看这张表的权限都没有，只有等它释放写锁值，其他的进程才可以完成相应的操作。如果该进程没有对该表进行更新操作，其他的进程只能做查询操作，但是无法实现更新操作。

用户读数据时自动加read锁，修改数据自动加write锁

显示加锁

Lock tables table_name read; //读锁Lock tables table_name write; //读锁unlock tables; //接触当前锁的限制Innodb 行级锁

通过查询条件对某一行进行加锁commit后解锁

//读锁（共享锁）SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;//写锁（排他锁）SELECT * FROM table_name WHERE ... FOR UPDATE;

当我们进行增删改的时候Innodb是默认给对应行自动加上了写锁的，select查询不会加锁。

在行锁中，写锁和读锁都是其他会话只能查看无法修改该行数据，自己可以修改，但是我们给某一行加了读锁后，其他会话可以给这一行加读锁，不能加写锁。当我们给某一行加了写锁后，其他会话不能给该行加锁

我们现在来看看什么是幻读

首先我想先说明是，mysql里面是分为快照读和当前读，当我们执行普通的查询语句时，mysql进行的是快照读，是不会看到别的事务插入的数据的。而当我们涉及到加锁的操作时，使用的就是当前读，而当前读的规则，就是要能读到所有已经提交的记录的最新值。而上文说过了，增删改是默认会加锁的，也就是增删改也是当前读。

首先会话一查询年龄为18的数据，只有三行记录

select * from student where age=18;