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

Question

Basically, I'm trying to figure out how to write the correct (or "write correctly"?) Transactional code when developing a REST service using Jax-RS and Spring. In addition, we use JOOQ to access data. But this does not have to be very relevant ...
Consider a simple model where we have some organizations that have the following fields: "id", "name", "code" . All this must be unique. There is also a status field.
At some point, the organization may be deleted. But we don’t want to delete the data at all, because we want to save it for analytics / maintenance purposes. Therefore, we simply set the status field of the organization 'REMOVED' .
Since we are not deleting the organization row from the table, we cannot simply put a unique constraint in the "name" column, because we can delete the organization and then create a new one with the same name. But suppose that the codes must be unique globally, so we have a unique restriction on the code column.

So, let's look at this simple example that creates an organization by doing some validation 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)); } } 

The DAO service looks like this:

 @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 raises some questions:

• Should I put the @Transactional annotation in the Resource createOrganization method? Or should I create another service that is negotiating with the DAO and add @Transactional annotation to this method? Something else?
• What happens if two users send a request simultaneously with the same "code" field. Before the first transaction, transactions were successfully completed, so 409 requests will not be sent. Then the first transaction will be executed correctly, but the second will violate the DB constraint. This will throw a SQLException. How to gracefully handle this? I mean, I still want to show a good error message on the client side, stating that this name is already in use. But I can not parse SQLException or anything else. Can I?
• Like the previous one, but this time the "name" is not unique. In this case, the second transaction will not violate any restrictions, which leads to the presence of two organizations with the same name, which violates our availability restrictions.
• Where I can see / study tutorials / code / etc, you consider excellent examples of how to write correct / reliable REST + DB code with complex work logic. Github / books / blogs, whatever. I tried to find something like this myselft, but most of the examples are simply focused on plumbing - add these libraries to maven, use these annotations, there is your simple CRUD, the end. They do not contain any transactional considerations at all. I.e.

UPDATE: I know about the isolation level and the usual error / separation matrix (dirty reads, etc.). The problem that I have is finding some sort of “production-ready” sample. Or a good book on the topic. I still don’t understand how to handle all errors correctly. I think I need to repeat a couple of times if the transaction failed .. and just throw some general error and implement a client that handles this. But do I really need to use SERIALIZABLE mode when I use range queries? Because it will greatly affect performance. But otherwise, how can I guarantee that the transaction will fail.

In any case, I decided that at the moment I need more time to learn about transactions and managing db in general to solve this problem ...

Answer 1

As a rule, without talking about transactions, the endpoint should only capture parameters from the request and call the service. He does not have to conduct business logic.

It seems your checkXXX methods are part of the business logic because they cause errors in domain-specific conflicts. Why not include them in the Service in one method, which, incidentally, is 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 it, since your parameters are strings, but they can be any. I'm not sure that you want to throw Responses.abortConflict at the service level, because it seems like a REST concept, but you can define your own exception types if you want.

Endpoint code should look like this, but it may contain an additional try-catch block that converts thrown exceptions into 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)); } 

Regarding questions 2 and 3, transaction isolation levels are your friends. Put the insulation level high enough. I think "repeat reading" is appropriate in your case. The checkXXX methods will detect that some other transaction commits entities with the same name or code, and this ensures that situations remain at the time the method is created. Another useful read regarding Spring and transaction isolation levels.

Answer 2

According to my understanding, the best way to handle a DB level transaction is to use Spring Isolation trnsaction effectively in the dao layer. Below is an example of a standard standard codec 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 here

Answer 3

Separation of Anxiety:

• Jax-rs resource level (endpoint): just process the request, call the service and wrap the potential exception in the corresponding response code (just understand and wrap manually or use exception mapper ).
• Service level / business: derive a transactional method for each unit of work, a business error should be treated as an exception, and operational errors should be treated as uncontrolled (subclasses of RuntimeException ).
• Data access level: just process the data access material (i.e. get the db context, execute the query and ultimately match the result).

I insist on one thing: a good place for transaction boundaries is the place where your business methods are defined. Transaction volume should be a business unit of work.

Regarding the concurrency problem, there are two ways to solve this concurrency problem: pessimistic or optimistic blocking.

• Pessimistic:

  • Lock
  • make your stuff
  • Refresh
  • Lock lock

• Optimistic:

  • check version
  • make your stuff
  • update if the version is the same, failed otherwise

Pessimistic is a problem of scalability and performance, an optimistic problem is that sometimes you end up sending an operational error to end users.

I would personally go with an optimistic lock in your case, JOOQ supports it

Answer 4

First of all, the DAO layer should not even know that it is under the control of the REST web service. Be sure to share responsibility.

Keep @Transactional in the DAO. If you publish only one report, you need to decide if you agree with the dirty readings. Basically, find out what is the lowest isolation level for your application. Each method starts a new transaction (if not called from another method that has already been started), and if any exceptions are thrown, it will roll back from any calls. You can configure a custom ExceptionHandler in your controller to handle SQLDataIntegrityExceptions (for example, you are inserting sample code).

Use the primary aggregation key that covers (identifier, name, code, status), so you may have an organization with the same name, but one will be CURRENT and one will be REDACTED