实验----8

(1)


### 演示脏读



#### MySQL客户机A



```sql

-- 步骤1：设置隔离级别并查询账户余额

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION;

SELECT balance FROM accounts WHERE account_no = '111';

```



#### MySQL客户机B



```sql

-- 步骤3：设置隔离级别并增加账户余额

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION;

UPDATE accounts SET balance = balance + 800 WHERE account_no = '111';

COMMIT;

```



#### MySQL客户机A



```sql

-- 步骤5：再次查询账户余额，发生脏读

SELECT balance FROM accounts WHERE account_no = '111';

```



#### MySQL客户机A



```sql

-- 步骤6：关闭MySQL客户机A和客户机B后，再查看账户余额，发生脏读

SELECT balance FROM accounts WHERE account_no = '111';

```



### 不可重复读演示



#### MySQL客户机A



```sql

-- 步骤1：设置隔离级别并查询账户余额

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

SELECT balance FROM accounts WHERE account_no = '111';

```



#### MySQL客户机B



```sql

-- 步骤3：在事务中增加账户余额

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

UPDATE accounts SET balance = balance + 800 WHERE account_no = '111';

```



#### MySQL客户机A



```sql

-- 步骤4：未提交事务时再次查询账户余额，出现脏读

SELECT balance FROM accounts WHERE account_no = '111';

```



#### MySQL客户机B



```sql

-- 步骤4：提交事务

COMMIT;

```



#### MySQL客户机A



```sql

-- 步骤4：再次查询账户余额，不可重复读

SELECT balance FROM accounts WHERE account_no = '111';

```



### 事务的可重复读



#### MySQL客户机A



```sql

-- 步骤1：设置隔离级别并查询账户信息

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

SELECT * FROM account;

```



#### MySQL客户机B



```sql

-- 步骤2：删除 value 等于 500 的记录

START TRANSACTION;

DELETE FROM account WHERE value = 500;

COMMIT;

```



#### MySQL客户机A



```sql

-- 步骤3：再次查询账户信息，可重复读

SELECT * FROM account;

```



### 事务的幻读



#### MySQL客户机A



```sql

-- 步骤1：设置隔离级别并查询账户信息

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

SELECT * FROM account WHERE account_no = '999';

```



#### MySQL客户机B



```sql

-- 步骤3：插入一条账户信息

START TRANSACTION;

INSERT INTO account (account_no, value) VALUES ('999', 700);

COMMIT;

```



#### MySQL客户机A



```sql

-- 步骤4：再次查询账户信息，避免不可重复读

SELECT * FROM account WHERE account_no = '999';

```



#### MySQL客户机A



```sql

-- 步骤5：插入账户信息

INSERT INTO account (account_no, value) VALUES ('999', 700);

```



### 事务可串行化



#### MySQL客户机A



```sql

-- 步骤1：设置隔离级别并查询账户信息

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

SELECT * FROM account WHERE account_no = '666';

```



#### MySQL客户机B



```sql

-- 步骤2：插入一条记录

START TRANSACTION;

INSERT INTO account (account_no, value) VALUES ('666', 300);

COMMIT;

```



#### MySQL客户机B



```sql

-- 步骤3：查询所有记录

SELECT * FROM account;

```



#### MySQL客户机A



```sql

-- 步骤4：再次查询账户信息，发生了锁等待

SELECT * FROM account WHERE account_no = '666';

```



#### MySQL客户机A



```sql

-- 步骤5：提交事务

COMMIT;

```



#### MySQL客户机B



```sql

-- 步骤5：再次查询所有记录

SELECT * FROM account;

```