基于MySQL语法的事务处理和隔离级别实验的代码

3.1 事务处理程序

(1) 隐式事务方式

-- 隐式事务方式
START TRANSACTION;

UPDATE icbc_card
SET restored_money = restored_money - 100
WHERE stu_card_id = (SELECT card_id FROM Stu_Card WHERE stu_id = '05212222');

UPDATE Stu_Card
SET remained_money = remained_money + 100
WHERE stu_id = '05212222';

COMMIT;

(2) 显示用户定义的事务方式

-- 显示用户定义的事务
BEGIN;

UPDATE icbc_card
SET restored_money = restored_money - 100
WHERE stu_card_id = (SELECT card_id FROM Stu_Card WHERE stu_id = '05212222');

UPDATE Stu_Card
SET remained_money = remained_money + 100
WHERE stu_id = '05212222';

COMMIT;

(3) 嵌套事务的编程

-- 嵌套事务
START TRANSACTION;

SAVEPOINT before_update_icbc;

UPDATE icbc_card
SET restored_money = restored_money - 100
WHERE stu_card_id = (SELECT card_id FROM Stu_Card WHERE stu_id = '05212222');

SAVEPOINT before_update_stu_card;

UPDATE Stu_Card
SET remained_money = remained_money + 100
WHERE stu_id = '05212222';

COMMIT;

(4) 命名事务与事务保存点

-- 命名事务与事务保存点
SAVEPOINT my_transaction;

UPDATE icbc_card
SET restored_money = restored_money - 100
WHERE stu_card_id = (SELECT card_id FROM Stu_Card WHERE stu_id = '05212222');

SAVEPOINT my_savepoint;

UPDATE Stu_Card
SET remained_money = remained_money + 100
WHERE stu_id = '05212222';

ROLLBACK TO my_savepoint;

UPDATE Stu_Card
SET remained_money = remained_money + 100
WHERE stu_id = '05212222';

COMMIT;

3.2 观察在更新操作下，事务与锁的变化特征

在MySQL中，默认情况下，InnoDB引擎使用行级锁，因此在更新操作下，会锁定相应的行。

3.3 隔离级别实验

(1) 未提交读隔离级别

-- 未提交读隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 在这里执行查询、修改操作

COMMIT;

(2) 提交读隔离级别

-- 提交读隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 在这里执行查询、修改操作

COMMIT;

(3) 可重复读隔离级别

-- 可重复读隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 在这里执行查询、修改操作

COMMIT;