网站系统建设管理制度,做网站需要什么服务器,店面设计说明,网站怎么广告投放文章目录 脏读1. 设置窗口B中事务的隔离级别2. 演示脏读3. 重新设置窗口B中事务的隔离级别4. 再次验证脏读 不可重复读1. 演示不可重复读2. 设置窗口B中事务的隔离级别3. 再次验证不可重复读 幻读1. 设置窗口B中事务的隔离级别2. 演示幻读3. 重新设置窗口B中事务的隔离级别4. 再… 文章目录 脏读1. 设置窗口B中事务的隔离级别2. 演示脏读3. 重新设置窗口B中事务的隔离级别4. 再次验证脏读 不可重复读1. 演示不可重复读2. 设置窗口B中事务的隔离级别3. 再次验证不可重复读 幻读1. 设置窗口B中事务的隔离级别2. 演示幻读3. 重新设置窗口B中事务的隔离级别4. 再次验证幻读 串行化插入问题1. 设置窗口B中事务的隔离级别2. 演示串行化插入问题 在MySQL中事务有4种隔离级别分别为READ UNCOMMITTED读未提交、READ COMMITTED读已提交、REPEATABLE READ可重复读和SERIALIZABLE串行化。 脏读
READ UNCOMMITTED是事务隔离级别中最低的级别该级别下的事务可以读取其他事务中未提交的数据这种读取方式也被称为脏读。 下面以示例数据库employees.departments表为例演示脏读。首先开启两个终端窗口分别称为窗口A和窗口B。两个窗口都登录到MySQL数据库并切换到employees库。
1. 设置窗口B中事务的隔离级别
MySQL默认隔离级别是REPEATABLE READ该级别可以避免脏读。为演示需求将其隔离级别设置为READ UNCOMMITTED。
mysql SHOW VARIABLES LIKE transaction_isolation;
----------------------------------------
| Variable_name | Value |
----------------------------------------
| transaction_isolation | REPEATABLE-READ |
----------------------------------------
1 row in set (0.00 sec)# 设置隔离级别
mysql SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)mysql SHOW VARIABLES LIKE transaction_isolation;
-----------------------------------------
| Variable_name | Value |
-----------------------------------------
| transaction_isolation | READ-UNCOMMITTED |
-----------------------------------------
1 row in set (0.00 sec)2. 演示脏读
在窗口A中开启事务并更新Sales部门名称为Sales2。
mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales |
--------------------
1 row in set (0.01 sec)mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql UPDATE departments SET dept_nameSales2 WHERE dept_nod007;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales2 |
--------------------
1 row in set (0.00 sec)此时不要提交事务否则无法演示脏读现象。 切换到窗口B查询该部门信息。 mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales2 |
--------------------
1 row in set (0.01 sec)从结果看窗口B中可以看到修改后的部门名称这是由于窗口B的事务隔离级别比较低因此读取了窗口A中还没有提交的内容出现了脏读情况。 脏读演示完毕可以在窗口A中执行ROLLBACK命令回滚事务让数据恢复初始状态。
3. 重新设置窗口B中事务的隔离级别
为防止脏读的发生将B中事务的隔离级别设置为READ COMMITTED该隔离级别可以避免脏读。
mysql SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)mysql SHOW VARIABLES LIKE transaction_isolation;
---------------------------------------
| Variable_name | Value |
---------------------------------------
| transaction_isolation | READ-COMMITTED |
---------------------------------------
1 row in set (0.01 sec)4. 再次验证脏读
在窗口B中查询部门d007的信息。
mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales |
--------------------
1 row in set (0.00 sec)然后在窗口A中开启事务并修改部门d007的信息。
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql UPDATE departments SET dept_nameSales3 WHERE dept_nod007;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales3 |
--------------------
1 row in set (0.00 sec)修改数据后再次查询d007的部门信息已更新但数据未提交。此时在窗口B中查询。
mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales |
--------------------
1 row in set (0.00 sec)结果显示窗口B中并没有查询到窗口A中未提交的内容说明READ COMMITTED隔离级别可以避免脏读。 验证演示完毕可以在窗口A中执行ROLLBACK命令回滚事务让数据恢复初始状态。
不可重复读
在MySQL的READ COMMITTED级别下事务只能读取其他事务已经提交的内容可以避免脏读现象但是会出现不可重复读和幻读的情况。 不可重复读是指在事务内重复读取数据由于多次查询期间其他事务更新了相关数据因此出现多次读取结果不一致的现象。
1. 演示不可重复读
在窗口B中设置事务的隔离级别为READ COMMITTED然后开启事务查询d007的部门信息。
mysql SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)mysql SHOW VARIABLES LIKE transaction_isolation;
---------------------------------------
| Variable_name | Value |
---------------------------------------
| transaction_isolation | READ-COMMITTED |
---------------------------------------
1 row in set (0.00 sec)mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales |
--------------------
1 row in set (0.00 sec)此时在窗口A中使用UPDATE语句更新d007的部门信息。
mysql UPDATE departments SET dept_nameSales4 WHERE dept_nod007;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales4 |
--------------------
1 row in set (0.00 sec)修改成功后再次回到窗口B中查询d007的部门信息。
mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales4 |
--------------------
1 row in set (0.00 sec)结果显示在一个事务中相同的查询语句两次查询结果不一致。其实不可重复读并不算错误但在有些情况下却不符合实际需求。 不可重复读演示完毕可以在窗口B中执行COMMIT命令提交事务。
2. 设置窗口B中事务的隔离级别
为了防止不可重复读的情况出现修改窗口B中事务的隔离级别为REPEATABLE READ。
mysql SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;Query OK, 0 rows affected (0.00 sec)mysql SHOW VARIABLES LIKE transaction_isolation;
----------------------------------------
| Variable_name | Value |
----------------------------------------
| transaction_isolation | REPEATABLE-READ |
----------------------------------------
1 row in set (0.00 sec)3. 再次验证不可重复读
在窗口B中开启事务并查询d007的部门信息。
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales4 |
--------------------
1 row in set (0.00 sec)在窗口A中使用UPDATE语句更新d007的部门信息
mysql UPDATE departments SET dept_nameSales5 WHERE dept_nod007;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales5 |
--------------------
1 row in set (0.00 sec)然后在窗口B中查询d007的部门信息
mysql SELECT * FROM departments WHERE dept_nod007;
--------------------
| dept_no | dept_name |
--------------------
| d007 | Sales4 |
--------------------
1 row in set (0.00 sec)对比窗口B的两次查询结果发现窗口B中事务的隔离级别修改为REPEATABLE READ后查询结果是一致的并没有出现不同的数据说明此该级别可以避免不可重复读的情况。 验证演示完毕可以在窗口B中执行COMMIT命令提交事务。
幻读
幻读又被称为虚读是指在一个事务内两次查询中的数据条数不一致。幻读和不可重复读类似都是在两次查询过程中。区别是幻读是由于其他事务插入记录导致记录数变化而引起的。 理论上REPEATABLE READ级别可以避免脏读、不可重复读但会出现幻读。不过MySQL的存储引擎通过多版本并发控制机制解决了该问题所以当事务隔离级别为REPEATABLE READ时可以避免幻读。
1. 设置窗口B中事务的隔离级别
REPEATABLE READ级别可以避免幻读所以将事务的隔离级别设置得更低。
mysql SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)mysql SHOW VARIABLES LIKE transaction_isolation;
---------------------------------------
| Variable_name | Value |
---------------------------------------
| transaction_isolation | READ-COMMITTED |
---------------------------------------
1 row in set (0.00 sec)2. 演示幻读
在窗口B中开启事务并查询部门名称以字母D开头的信息
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql SELECT * FROM departments WHERE dept_name LIKE D%;
----------------------
| dept_no | dept_name |
----------------------
| d005 | Development |
----------------------
1 row in set (0.00 sec)在窗口A中插入新成立的部门Design
mysql INSERT INTO departments value(d010, Design);
Query OK, 1 row affected (0.01 sec)mysql SELECT * FROM departments WHERE dept_nod010;
--------------------
| dept_no | dept_name |
--------------------
| d010 | Design |
--------------------
1 row in set (0.01 sec)切换到窗口B中再次查询部门名称以字母D开头的信息
mysql SELECT * FROM departments WHERE dept_name LIKE D%;
----------------------
| dept_no | dept_name |
----------------------
| d010 | Design |
| d005 | Development |
----------------------
2 rows in set (0.00 sec)删除数据和新增数据类似也会出现幻读现象。 幻读演示完毕可以在窗口B中执行COMMIT命令提交事务。
3. 重新设置窗口B中事务的隔离级别
为避免幻读现象将窗口B中事务的隔离级别设置为REPEATABLE READ。
mysql SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;Query OK, 0 rows affected (0.00 sec)mysql SHOW VARIABLES LIKE transaction_isolation;
----------------------------------------
| Variable_name | Value |
----------------------------------------
| transaction_isolation | REPEATABLE-READ |
----------------------------------------
1 row in set (0.00 sec)4. 再次验证幻读
在窗口B中开启事务并查询部门名称以字母D开头的信息
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql SELECT * FROM departments WHERE dept_name LIKE D%;
----------------------
| dept_no | dept_name |
----------------------
| d010 | Design |
| d005 | Development |
----------------------
2 rows in set (0.00 sec)在窗口A中插入新成立的部门Design2
mysql INSERT INTO departments value(d011, Design2);
Query OK, 1 row affected (0.00 sec)mysql SELECT * FROM departments WHERE dept_nod011;
--------------------
| dept_no | dept_name |
--------------------
| d011 | Design2 |
--------------------
1 row in set (0.00 sec)切换到窗口B中再次查询部门名称以字母D开头的信息
mysql SELECT * FROM departments WHERE dept_name LIKE D%;
----------------------
| dept_no | dept_name |
----------------------
| d010 | Design |
| d005 | Development |
----------------------
2 rows in set (0.00 sec)对比窗口B中的两次查询结果发现窗口B设置事务的隔离级别为REPEATABLE READ后同一个事务的两次查询结果是一致的并没有读取其他事务新插入的记录。说明REPEATABLE READ可以避免幻读。 验证演示完毕可以在窗口B中执行COMMIT命令提交事务。
串行化插入问题
事务的隔离级别设置为SERIALIZABLE可以避免脏读、不可重复读和幻读但也会导致大量的超时和锁竞争数据库性能极差。
1. 设置窗口B中事务的隔离级别
mysql SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)mysql SHOW VARIABLES LIKE transaction_isolation;
-------------------------------------
| Variable_name | Value |
-------------------------------------
| transaction_isolation | SERIALIZABLE |
-------------------------------------
1 row in set (0.01 sec)2. 演示串行化插入问题
在窗口B中开启事务并查询表中数据。
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql SELECT COUNT(1) FROM departments;
----------
| COUNT(1) |
----------
| 11 |
----------
1 row in set (0.00 sec)切换到窗口A并插入新成立的部门Design3
mysql INSERT INTO departments value(d012, Design3);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction窗口A中执行插入语句后不会立即执行成功而是光标闪烁一直等待。此时提交窗口B中的事务窗口A中的插入操作会立即执行否则A中的操作一直等待直到最后超时提示信息。 默认超时时间是50秒。
