使用@Transactional(isolation = Isolation.SERIALIZABLE)如何仅锁定部分行？

Great question—using the SERIALIZABLE isolation level doesn't force you to lock every row in your table. Let's walk through your scenario, fix the inefficiency in your current code, and show you how to lock only the rows that matter.

First, Let's Diagnose the Problem with Your Current Code #

Right now, your code does sch.getAllStudents() to load every student in the school. Under SERIALIZABLE isolation, most databases (like MySQL InnoDB) will lock all rows returned by that query, or even add gap locks to prevent new rows from being inserted into that school's student set. This is overkill because you only care about checking if a specific student name exists in the school—not about all students.

Solutions to Lock Only Specific Rows #

1. Replace Collection Loading with a Targeted Existence Check

Instead of loading every student and then checking the name list, use a direct query to check if the student already exists in the school. This tells the database exactly what you're looking for, so it only locks rows that match your criteria (or protects that specific "gap" from new inserts).

Here's how to rewrite your logic:

@Transactional(isolation = Isolation.SERIALIZABLE)
public default Student doSomething(Student student) {
    School sch = student.getSchool(); 
    // Check directly if this name exists in the school
    boolean studentExists = studentRepository.existsBySchoolAndName(sch, student.getName());
    
    if (!studentExists) {
        save(student);
    }
    return student;
}

With this query (SELECT 1 FROM students WHERE school_id = ? AND name = ?), the database will only lock the row if a matching student exists. If no match exists, it will lock the gap where that student would be inserted—preventing other transactions from adding the same name to the school, which is exactly what you need.

2. Use Explicit Pessimistic Locking (If You Need More Control)

If you want to explicitly lock the matching row (for example, if you might update it later), you can use JPA's pessimistic write locking. This ensures that once you check for the student, no other transaction can modify or insert that specific student until your transaction ends.

Example:

@Transactional(isolation = Isolation.SERIALIZABLE)
public default Student doSomething(Student student) {
    School sch = student.getSchool(); 
    // Lock the row if it exists
    Student existingStudent = studentRepository.findFirstBySchoolAndName(
        sch, 
        student.getName(), 
        LockModeType.PESSIMISTIC_WRITE
    );
    
    if (existingStudent == null) {
        save(student);
    }
    return student;
}

The LockModeType.PESSIMISTIC_WRITE tells the database to acquire an exclusive lock on the matching row (if found), and combined with SERIALIZABLE isolation, it prevents any concurrent modifications or inserts of the same school-name pair.

3. Understand Database-Specific Behavior

Keep in mind that SERIALIZABLE works slightly differently across databases:

• MySQL InnoDB: Uses next-key locking (row locks + gap locks) to prevent phantom reads. A targeted query like existsBySchoolAndName will narrow the lock to the exact row/gap you care about.
• PostgreSQL: Uses serializable snapshot isolation (SSI), which detects concurrent conflicts instead of using strict locks. A precise query reduces the chance of conflicts being triggered.

Key Takeaway #

The main mistake in your original code is loading all students in the school—this forces the database to lock far more rows than necessary. By switching to a targeted query that checks exactly what you need (does this school already have a student with this name?), you let the database optimize the locking to only the relevant rows, while still maintaining the SERIALIZABLE isolation guarantees you need.

内容的提问来源于stack exchange，提问作者saferJo