1

I need to model the status of an object over time. I'm looking for the best way to model this so that concurrent database connections can look at the current status and update the status if the newer status is different.

Suppose minimally a record containsobject_id, status_id, start_date. My first approach was to query for the latest, and then create a new record if the statuses differed.

The issue I experienced is that anytime after I ask for the latest and decide that I want to make a new record, a new record may be created (by some concurrent process) invalidating my decision to create.

One example is on the first status create. Multiple processes may concurrently read no prior status, and each creates the initial status.

I'm looking for advice because I'm not sure if there is simply an easier way to model my data so that I could ensure no duplicates (catching integrity errors), or if I need to look more into database locking. I'm using the Django ORM with postgres.

  • 1
    A typical solution is to have a version number incremented automatically (or based on a fast time stamp) within the database server. That version number is returned with your object. Every time you make a change, you submit the version number; if it is stale (according to the server) the request is rejected. If it is current, the request is accepted and a new version number is generated. At the client end, deal with rejection by trying again some number of times. – Frank Hileman May 11 '18 at 23:18
  • Interesting. I was thinking of adding a previous_id field which would point to the previous status and serve a similar purpose. However, then I'd have two ways to represent the history via previous and time. I think a version field would be better suited. – cdosborn May 11 '18 at 23:42
  • Also, you don't need any locking if you always create new rows, instead of updating old rows (use some garbage collection algorithm if you need to -- that would have to lock). – Frank Hileman May 11 '18 at 23:52
0

A couple of solutions come to mind.

  1. Stored procedures that return error codes when a duplicate is detected

  2. Triggers on the status tables to reject rows that would create sequential statuses (e.g. two "Published" statuses in a row).

  3. Live with duplicates

  4. Implement some sort of optimistic locking mechanism

Doing any of this work outside the database opens you up to race conditions.

Having already implemented several such tables, I think I would go for stored procedures that return an error code on failure, so your application can handle the duplicates better. I don't normally like stored procedures, but they have their place. This may be one of them.

Most ORMs allow you to call stored procedures directly for INSERT, UPDATE or DELETE operations on your object model.

On the other hand, if you just live with it, and have two people flag a record as "trash" then you know that people really, really didn't need it (or "really, really, really" if 3 people did it at the same time).

  • I went with optimistic locking via a 'version' field. I narrowed the code which interacts with the statuses, so that very little code knows about the version field. – cdosborn Jul 11 '18 at 19:24
0

I assume that you wish to ignore the second update. There are other scenarios (e.g. instead of inserting a new row, updating certain fields of the existing row), but I infer that you're looking for something called AddOrIgnore rather than AddOrUpdate.

There are some possible avenues here, some better than others.

Blocking at the application level.

This seems the easiest to implement, but it's not the best solution. There will be a minimal amount of time between checking for existence and saving the object. In that inbetween time, a row could be inserted and you would still end up with duplicate data.

Race conditions are notoriously hard to debug, so I suggest avoiding them at all costs.

Blocking at the database level.

Database locking gives you the ability to prevent the race condition.

However, if you're expecting a lot of concurrent access to that table (for different objects, whose statuses would never collide), then this may become a bottleneck for performance.

I'm not sure if the locking can be guaranteed to apply to every update to the table. Because if it cannot, then you run the risk of other parties forgetting to implement blocking behavior.

Cleaning up after the fact.

This is easier to implement but may end up causing a bit more work. This only applies in cases where your status update does not return a value, and you're not interested in alerting the user/application that a duplicate was found (but instead continue working as normal).

The benefit is that you don't need to block the behavior, and you can simply clean the data afterwards (removing any duplicates). Depending on avoiding duplicates being business critical; you could schedule this as a job. Or you could create a task to check this (preferably in a fire-and-forget separate thread).

The drawback is that you may end up having to undo the work you just did, which is extra work. If your server will be pressed for performance, then more work will always be a bad thing. The question here is how often you expect to run into collisions, and how much you'll be pressed for performance on the server.

A second benefit is that the original request is not slowed down by doing an additional check, and you instead delegate the checking behavior to a secondary thread/task/scheduled job.


This highly depends on your environment.

If you're creating a highly used service where duplicate entries are rare and nothing more than an aesthetic flaw, then blocking would create a bottleneck for everyone. Adding and cleaning, however, will only block those requests which happen to run into a rare collision.

If you're creating a service where duplicate entries can cause major issues, then you should focus on blocking instead of cleaning after the fact.

If you're creating a service where duplicate entries are a common occurrence, then cleaning behavior cause extra work too often. If duplicates are not causing issues, you could clean it up in a scheduled job. If duplicates are causing issues, you should focus on blocking behavior.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.