如何进行MySQL 5.5 隔离级别的测验

发布时间：2023-07-28 13:24:14 所属栏目：MySql教程来源：

导读：这篇文章将为大家详细讲解有关如何进行MySQL 5.5 隔离级别的测试，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。REPEATABLE READ这是InnoDB默认的隔离级别。对于

这篇文章将为大家详细讲解有关如何进行MySQL 5.5 隔离级别的测试，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

REPEATABLE READ

这是InnoDB默认的隔离级别。对于一致性读，REPEATABLE READ和READ COMMITTED有一个重要的区别：在同一个事务中，所有的一致性读会从这个事务第一次读取的快照中读取数据。如果在相同事务中执行多条相同的SELECT语句，这些SELECT查询语句的返回结果是相同的。对于锁定读（例如SELECT .. FOR UPDATE 或 LOCK IN SHARE MODE），UPDATE, DELETE语句，锁依赖语句是否使用unique索引扫描或范围扫描。对于unique索引扫描，InnoDB只会锁定找到的索引记录。对于其他的搜索条件，InnoDB会锁定范围索引扫描到的记录。

会话向表插入数据并提交，会话只有在执行commit或rollback操作后，才可以查询到会话的插入数据，否则看到的是登录时最初查询到的快照中的数据。

mysql> show variables like '%iso%';

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

| Variable_name | Value |

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

| tx_isolation | REPEATABLE-READ |

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

1 row in set (0.09 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into dept2 values(10,'Research');

Query OK, 1 row affected (0.01 sec)

mysql> commit;

Query OK, 0 rows affected (0.12 sec)

mysql> select * from dept2;

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

| deptno | dname |

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

| 10 | Research |

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

1 row in set (0.00 sec)

会话

mysql> show variables like '%iso%';

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

| Variable_name | Value |

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

| tx_isolation | REPEATABLE-READ |

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

1 row in set (0.01 sec)

mysql> select * from dept2;

Empty set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.16 sec)

mysql> select * from dept2;

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

| deptno | dname |

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

| 10 | Research |

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

1 row in set (0.00 sec)

会话

mysql> start transaction

-> ;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into dept2 values(20,'Maintenance');

Query OK, 1 row affected (0.04 sec)

mysql> commit;

Query OK, 0 rows affected (0.05 sec)

mysql> select * from dept2;

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

| deptno | dname |

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

| 10 | Research |

| 20 | Maintenance |

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

2 rows in set (0.00 sec)

会话

mysql> select * from dept2;

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

| deptno | dname |

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

| 10 | Research |

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

1 row in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from dept2;

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

| deptno | dname |

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

| 10 | Research |

| 20 | Maintenance |

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

2 rows in set (0.00 sec)

间隔锁测试，在REPEATABLE-READ隔离模式下，MySQL对数据进行范围、条件扫描时，会对范围内也许并不存在的值进行加锁。

会话

mysql> select @@global.tx_isolation,@@tx_isolation;

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

| @@global.tx_isolation | @@tx_isolation |

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

| REPEATABLE-READ | REPEATABLE-READ |

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

1 row in set (0.00 sec)

mysql> select * from dept2;

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

| deptno | dname |

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

| 10 | Research |

| 20 | Maintenance |

| 30 | Leader |

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

3 rows in set (0.00 sec)

mysql> select * from dept2 where deptno < 30 lock in share mode;

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

| deptno | dname |

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

| 10 | Research |

| 20 | Maintenance |

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

2 rows in set (0.02 sec)

会话

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

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

| @@GLOBAL.tx_isolation | @@tx_isolation |

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

| REPEATABLE-READ | REPEATABLE-READ |

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

1 row in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into dept2 values(40,'Market');

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

READ COMMITTED

类似Oracle的事务隔离级别：每个一致性读，及时在相同的事务中，会读取自己事务最新的快照。对于锁定读（例如SELECT .. FOR UPDATE 或 LOCK IN SHARE MODE），UPDATE, DELETE语句，InnoDB只会锁定索引记录，允许向非锁定的记录插入新的记录。

会话删除一条数据并执行提交操作后，会话会立刻查询到会话更新后的操作。

mysql> SET GLOBAL tx_isolation='READ-COMMITTED';

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

会话

mysql> use fire

Database changed

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

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

| @@GLOBAL.tx_isolation | @@tx_isolation |

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

| READ-COMMITTED | READ-COMMITTED |

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

1 row in set (0.00 sec)

mysql> select * from dept;

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

| DEPTNO | DNAME | LOC |

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

| 10 | ACCOUNTING | NEW YORK |

| 20 | RESEARCH | DALLAS |

| 30 | SALES | CHICAGO |

| 40 | OPERATIONS | BOSTON |

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

4 rows in set (0.08 sec)

mysql> select * from dept2;

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

| deptno | dname |

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

| 10 | Research |

| 20 | Maintenance |

| 30 | Leader |

| 40 | Market |

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

4 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from dept2 where deptno=40;

Query OK, 1 row affected (0.09 sec)

会话

mysql> use fire

Database changed

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

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

| @@GLOBAL.tx_isolation | @@tx_isolation |

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

| READ-COMMITTED | READ-COMMITTED |

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

1 row in set (0.00 sec)

mysql> select * from dept2;

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

| deptno | dname |

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

| 10 | Research |

| 20 | Maintenance |

| 30 | Leader |

| 40 | Market |

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

4 rows in set (0.00 sec)

会话

mysql> commit;

Query OK, 0 rows affected (0.12 sec)

会话

mysql> select * from dept2;

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

| deptno | dname |

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

| 10 | Research |

| 20 | Maintenance |

| 30 | Leader |

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

3 rows in set (0.00 sec)

间隔锁测试，在READ-COMMITTED隔离模式下，会话不会受到会话的影响。

会话

mysql> select @@global.tx_isolation,@@tx_isolation;

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

| @@global.tx_isolation | @@tx_isolation |

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

| READ-COMMITTED | READ-COMMITTED |

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

1 row in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from dept2 where deptno < 30 lock in share mode;

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

| deptno | dname |

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

| 10 | Research |

| 20 | Maintenance |

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

2 rows in set (0.00 sec)

会话

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

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

| @@GLOBAL.tx_isolation | @@tx_isolation |

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

| READ-COMMITTED | READ-COMMITTED |

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

1 row in set (0.00 sec)

mysql> select * from dept2;

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

| deptno | dname |

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

| 10 | Research |

| 20 | Maintenance |

| 30 | Leader |

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

3 rows in set (0.00 sec)

mysql> insert into dept2 values(40,'Market');

Query OK, 1 row affected (0.11 sec)

READ UNCOMMITTED

SELECT语句允许以非锁定的方式执行，但是只有较早版本的行可以使用。因而，使用这个隔离级别，一些的读操作不是一致性的。这个隔离级别也被称为脏读。

SERIALIZABLE

这个隔离级别类似REPEATABLE READ，但是如果autocommit没有开启的话，InnoDB会隐式将所有SELECT语句转化为SELECT ... LOCK IN SHARE MODE。

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!