在多对多关系中的连接表中保存数据时出现的问题

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    Title VARCHAR(100)
);

CREATE TABLE StudentCourse (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE,
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID) ON DELETE CASCADE
);

-- 插入学生
INSERT INTO Student (StudentID, Name) VALUES (1, 'Alice');
INSERT INTO Student (StudentID, Name) VALUES (2, 'Bob');

-- 插入课程
INSERT INTO Course (CourseID, Title) VALUES (101, 'Math');
INSERT INTO Course (CourseID, Title) VALUES (102, 'Science');

-- 插入学生和课程的关联
INSERT INTO StudentCourse (StudentID, CourseID) VALUES (1, 101);
INSERT INTO StudentCourse (StudentID, CourseID) VALUES (1, 102);
INSERT INTO StudentCourse (StudentID, CourseID) VALUES (2, 101);

-- 使用悲观锁
BEGIN;
SELECT * FROM StudentCourse WHERE StudentID = 1 FOR UPDATE;
-- 执行更新操作
UPDATE StudentCourse SET CourseID = 103 WHERE StudentID = 1 AND CourseID = 101;
COMMIT;

-- 使用乐观锁
UPDATE StudentCourse 
SET CourseID = 103, Version = Version + 1
WHERE StudentID = 1 AND CourseID = 101 AND Version = CurrentVersion;