Lab4-事务与并发编程实现

实验四事务与并发编程实现

实验目的：

学习MySQL中事务编程方法与技术，初步理解并发操作中锁的使用方式。

实验内容：

1、编写一个基于事务的存储过程。
2、练习理解并发操作中锁的使用。

实验过程及要求：

1、删除ustudent表中的触发器，编写存储过程，使用事务实现：在学生表中更新（增、删、改）学生记录时，同时更新班级表中班级的人数

2、创建表test(c1 int primary key,c2 varchar(5),c3 varchar(5)),插入如下记录（1,’a’,’k1’）, （2,’b’,’k2’）, （3,’c’,’k3’）, （4,’d’,’k4’）, （5,’a’,’k2’）, （6,’b’,’k1’）, （7,’c7’,’k4’）, （8,’d’,’k3’）, （9,’a’,’k4’）, （10,’b’,’k3’）, （11,’c’,’k2’）, （12,’d’,’k1’）
打开两个客户端，分别执行Set autocommit=0命令后，在一个客户端执行一条命令，另一客户端分别执行所有命令时，观察结果。(本题只观察结果即可)
(1) Select * from test where c2=’b’ and c1=6
(2) Select * from test where c2=’b’ and c1=6 for update
(3) Select * from test where c2=’b’ and c3=’k1’
(4)Select * from test where c2=’b’ and c3=’k1’ for update

具体实现操作

1、删除ustudent表中的触发器，编写存储过程，使用事务实现：在学生表中更新（增、删、改）学生记录时，同时更新班级表中班级的人数

创建一个触发器存储过程 UpdateClassStudentCount


DELIMITER //

CREATE TRIGGER UpdateClassStudentCount
AFTER INSERT ON ustudent
FOR EACH ROW
BEGIN
    DECLARE class_id VARCHAR(2);
    DECLARE student_count INT;

    -- class id
    SET class_id = NEW.gid;

    -- count of students in the class
    SELECT COUNT(*) INTO student_count FROM ustudent WHERE gid = class_id;

    -- ugrade table
    UPDATE ugrade SET gyear = student_count WHERE gid = class_id;
END //

DELIMITER ;

删除触发器 UpdateClassStudentCount

drop TRIGGER if exists UpdateClassStudentCount;

在高并发的环境下，多个事务可能会尝试同时修改相同的数据。为了保证数据的一致性，在事务中使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 语句来显式地获取锁。

编写存储过程，使用事务实现：在学生表中更新（增、删、改）学生记录时，同时更新班级表中班级的人数

DELIMITER //

CREATE PROCEDURE UpdateStudentAndClassCount(IN student_id VARCHAR(9), IN class_id VARCHAR(2), IN action_type VARCHAR(10))
BEGIN
  DECLARE student_count INT;

  -- 开始事务
  START TRANSACTION;

  -- 根据操作类型进行相应的操作
  IF action_type = 'INSERT' THEN
    -- 插入学生记录
    INSERT INTO ustudent(Sid, gid) VALUES (student_id, class_id);
  ELSEIF action_type = 'DELETE' THEN
    -- 删除学生记录
    DELETE FROM ustudent WHERE Sid = student_id;
  ELSEIF action_type = 'UPDATE' THEN
    -- 更新学生记录
    UPDATE ustudent SET gid = class_id WHERE Sid = student_id;
  END IF;

  -- 更新班级表中的学生人数
  SELECT COUNT(*) INTO student_count FROM ustudent WHERE gid = class_id;
  UPDATE ugrade SET gnum = student_count WHERE gid = class_id;

  -- 提交事务
  COMMIT;
END //

DELIMITER ;

2、创建表test(c1 int primary key,c2 varchar(5),c3 varchar(5)),插入如下记录（1,’a’,’k1’）, （2,’b’,’k2’）, （3,’c’,’k3’）, （4,’d’,’k4’）, （5,’a’,’k2’）, （6,’b’,’k1’）, （7,’c7’,’k4’）, （8,’d’,’k3’）, （9,’a’,’k4’）, （10,’b’,’k3’）, （11,’c’,’k2’）, （12,’d’,’k1’）
打开两个客户端，分别执行Set autocommit=0命令后，在一个客户端执行一条命令，另一客户端分别执行所有命令时，观察结果。(本题只观察结果即可)

(1) Select * from test where c2=’b’ and c1=6

先建立数据表插入必要的测试数据

create Table if not exists test(
    c1 int primary key ,
    c2 VARCHAR(10),
    c3 VARCHAR(10)
);

INSERT INTO test VALUES
(1,'a','k1'),(2,'b','k2'),(3,'c','k3'),(4,'d','k4'),
(5,'a','k2'),(6,'b','k1'),(7,'c7','k4'),(8,'d','k3'),
(9,'a','k4'),(10,'b','k3'),(11,'c','k2'),(12,'d','k1');

打开两个sql窗口