How to write correct/reliable transactional code with JAX-RS and Spring

Question

Basically, I am trying to understand how to write correct (or "to correctly write"?) transactional code, when developing REST service with Jax-RS and Spring. Also, we're using JOOQ for data-access. But that shouldn't be very relevant...
Consider simple model, where we have some organisations, that have these fields: "id", "name", "code". All of which must be unique. Also there's a status field.
Organization might be removed at some point. But we don't want to remove the data altogether, because we want to save it for analytical/maintenance purposes. So we just set organization 'status' field to 'REMOVED'.
Because we don't delete the organization row from the table, we can't simply put the unique constraint on the "name" column, because, we might delete organization and then create a new one with the same name. But let's assume that codes has to be unique globally, so we DO have a unique constraint on the code column.

So with that, let's see this simple example, that creates organization, performing some checks along the way.

Resource:

@Component
@Path("/api/organizations/{organizationId: [0-9]+}")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaTypeEx.APPLICATION_JSON_UTF_8)
public class OrganizationResource {
    @Autowired
    private OrganizationService organizationService;

    @Autowired
    private DtoConverter dtoConverter;

    @POST
    public OrganizationResponse createOrganization(@Auth Person person, CreateOrganizationRequest request) {

        if (organizationService.checkOrganizationWithNameExists(request.name())) {
            // this throws special Exception which is intercepted and translated to response with 409 status code
            throw Responses.abortConflict("organization.nameExist", ImmutableMap.of("name", request.name()));
        }

        if (organizationService.checkOrganizationWithCodeExists(request.code())) {
            throw Responses.abortConflict("organization.codeExists", ImmutableMap.of("code", request.code()));
        }

        long organizationId = organizationService.create(person.user().id(), request.name(), request.code());
        return dtoConverter.from(organization.findById(organizationId));
    }
}

DAO service looks like that:

@Transactional(DBConstants.SOME_TRANSACTION_MANAGER)
public class OrganizationServiceImpl implements OrganizationService {
    @Autowired
    @Qualifier(DBConstants.SOME_DSL)
    protected DSLContext context;

    @Override
    public long create(long userId, String name, String code) {
        Organization organization = new Organization(null, userId, name, code, OrganizationStatus.ACTIVE);
        OrganizationRecord organizationRecord = JooqUtil.insert(context, organization, ORGANIZATION);
        return organizationRecord.getId();
    }

    @Override
    public boolean checkOrganizationWithNameExists(String name) {
        return checkOrganizationExists(Tables.ORGANIZATION.NAME, name);
    }

    @Override
    public boolean checkOrganizationWithCodeExists(String code) {
        return checkOrganizationExists(Tables.ORGANIZATION.CODE, code);
    }

    private boolean checkOrganizationExists(TableField<OrganizationRecord, String> checkField, String checkValue) {
        return context.selectCount()
                .from(Tables.ORGANIZATION)
                .where(checkField.eq(checkValue))
                .and(Tables.ORGANIZATION.ORGANIZATION_STATUS.ne(OrganizationStatus.REMOVED))
                .fetchOne(DSL.count()) > 0;
    }
}

This brings some questions:

1. Should I put @Transactional annotation on Resource's createOrganization method? Or should I create one more service that talks to DAO and put @Transactional annotation to it's method? Something else?
2. What would happen if two users concurrently send request with the same "code" field. Before first transaction is commited the checks are successfully passed, so no 409 respones will be sent. Than first transaction will be committed properly, but the second one will violate DB constraint. This will throw SQLException. How to gracefully handle that? I mean I still want to show nice error message on the client side, saying that name is already used. But I can't really parse SQLException or smth.. can I?
3. Similar to the previous one, but this time "name" is not unique. In this case, second transaction will not violate any constraints, which leads to having two organization with the same name, that violates our buisness constraints.
4. Where can I see/learn tutorials/code/etc., that you consider great examples on how to write correct/reliable REST+DB code with complicated buisness logic. Github/books/blogs, whatever. I've tried to find something like that myselft, but most examples just focus on the plumbing - add these libs to maven, use these annotations, there is your simple CRUD, the end. They don't contain any transactional considirations at all. I.e.

UPDATE: I know about isolation level and the usual error/isolation matrix (dirty reads, etc..). The problem I have is finding some "production-ready" sample to learn from. Or a good book on a subject. I still don't really get how to handle all the errors properly.. I guess I need to retry a couple of times, if transaction failed.. and than just throw some generic error and implement client, that handles that.. But do I really have to use SERIALIZABLE mode, whenever I use range queries? Because it will affect performance greatly. But otherwise how can I garantee that transaction will fail..

Anyway I've decided that for now I need more time to learn about transactions and db management in general to tackle this problem...

Answer 1

Generally, without talking about transactionality, endpoint should only grab parameters from the request and call the Service. It shouldn't do business logic.

It seems your checkXXX methods are part of the business logic, because they throw errors about domains-specific conflicts. Why not put them into the Service into one method, which is by the way transactional?

//service code
public Organization createOrganization(String userId, String name, String code) {

    if (this.checkOrganizationWithNameExists(request.name())) {
        throw ...
    }

    if (this.checkOrganizationWithCodeExists(code)) {
        throw ...
    }

    long organizationId = this.create(userId, name, code);
    return dao.findById(organizationId);
}

I took as your parameters are Strings, but they can be anything. I'm not sure you want to throw Responses.abortConflict in the service layer because it seems to be a REST concept, but you can define your own exception types for it if you want.

Endpoint code should look like this, however, it might contain additional try-catch block which converts the thrown exceptions to Error responses:

//endpoint code
@POST
public OrganizationResponse createOrganization(@Auth Person person, CreateOrganizationRequest request) {
    String code = request.code();
    String name = request.name();
    String userId = person.user().id();
    return dtoConverter.from(organizationService.createOrganization(userId, name, code));
}

As for question 2 and 3, transaction isolation levels are your friends. Put isolation level high enough. I think 'repeatable read' is the suitable one in your case. Your checkXXX methods will detect if some other transaction commits entities with the same name or code and it's guaranteeed that the situations stays by the time 'create' method is executed. One more useful read regarding Spring and transaction isolation levels.

Answer 2

As per my understanding the best way to handle DB level transaction you must use Spring's Isolation trnsaction in effective way in the dao layer. Below is sample industry standard codde in your case...

public interface OrganizationService {
    @Retryable(maxAttempts=3,value=DataAccessResourceFailureException.class,backoff=@Backoff(delay = 1000))
    public boolean checkOrganizationWithNameExists(String name);    
}

@Repository
@EnableRetry
public class OrganizationServiceImpl implements OrganizationService {
    @Transactional(isolation = Isolation.READ_COMMITTED)
    @Override
    public boolean checkOrganizationWithNameExists(String name){
        //your code
        return  true;       
    }
}

Please pinch me if I'm wrong in here