资源预留模式的锁定和隔离 [英] Lock and Isolation for resource reservation pattern

问题描述

我需要使用Spring和MariaDB解决资源预留模式. 问题非常简单，我有一个来宾表，用于存储事件的来宾名称，我必须确保该事件的来宾计数必须小于或等于最大容量.

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.

这是表格:

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

什么是正确的DB锁定过程和隔离级别? 请考虑该代码将在多线程容器中运行. 我选择使用"SELECT ... FOR UPDATE"锁定表，以仅将锁定限制在一个事件行中.

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

我进行了一些测试，似乎我需要READ_COMMITTED隔离级别，对吗? 这是我发现的:

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

这是我第一次必须更改隔离级别，对此感到有些惊讶，您是否可以确认标准MariaDB隔离级别REPETABLE_READ不能通过这种模式执行?

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?

推荐答案

问题是，在线程2中进行事务处理时，repeatable_read保证您看到的DB处于事务开始时的状态.因此，那时尚未完成的事务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根据文档的意思是:即使在同一事务中，每次一致性读取也会设置并读取其自己的新快照".全新快照意味着将包括已提交并发事务的结果.

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.

这篇关于资源预留模式的锁定和隔离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！