Implementing transactional entity lockouts with Spring and JPA

Question

Spring Boot/Java 8/MySQL here.

I have a widgets table in my MySQL DB that is modeled by a JPA entity like so:

@Entity
@Table(name = "widgets")
@Data
public class Widget {

    @Column(name = "widget_property")
    private String property;

    // all remaining fields here...

}

And which has a matching, typical, repository:

@Repository
public interface WidgetRepository extends CrudRepository<Long,Widget> {
  // JPA methods here...
}

And which also has corresponding controller and service classes as well:

@RestController
@RequestMapping("/v1/widgets")
public class WidgetResource {

  @Autowired
  private WidgetService widgetService;

  // API endpoint methods to CRUD widgets using the service...

}

@Service
public class WidgetService {
  // widget methods here...
}

Pretty standard stuff. I now need to present my users with a list of all Widgets and allow a user to "lock" a particular Widget for editing. The idea/flow I'm thinking here is:

1. A user "locks" a widget, perhaps by clicking a button or selecting a drop-down in the UI
2. While the widget is "locked" it will not appear as being available to any other users; also, if another user happened to view the screen (list of available widgets) just before the first user locked a particular widget, and this other user just happens to try and lock the same widget, they will not be allowed to do so. In general, if a widget is locked, no other user can lock it.
3. A user can only edit a widget they have "locked"
4. When a user is done editing it they may "unlock" it which allows it to be listed, re-locked and re-edited by other users. Or, after 12 hrs, a widget automatically unlocks itself (in case the locking user forgot about it and went home)

I know there's probably all sorts of tricks with optimistic locking that can be done here, but I'm wondering if Spring has anything out of the box (perhaps that leverages @Transactional?) that would help me out here. I'm not opposed to implementing some type of blocking-queue-with-competing-consumers pattern, however I cannot add any infrastructure or deployment complexity, so it would have to be an in-memory, transactional queuing solution.

Any ideas as to how I can implement this using my tech stack, data model and required user flow? Thanks!

Answer 1

Short Answer

Don't use JPA locking to implement this. It's insufficient to deliver a good experience in the style you defined. Record check-out information about the widgets instead.

Why?

Some aspects of your desired user experience ("idea/flow") do not fit well with JPA or database locking methods.

• "While a widget is locked it will not appear as being available to any other users."

• Long lived locks that depend on the user or a 12 hour timeout to release.

Take a look at the two kinds of locking JPA offers.

JPA Pessimistic will begin a transaction and use SELECT ... FOR UPDATE to get the row(s) that represent the widget, placing an exclusive lock on each row.

• When a widget is locked, anyone else attempting to retrieve that widget waits for the lock to be released. (Ouch!) That's what exclusive means.

• If you bypass these locks to read widgets with READ_UNCOMMITTED isolation level you get no indication that the rows are locked.

So there's no good way to show users that another user has locked a widget. There other worse side effects of leaving a database transaction open for 12 hours, or even 12 minutes before timing out and rolling back. It's almost never a good practice.

JPA Optimistic will retrieve your widget rows and take note of their versions. It will not hold a lock while the user ponders what to do. It will check whether the rows have changed when saving the user's changes.

• This is not the workflow you wanted. You wanted users to be able to lock a widget and be confident that their changes will be accepted.

In both cases you would have to hold on to the widget's JPA Entity somewhere between user requests for up to 12 hours to be able to save changes, commit or rollback.

In order to even theoretically get the desired feature using JPA locking you probably end up building an in-memory structure to allow searching for locked widgets and expiring after 12 hours. At best you would end up fighting the tools.

Ok Then What? Check This Out

The workflow you outlined is like an old style source control check-out process where the widget is locked until checked back in or the check-out is released.

The simplest way to implement that is probably to record check-out information whenever a user locks a widget.

• User1 retrieves list of widgets, with indication of which are checked out and who checked them out.

• Abandoned check-outs older than 12 hours can be shown as not-checked out, or as abandoned, as you wish.

• When User1 wants to lock a widget, you record the time of the check-out and User1's identity.

• If User2 has beaten User1 to checking out the widget, User1's lock request fails with an appropriate message, 'blaming' User2. There are no race conditions if you use some form of mutual exclusion to update the check-out info.

• When User1 locks a widget, makes changes, and saves, the check-out must match in order for changes to be saved, and the check-out info is cleared at that point.

• When User1 locks a widget and goes home for the evening, the lock automatically expires after 12-hours because the check-out info includes the time of check-out.

• If another user wants to lock the widget after 12 hours you can let that happen. But if User1 returns and no one has locked the widget User1 could still be allowed to save changes. It's up to you.

Where to Store The Check-Out Info?

The database would be a good choice. You could add columns to the widget table to contain the check-out info, or create a new table for this purpose.

• Database can provide mutual exclusion to prevent race conditions during check-outs and saves.

• Database persists through brief outages and software upgrades.

If you can't change the database schema you could implement it as an in-memory structure as you suggested.

My go-to structure for that would be ConcurrentHashMap keyed by widget's unique identifier. It has non-blocking random and sequential read access and a good set of atomic operations you can use to avoid race conditions.

The main downsides to an in-memory structure are...

• Limited to a single server JVM

• Does not survive restart of service, or software upgrade.

• Requires you to clean up old entries. Not hard, just one more thing.

In the database you could just leave old check-out info in columns in the widget's table. In memory, you could just leave the entries too if you know they will grow to an acceptable maximum.