How do I approach structuring when to read/write objects to db?

Question

This is a problem I've faced in many languages over the years (js, go, c, f#, haskell, python, ...), but I haven't found a general approach to solving this yet. Learning about consistency models in databases made me realise all my approaches so far are failing in different ways, usually I end up with a ton of accidental race conditions.

The problem:

I'm fetching data from a db, and storing it in data structures in memory on a server. There's no limit on how many parts of a single codebase may hold those elements at a time, so we might hold two contradictory copies of a single datum in memory if we're not careful. Some updates require atomic changes in the db (login attempts, for example), while others (like user display name) may be stale, or cached for a while. I don't want to call the db on each read/write, because it's way to slow and expensive.

How do you usually approach this problem? What's the pros/cons of your approach? Are there some language-agnostic best-practices in this area? Some way to be reasonably confident the code free from data races?

Tagging as ORM even though it's not strictly about OOP-RDBMS mappings, because I don't know what to call the more general thing.

Answer 1

You can take a look at Unit of work:

[A unit of work] maintains a list of objects affected by a business transaction and coordinates the writing out of changes and the resolution of concurrency problems.

The pattern is described in Patterns of Enterprise Application Architecture

As for avoiding race conditions, you might use some kind of locking mechanism. There are a few strategies. This answer explains it nicely, in summary:

• Optimistic locking: when you read a record you take some kind of version from it (e.g. the last modification timestamp). And when it's time to write the changes you check if the record has been modified or not, if so, you abort the transaction.
• Pessimistic locking: before reading the record, you create a lock so that nobody else can read/write to it while you work with it. Once the changes are written or discarded, you release the lock.

Chosing one or the other depends on the use case, mainly depending on how many clients you expect to write to a given resource at the same time. If there are many, pessimistic locks would be the choice.

The answer talks about DB locks, but you can apply the idea to cache locks (redis, memcached).

Unfortunately, AFAIK there's no simple way to "be free from race conditions". We have to think about each case/transaction.

Answer 2

There are two primary classes of ways to address this problem:

• locking
• ignoring the problem

Which is best for you depends entirely on your particular application needs and performance tolerance. And often, within an application, for different kinds of data, different strategies make sense.

LOCKING

Locking comes in many flavors (optimistic, strict etc). But one thing all the flavors share in common, is they create a huge performance overhead, and a bit of a coding/logic overhead (complexity of implementation).

Another orthogonal mechanism that CAN work well with locking, is data classification/segregation (sharding). Basically - you use some feature of the data to segregate where it is stored (e.g. if there is an 'id' field, use id%3 and split the data across three database servers), and then you can implement cheaper lightweight locking strategies on that data (and have less lock contention).

IGNORE THE PROBLEM

For many programming problems, this just doesn't matter. If you can convince yourself you are in one of those situations, just relax and don't worry about it. For example, logging (or event generation/processing). Here things that are created are immutable, and so never get updated. You just add new ones.

Answer 3

You need to have transactions to update some data, you need to control where it's read and written. A database if the "usual" place for that, but it can have performance issues if all of your requests for that data have to hit the database.

An option I've seen used to alleviate this issue is to introduce a data fabric layer capable of dealing with transactions as well. For example, you can look at something like Apache Ignite which supports transactions

Answer 4

The solution I have found that works is to use the DBMS as a full data management system rather than just as a persistence layer. As studies as shown - such as Feral Concurrency Control concentrating on Ruby on Rails - implementing data integrity in the application/ORM means they are subject to race conditions.

Instead any business rule that can be implemented as a data integrity constraint within the database should be. Ideally these should be implemented declaratively using the check, unique and foreign key constraints available in most SQL DBMS. The implementation of these in the DBMS engine should include any concurrency control mechanisms required.

Unfortunately most, if not all, SQL DBMS do not support the generic SQL assertion statement which would allow any data integrity constraint of arbitrary complexity to be declared. Therefore, I have implemented these programatically using triggers and included my own concurrency control using advisory locks in PostgreSQL or DBMS_LOCK in Oracle.

By also using optimistic or pessimistic locking techniques, I am sure I have properly implemented a concurrency control mechanism that guarantees the integrity of the data without any race conditions.