首页 热点资讯 义务教育 高等教育 出国留学 考研考公
您的当前位置:首页正文

事务的隔离级别及mysql对应操作

2023-11-08 来源:花图问答

本次高并发解决之道1,更改事务隔离级别为 read uncommitted读未提交2,查询前设定延迟,延迟时间为随机 50-500 微秒3,修改数据前将 超范围作为 限定修改条件事务是作为单个逻辑工作单元执行的一系列操作.可以是一条SQL语句也可以是多条SQL语句.一个支持事务 Transaction的数据库系统,必需要具有这四种特性,以保证保证数据的正确性事务的隔离级别隔离级别与并发性是互为矛盾的:隔离程度越高,数据库的并发性越差;隔离程度越低,数据库的并发性越好。隔离级别 脏读 不可重复读 幻读读未提交(Read uncommitted) 可能 可能 可能读已提交(Read committed) 不可能 可能 可能可重复读(Repeatable read) 不可能 不可能 可能可串行化(Serializable ) 不可能 不可能 不可能脏读(Dirty Read)当事务读取还未被提交的数据时,就会发生这种事件。举例来说:Transaction1修改了一行数据,然后Transaction2在 Transaction1还未提交修改操作之前读取了被修改过的数据。如果Transaction1回滚了修改操作,那么Transaction2读取的数据就可以看作是从未存在过的。不可重复读(NonRepeatable Read)一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改数据后提交而引起的。比如事务Transactio1读取某一数据,事务Transaction2读取并修改了该数据后提交,Transaction1为了对读取值进行检验而再次读取该数据,便得到了不同的结果。幻读(Phantom Read)如果符合搜索条件的一行数据在后面的读取操作中出现,但该行数据却不属于最初的数据,就会发生这种事件。举例来说Transactio1读取满足某种搜索条件的一些行,然后Transaction2插入了符合Transaction1的搜索条件的一个新行。如果Transaction1重新执行相同查询及条件,就会得到不同的行Read Uncommitted (Oracle不支持)最低等级的事务隔离,仅仅保证了读取过程中不会读取到非法数据。上诉4种不确定情况均有可能发生。与READ COMMITTED 隔离级相反它允许读取已经被其它用户修改但尚未提交确定的数据限制级别最小,脏读在这个隔离级别下是非Consistent Reads的Read Committed (SQL Server, Oracle默认)大多数主流数据库的默认事务等级,保证了一个事务不会读到另一个并行事务已修改但未提交的数据,避免了"脏读取",该级别适用于大多数系统.Repeatable Read (MySQL默认,Oracle不支持)保证了一个事务不会修改已经由另一个事务读取但未提交(回滚)的数据。避免了“脏读取”和“不可重复读取”的情况,但是带来了更多的性能损失。Serializable (Oracle中Read only和这个很相似,唯一区别是Read only事务不允许修改,因此不会遇到ORA-08177错误)最高等级的事务隔离,上面3种不确定情况都将被规避。这个级别将模拟事务的串行执行。-- updated 2009-09-15 from《Expert Oracle Database Architecture》P239这是最大的限制,和X锁类似,不允许其他事务进行任何写访问。如非必要,不要使用这个选项。在事务的开始使用这个命令即可,该隔离级别一直对该SQL Server连接(不是本事务)有效,直到下一次使用本命令设置了新的隔离级别为止。*/-- 以下4种隔离级别都会以创建事务的测试步骤来进行测试-- 开始事务:start transaction-- 设置会话事务级别:set session transaction isolation level ( read uncommitted / read committed / repeatable read / serializable )-- 提交事务:commit-- 回滚事务:rollbackSET TRANSACTION ISOLATION LEVEL -- 只能在开启事务前执行SET SESSION TRANSACTION ISOLATION LEVEL -- 可以在开启事务后执行-- 脏读(Dirty Read)-- 用户1操作START TRANSACTION;SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;UPDATE t1 SET test_str = ‘测试内容2‘ WHERE id = 1;-- 用户2操作START TRANSACTION;SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT * FROM t1 WHERE id = 1;-- 用户1操作ROLLBACK;-- 用户2操作SELECT * FROM t1 WHERE id = 1;ROLLBACK;-- 不可重复读(NonRepeatable Read)-- 用户1操作START TRANSACTION;SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT * FROM t1 WHERE id = 1;-- 用户2操作START TRANSACTION;SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;UPDATE t1 SET test_str = ‘修改后的测试内容‘ WHERE id = 1;COMMIT;-- 用户1操作SELECT * FROM t1 WHERE id = 1;ROLLBACK;-- 幻读(Phantom Read)-- 用户1操作START TRANSACTION;SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT * FROM t1;-- 用户2操作START TRANSACTION;SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;INSERT INTO `test`.`t1` (`storage_num`, `test_str`) VALUES (‘0‘, ‘测试内容1‘);-- 用户1操作SELECT * FROM t1;ROLLBACK;-- 用户2操作ROLLBACK;

 

事务的隔离级别及mysql对应操作

标签:

小编还为您整理了以下内容,可能对您也有帮助:

数据库事务隔离级别有哪些

数据库事务隔离级别有哪些如下:

事务的隔离级别有4种,由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。事物的并发操作中可能休闲脏读,不可重复读,幻读。

1、第一种隔离级别:Read uncommitted(读未提交)

如果一个事务已经开始写数据,则另外一个事务不允许同时进行写操作,但允许其他事务读此行数据,该隔离级别可以通过“排他写锁”,但是不排斥读线程实现。这样就避免了更新丢失,却可能出现脏读,也就是说事务B读取到了事务A未提交的数据。

解决了更新丢失,但还是可能会出现脏读。

2、第二种隔离级别:Read committed(读提交)

如果是一个读事务(线程),则允许其他事务读写,如果是写事务将会禁止其他事务访问该行数据,该隔离级别避免了脏读,但是可能出现不可重复读。事务A事先读取了数据,事务B紧接着更新了数据,并提交了事务,而事务A再次读取该数据时,数据已经发生了改变。

解决了更新丢失和脏读问题。

3、第三种隔离级别:Repeatable read(可重复读取)

可重复读取是指在一个事务内,多次读同一个数据,在这个事务还没结束时,其他事务不能访问该数据,这样就可以在同一个事务内两次读到的数据是一样的,因此称为是可重复读隔离级别,读取数据的事务将会禁止写事务,写事务则禁止任何其他事务,这样避免了不可重复读和脏读,但是有时可能会出现幻读。

读取数据的事务可以通过“共享读镜”和“排他写锁”实现。

解决了更新丢失、脏读、不可重复读、但是还会出现幻读。

4、第四种隔离级别:Serializable(可序化)

提供严格的事务隔离,它要求事务序列化执行,事务只能一个接着一个地执行,但不能并发执行,如果仅仅通过“行级锁”是无法实现序列化的,必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。

mysql事务隔离级别

mysql事务隔离级别如下:

1.读取未提交(READ-UNCOMMITTED):最低的隔离级别,允许读取尚未提交的数据变更,可能造成脏读、不可重复读、幻读。

2.读取已提交(READ-COMMITTED):允许读取并发事务已经提交的数据,可以避免脏读,但是可能造成不可重复、幻读。

3.可重复读(REPEATABLE-READ):对同一字段多次读取的结果都是一致的,除非本身事务修改,可以避免脏读和不可重复读,但是可能造成幻读。

4.可串行化(SERIALIZABLE):最高的隔离级别,完全服从ACID的隔离级别,所以的事务依次执行,可以避免脏读、不可重复读、幻读。

事务的特性:

1.原子性:事务最小的执行单位,不允许分割。事务的原子性确保动作要么全部执行,要么全部不执行。

2.一致性:执行事务的前后,数据保持一致。例如转账的业务中,无论事务是否成功,转账者和收款人的总额应该是不变的。

3.隔离性:并发访问数据库时,一个用户的事务不应该被其他事务所影响,各并发事务之间数据库是的。

4.持久性:一个事务被提交后,它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有影响。

MySQL的默认事务隔离级别是(mysql的隔离级别)

mysql的4种事务隔离级别,如下所示:

1、未提交读(ReadUncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据。

2、提交读(ReadCommitted):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别(不重复读)。

3、可重复读(RepeatedRead):可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读,但是innoDB解决了幻读。

4、串行读():完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞。

相关简介

MySQL是一个关系型数据库管理系统,由瑞典MySQLAB公司开发,属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一,在WEB应用方面,MySQL是最好的RDBMS(RelationalDatabaseManagementSystem,关系数据库管理系统)应用软件之一。

MySQL是一种关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。

MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL软件采用了双授权,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择MySQL作为网站数据库。

MySQLInnoDB四个事务级别与脏读、不重复读、幻读是什么

1、MySQL InnoDB事务隔离级别脏读、可重复读、幻读MySQL InnoDB事务的隔离级别有四级,默认是“可重复读”(REPEATABLE READ)。

· 1).未提交读(READUNCOMMITTED)。另一个事务修改了数据,但尚未提交,而本事务中的SELECT会读到这些未被提交的数据(脏读)( 隔离级别最低,并发性能高 )。

· 2).提交读(READCOMMITTED)。本事务读取到的是最新的数据(其他事务提交后的)。问题是,在同一个事务里,前后两次相同的SELECT会读到不同的结果(不重复读)。会出现不可重复读、幻读问题(锁定正在读取的行)

· 3).可重复读(REPEATABLEREAD)。在同一个事务里,SELECT的结果是事务开始时时间点的状态,因此,同样的SELECT操作读到的结果会是一致的。但是,会有幻读现象(稍后解释)。会出幻读(锁定所读取的所有行)。

· 4).串行化(SERIALIZABLE)。读操作会隐式获取共享锁,可以保证不同事务间的互斥(锁表)。

四个级别逐渐增强,每个级别解决一个问题。

· 1).脏读。另一个事务修改了数据,但尚未提交,而本事务中的SELECT会读到这些未被提交的数据。

· 2).不重复读。解决了脏读后,会遇到,同一个事务执行过程中,另外一个事务提交了新数据,因此本事务先后两次读到的数据结果会不一致。

· 3).幻读。解决了不重复读,保证了同一个事务里,查询的结果都是事务开始时的状态(一致性)。但是,如果另一个事务同时提交了新数据,本事务再更新时,就会“惊奇的”发现了这些新数据,貌似之前读到的数据是“鬼影”一样的幻觉。

具体地:

1). 脏读

首先区分脏页和脏数据

脏页是内存的缓冲池中已经修改的page,未及时flush到硬盘,但已经写到redo log中。读取和修改缓冲池的page很正常,可以提高效率,flush即可同步。脏数据是指事务对缓冲池中的行记录record进行了修改,但是还没提交!!!,如果这时读取缓冲池中未提交的行数据就叫脏读,违反了事务的隔离性。脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。

2). 不可重复读

是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,第二个事务已经提交。那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。例如,一个编辑人员两次读取同一文档,但在两次读取之间,作者重写了该文档。当编辑人员第二次读取文档时,文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档,则可以避免该问题

3). 幻读 :

是指当事务不是执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。例如,一个编辑人员更改作者提交的文档,但当生产部门将其更改内容合并到该文档的主复本时,发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前,任何人都不能将新材料添加到文档中,则可以避免该问题。

2、隔离级别实验 以下实验基于博主MySQL Server 5.6

首先创建一个表,如下:

USE test;

CREATE TABLE `t` (

`a` int(11) NOT NULL PRIMARY KEY

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.1、实验一:解释脏读、可重复读问题

事务A READ-UNCOMMITTED

事务B READ-COMMITTED,

事务C-1 REPEATABLE-READ

事务C-2 REPEATABLE-READ

事务D SERIALIZABLE

set autocommit =0;

start transaction ;

start transaction;

insert into t(a)values(4);

select * from t;

1,2,3,4(脏读:读取到了未提交的事务中的数据)

select * from t;

1,2,3(解决脏读)

select * from t;

1,2,3

select * from t;

1,2,3

select * from t;

1,2,3

commit;

select * from t:

1,2,3,4

select * from t:

1,2,3,4

select * from t:

1,2,3,4 (与上面的不在一个事务中,所以读到为事务提交后最新的,所以可读到4)

select * from t:

1,2,3(重复读:由于与上面的在一个事务中,所以只读到事务开始事务的数据,也就是重复读)

select * from t:

1,2,3,4

commit(提交事务,下面的就是一个新的事务,所以可以读到事务提交以后的最新数据)

select * from t:

1,2,3,4

READ-UNCOMMITTED 会产生脏读,基本很少适用于实际场景,所以基本不使用。

2.2、实验二:测试READ-COMMITTED与REPEATABLE-READ事务A

事务B READ-COMMITTED

事务C REPEATABLE-READ

set autocommit =0;

start transaction ;

start transaction;

start transaction;

insert into t(a)values(4);

select * from t;

1,2,3

select * from t;

1,2,3

commit;

select * from t:

1,2,3,4

select * from t:

1,2,3(重复读:由于与上面的在一个事务中,所以只读到事务开始事务的数据,也就是重复读)

commit(提交事务,下面的就是一个新的事务,所以可以读到事务提交以后的最新数据)

select * from t:

1,2,3,4

REPEATABLE-READ可以确保一个事务中读取的数据是可重复的,也就是相同的读取(第一次读取以后,即使其他事务已经提交新的数据,同一个事务中再次select也并不会被读取)。

READ-COMMITTED只是确保读取最新事务已经提交的数据。

当然数据的可见性都是对不同事务来说的,同一个事务,都是可以读到此事务中最新数据的。如下,

start transaction;

insert into t(a)values(4);

select *from t;

1,2,3,4;

insert into t(a)values(5);

select *from t;

1,2,3,4,5;

2.3、实验三:测试SERIALIZABLE事务对其他的影响

事务A SERIALIZABLE事务B READ-UNCOMMITTED

事务C READ-COMMITTED,

事务D REPEATABLE-READ

事务E SERIALIZABLE

set autocommit =0;

start transaction ;

start transaction;

select a from t union all select sleep(1000) from al;

insert into t(a)values(5);

insert into t(a)values(5);

insert into t(a)values(5);

insert into t(a)values(5);

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

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

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

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

SERIALIZABLE 串行化执行,导致所有其他事务不得不等待事务A结束才行可以执行,这里特意使用了sleep函数,直接导致事务B,C,D,E等待事务A持有释放的锁。由于我sleep了1000秒,而innodb_lock_wait_timeout为120s。所以120s到了就报错HY000错误。

SERIALIZABLE是相当严格的串行化执行模式,不管是读还是写,都会影响其他读取相同的表的事务。是严格的表级读写排他锁。也就失去了innodb引擎的优点。实际应用很少。

2.4、实验四:幻读一些文章写到InnoDB的可重复读避免了“幻读”(phantom read),这个说法并不准确。做个实验:(以下所有试验要注意存储引擎和隔离级别)

CREATE TABLE `t_bitfly` (

`id` bigint(20) NOT NULL default '0',

`value` varchar(32) default NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select @@global.tx_isolation, @@tx_isolation;

+-----------------------+-----------------+

| @@global.tx_isolation | @@tx_isolation |

+-----------------------+-----------------+

| REPEATABLE-READ | REPEATABLE-READ |

+-----------------------+-----------------+

实验4-1:

Session ASession B

start transaction ;start transaction ;SELECT * FROM t_bitfly;

empty set

INSERT INTO t_bitfly VALUES (1, 'a');COMMIT;SELECT * FROM t_bitfly;

| empty set

INSERT INTO t_bitfly VALUES (1, 'a');

|ERROR 1062 (23000):

|Duplicate entry '1' for key 1

(刚刚明明告诉我没有这条记录的)I如此就出现了幻读,以为表里没有数据,其实数据已经存在了,提交后,才发现数据冲突了。

实验4-2:

Session A

Session B

start transaction ;

start transaction ;

SELECT * FROM t_bitfly;

| +------+-------+

| | id | value |

| +------+-------+

| | 1 |a |

| +------+-------+

INSERT INTO t_bitfly VALUES (2, 'b');

SELECT * FROM t_bitfly;

| +------+-------+

| | id | value |

| +------+-------+

| | 1 |a |

| +------+-------+

COMMIT;

SELECT * FROM t_bitfly;

| +------+-------+

| | id | value |

| +------+-------+

| | 1 |a |

| +------+-------+

UPDATE t_bitfly SET value='z';

| Rows matched: 2 Changed:2 Warnings: 0

(怎么多出来一行)

SELECT * FROM t_bitfly;

| +------+-------+

| | id | value |

| +------+-------+

| | 1 |z |

| | 2 |z |

| +------+-------+

本事务中第一次读取出一行,做了一次更新后,另一个事务里提交的数据就出现了。也可以看做是一种幻读。

附说明

那么,InnoDB指出的可以避免幻读是怎么回事呢?

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html

By default, InnoDB operatesin REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 13.6.8.5,

“Avoidingthe Phantom Problem Using Next-Key Locking”).

准备的理解是,当隔离级别是可重复读,且禁用innodb_locks_unsafe_for_binlog的情况下,在搜索和扫描index的时候使用的next-keylocks可以避免幻读。

关键点在于,是InnoDB默认对一个普通的查询也会加next-key locks,还是说需要应用自己来加锁呢?如果单看这一句,可能会以为InnoDB对普通的查询也加了锁,如果是,那和序列化(SERIALIZABLE)的区别又在哪里呢?

MySQL manual里还有一段:

13.2.8.5. Avoiding the PhantomProblem Using Next-Key Locking (http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html)

Toprevent phantoms, InnoDB usesan algorithm called next-key

locking that combinesindex-row locking with gap locking.

Youcan use next-key locking to implement a uniqueness check in your application:If you read your data in share mode and do not see a plicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the

success or of your row ring the read prevents anyone mean while inserting a plicate for your row. Thus, the next-key locking enables you to “lock” the nonexistence of something in your table.

我的理解是说,InnoDB提供了next-key locks,但需要应用程序自己去加锁。manual里提供一个例子:

SELECT * FROM child WHERE id> 100 FOR UPDATE;

这样,InnoDB会给id大于100的行(假如child表里有一行id为102),以及100-102,102+的gap都加上锁。

可以使用show engine innodb status来查看是否给表加上了锁。

再看一个实验,要注意,表t_bitfly里的id为主键字段。

实验4-3:

Session A

Session B

start transaction ;

start transaction ;

SELECT * FROM t_bitfly

WHERE id<=1

FOR UPDATE;

| +------+-------+

| | id | value |

| +------+-------+

| | 1 |a |

| +------+-------+

INSERT INTO t_bitfly VALUES (2, 'b');

| Query OK, 1 row affected

SELECT * FROM t_bitfly;

| +------+-------+

| | id | value |

| +------+-------+

| | 1 |a |

| +------+-------+

INSERT INTO t_bitfly VALUES (0, '0');

| (waiting for lock ...

| then timeout) ERROR 1205 (HY000):Lock wait timeout exceeded;

|try restarting transaction

SELECT * FROM t_bitfly;

| +------+-------+

| | id | value |

| +------+-------+

| | 1 |a |

| +------+-------+

COMMIT;

SELECT * FROM t_bitfly;

| +------+-------+

| | id | value |

| +------+-------+

| | 1 |a |

| +------+-------+

可以看到,用id<=1加的锁,只锁住了id<=1的范围,可以成功添加id为2的记录,添加id为0的记录时就会等待锁的释放。

附说明:

MySQL manual里对可重复读里的锁的详细解释:

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read

For locking reads (SELECT with FOR

UPDATE or LOCK IN SHARE MODE),UPDATE,

and DELETE statements, locking depends on whether the statement uses

a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks

only the index record found, not the gap before it. For other search conditions, InnoDB locks

the index range scanned, using gap locks or next-key (gap plus index-record)locks to block insertions by other sessions into the gaps covered by the range.

一致性读和提交读,先看实验,

实验4-4:

Session A

Session B

start transaction ;

start transaction ;

SELECT * FROM t_bitfly;

| +------+-------+

| | id | value |

| +------+-------+

| | 1 |a |

| +------+-------+

INSERT INTO t_bitfly VALUES (2, 'b');

COMMIT;

SELECT * FROM t_bitfly;

| +------+-------+

| | id | value |

| +------+-------+

| | 1 |a |

| +------+-------+

SELECT * FROM t_bitfly LOCK IN SHARE MODE;

| +----+-------+

| | id | value |

| +----+-------+

| | 1 |a |

| | 2 |b |

| +----+----

显示全文