Lock and Isolation for resource reservation patter

问题:

I need to solve a resource reservation pattern with Spring and MariaDB. The problem is very simple, I have a guest table where I store guest names of events, I have to be sure that the guest count for the event must be less or equals the maximum capacity.

This is the table:

create table guest(
    event int,
    name varchar(50)
)
create index event on guest (event);

What is the right lock procedure and isolation level for DB? Please consider that this code will run in multi-threading container. I chose to lock the table with a "SELECT...FOR UPDATE" to limit the lock only in one event rows.

// START TRANSACTION
@Transactional 
public void reserve(int event, String name){
    getJdbc().query("SELECT * FROM guest WHERE id=? FOR UPDATE",event);
    Integer count=getJdbc().queryForObject("SELECT COUNT(*) FROM guest WHERE id=?",Integer.class,event);
    if(count>=MAX_CAPACITY)
        throw new ApplicationException("No room left");
    getJdbc().query("INSERT INTO guest VALUES (?,?)",event,name);
}
// COMMIT

I made some test and seems that I need the READ_COMMITTED isolation levels, am I right? This is what I found:

This is the first time I have to change the isolation level and I'm a bit surprised of this need, can you confirm that the standard MariaDB isolation level REPETABLE_READ fails with this pattern?

回答1:

The problem is, that during the transaction in Thread 2, repeatable_read guarantees that you see the DB in the state as it was at the transaction start. So effects of transaction 1 which has not been completed yet at that time, will be hidden. Therefore you will always see the same number of records independent on what other transactions meanwhile did. So both transactions will insert a record.

READ_COMMITTED means according to the documentations: "Each consistent read, even within the same transaction, sets and reads its own fresh snapshot". Fresh snapshot means, that the results of committed concurrent transactions will be included.

回答2:

A suggestion for working around the problem. This involves keeping a counter instead of doing COUNT(*). (Yes, this violates the principle of not having redundant info.)

CREATE TABLE EventCount ( event ..., ct INT ..., PRIMARY KEY(event) ) ENGINE=InnoDB;

START TRANSACTION;
    INSERT ...;
    UPDATE EventCount
        SET ct = ct + 1
        WHERE event = ?;
    ct = SELECT ct FROM EventCount WHERE event = ?;
    if (ct > max)
    {
        ROLLBACK;
        exit;
    }
COMMIT;

(Caveat: I have not verified that this works for your situation.)