测试mysql的隔离级别_测试mysql事务隔离级别

测试使用的mysql版本5.1，本文主要测试了mysql的4种事务隔离级别。

在执行下面的测试例子时，都需要先执行STEP0

需要注意的时， 在测试mysql事务隔离级别的时候，需要使用innodb等支持事务的存储引擎，myisam不支持事务。一个database中可以有各种存储引擎的表。

STEP 0: 准备工作

DROPDATABASEIF EXISTSmydb;

CREATE DATABASE mydb

DEFAULT CHARACTER SET utf8

DEFAULT COLLATE utf8_general_ci;

USE mydb;

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;

INSERT INTO customers(a,b) VALUES(1,‘hello‘),(2,‘world‘);

=====================================================

READ UNCOMMITTED测试(重现读脏数据)

STEP 1: 执行下面的Query1

START TRANSACTION;

UPDATE  customers SET b=‘henry‘ WHERE a=1;

SELECT SLEEP(15);

ROLLBACK;

-----------------------------------------------------------------------------------------

STEP 2：新开一个查询执行下面的Query2：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT * FROM customers;

Results：

Notice that Query2 will not wait for Query1 to finish, also more importantly Query2 returns dirty data. Remember Query1 rolls back all its changes however Query2 has returned the data anyway, this is because it didn‘t wait for all the other transactions with exclusive locks on this data it just returned what was there at the time.

=====================================================

READ COMMITTED测试1(解决了读脏数据问题)

STEP 1: 执行下面的Query1

START TRANSACTION;

UPDATE  customers SET b=‘henry‘ WHERE a=1;

SELECT SLEEP(15);

ROLLBACK;

-----------------------------------------------------------------------------------------

STEP 2：新开一个查询执行下面的Query2：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM customers;

Results:

Notice how Query2 waited for the first transaction to complete before returning and also how the data returned is the data we started off with as Query1 did a rollback.

=====================================================

READ COMMITTED测试2(重现不可重复读问题)

STEP 1:执行下面的Query1

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

SELECT * FROM customers WHERE a=1;

SELECT SLEEP(15);

SELECT * FROM customers WHERE a=1;

ROLLBACK;

STEP 2:执行Query2：

UPDATE  customers SET b=‘henry1‘ WHERE a=1;

Results:

先执行Query1，立即执行Query2，Query1中第一次select的结果和第二次select的结果不一致。第二次查询的结果是Query2更新后的值。

=====================================================

REPEATABLEREAD 测试1(解决READ COMMITTED重复读取不一致问题)

STEP 1:执行下面的Query1

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE  READ ;

START TRANSACTION;

SELECT * FROM customers WHERE a=1;

SELECT SLEEP(15);

SELECT * FROM customers WHERE a=1;

ROLLBACK;

STEP 2:执行Query2：

UPDATE  customers SET b=‘henry1‘ WHERE a=1;

Results:

先执行Query1，立即执行Query2，Query1中第一次select的结果和第二次select的结果一致。两次查询的结果都是Query2更新前的值。

Notice that Query1 returns the same data for both selects even though you ran a query to modify the data before the second select ran. This is because the Update query was forced to wait for Query1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.

=====================================================

REPEATABLEREAD 测试2(重现REPEATABLEREAD幻读问题)

STEP 1:执行下面的Query1

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE  READ ;

START TRANSACTION;

SELECT * FROM customers ;

SELECT SLEEP(15);

SELECT * FROM customers;

ROLLBACK;

STEP 2:执行Query2：

INSERT INTO customers(a,b) VALUES(3,‘good‘),(4,‘night‘);

预期结果：

Query1中的第一个查询和第二个查询的结果不一样。第二个查询的结果中包含Query2新插入的记录。预期和实际不一致。预期能够在mssql和oracle中达成。

Results:

You will never found phantoms on InnoDB mysql with read commited or more restricted isolation level.

在mysql InnoDB无法重新幻读现象。

=====================================================

SERIALIZABLE  测试(解决REPEATABLEREAD幻读问题)

STEP 1:执行下面的Query1

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE   ;

START TRANSACTION;

SELECT * FROM customers ;

SELECT SLEEP(15);

SELECT * FROM customers;

ROLLBACK;

STEP 2:执行Query2：

INSERT INTO customers(a,b) VALUES(3,‘good‘),(4,‘night‘);

Results:

Query2会阻塞，直到Query1执行完成，才会开始执行。显然，不适用于高并发的请求。

Query2 waits for Query1 to complete before it runs eradicating the chance of a phantom read. If you change the isolation level in Query1 to repeatable read, you’ll see the insert no longer gets blocked and the two select statements in Query1 return a different amount of rows.

参考：

http://gavindraper.com/2012/02/18/sql-server-isolation-levels-by-example/