If your data validation doesn't involve your database, you're asking for conflicts.
Application code alone cannot prevent conflicting data. To explain why, let's start with a familiar example.
Say you're writing a web app where every user needs a unique username. How do you prevent duplicates?
If you're using Active Record or a Rails-inspired ORM, you might write something like validates :username, uniqueness: true. But you probably know that's not enough.
Your app probably has a way to process more than one request at a time, using multiple processes, threads, servers, or some combination of the three. If so, multiple simultaneous requests could claim the same username. You'll have to add a unique index on the username column to guarantee there are no duplicates.
This is standard advice. But there's an underlying principle that you may have missed: any validation that depends on what's currently in the database is unreliable without a guarantee from the database itself.
Does this user's account have enough money to cover this purchase right now?
Does this rental property have an existing reservation whose date range overlaps the one I'm requesting right now?
Is there an employee with the job title "manager" assigned to this store right now?
In each of these cases, your application code can check what's in the database before writing. But in each case, there's a race condition: things may change between the time it checks and the time it writes. Other requests are being handled simultaneously, and your multiple application processes do not coordinate their work. Only the database is involved in every modification of data, so only it can prevent conflicts.
There are multiple ways you can enlist your database's help here, but the simplest and most performant option is to use database constraints.
Before I explain constraints, let's look at some of the alternatives.
A transaction guarantees that all its statements will be executed successfully, or else none of them will be executed at all. And in this case, if Active Record finds an existing user with this username, it aborts the transaction. So why isn't it enough?
Well, it could be - depending on your database settings. Transactions are run with varying isolation levels, meaning "how much can concurrent transactions affect this one?" The good news is that if you use "serializable isolation" for your transactions, the database will guarantee that no other users are inserted between the SELECT and INSERT above. If two concurrent transactions try to insert a user with the username doofenshmirtz, the second one to complete will be rolled back, because the database knows that the second SELECT would be affected by the first INSERT.
Writing application code to retry insertions sounds far too messy to me. And if concurrent requests have to wait in line to add a user, that's a serious performance bottleneck - especially if the same request has return to the back of the "retry line" repeatedly.
Another alternative is to use database locking. Essentially, you say "nobody else can use this data until I've finished." There are various levels of locking - prohibiting reads or only writes, and locking a single row or an entire table.
While locking a row may not be a problem, the "no duplicate usernames" case would require locking the entire table for writes while your application code fetches and examines the results of the SELECT. Like serializable isolation, that creates a performance bottleneck. And if you ever use multiple locks, you have to be careful that you don't create a deadlock.
Constraints are a much more targeted tool. Constraints are rules you set in the database about what's valid for a row, a table, or the relationships between them. The database will reject any data that violates those rules.
We've already seen unique indexes (which are a kind of uniqueness constraint). They reliably solve the "no duplicates" problem.
But if you're using PostgreSQL, there are other kinds of constraints available.
For example, a check constraint could be used to say "an account balance may never be less than zero".
This rule applies to each row in the accounts table. If you don't see a need for this, you may have another race condition you haven't thought about.
Suppose a business makes a purchase of $20. Does your code read the current $100 balance, subtract $20, and UPDATE the balance to $80? If so, there's a race condition between the read and write. If two users make simultaneous $20 purchases, the second request to finish processing would set the balance to $80 when it should be $60.
With this approach, both purchases would subtract $20 correctly, and the CHECK constraint would ensure that if there's not enough funds in the account, the purchase fails.
Clearly, you can't allow conflicting reservations if you want happy customers. If Alice has rented a cabin from June 15-20, Bob must not be allowed to rent it from June 12-21 or any other overlapping period. But how do you guarantee this doesn't happen?
This says "if the property_id of an existing reservation is = to mine, and its inclusive () date_range overlaps mine (&&), my reservation is invalid. (Constraints are implemented via indexes, and this one is a gist index.) With this in place, we're guaranteed not to get conflicting reservations.
You can do even fancier things, like restricting this rule to reservations WHERE status = active, so that cancelled reservations don't prevent new ones.
If you want more detail about exclusion constraints, see my write-up here. But before we wrap up, let's talk about why you might not be using constraints already.
One objection to techniques like these is that they put "business logic" in the database.
Way back in 2005, David Heinemeier Hansson, creator of Ruby on Rails, wrote a post called "Choose a Single Layer of Cleverness", in which he dismissed "stored procedures and constraints" as destroying the "coherence" of a system.
I don't know what systems DHH was reacting to, but I can imagine being frustrated to find that my code only told half the story about how the application behaves, or that the logic in SQL wasn't deployed in lockstep with my application code.
I also don't know to what extent DHH has changed his mind. Rails appears to have supported unique indexes since version 1.0, and finally added foreign key support in Rails 4.2 in December 2014.
But Rails, like the frameworks that sprang up after it in other languages, still doesn't support database constraints nearly as well as it supports validations. This may be partly due to its early "database agnostic" approach; MySQL, for example, doesn't even have CHECK or EXCLUDE constraints. But even if you use supported constraints (like unique indexes and foreign keys), violations will result in an ActiveRecord exception; it's up to you to rescue it and provide meaningful user feedback.
Database constraints and/or stored procedures make the validation mechanisms database-dependent and can make testing and maintenance more difficult. However… database-level validations can safely handle some things (such as uniqueness in heavily-used tables) that can be difficult to implement otherwise…. [but] it's the opinion of the Rails team that model-level validations are the most appropriate in most circumstances.
They have a point. Clearly, validations for things like "products must have a price" or "email addresses must contain an '@'" can be safely handled by application code. And even when validations are insufficient to guarantee correctness, your application might not get heavy enough traffic for you to notice.
Empirically, even under worst-case workloads, these validations [for things like uniqueness] result in order-of-magnitude reductions in inconsistency. Under less pathological workloads, they may eliminate it. It is possible that, in fact, the degree of concurrency and data contention within Rails-backed applications simply does not lead to these concurrency races - that, in some sense, validations are "good enough" for many applications.
The reason validations help so much is this: if you're trying to reserve a username that was already taken, it's much more likely that it was claimed in a request before yours than in a request nearly simultaneous to yours. And if it was taken, say, yesterday, the validation will prevent your duplicate.
Fundamentally, your multiple web application instances do not coordinate their actions, so they can't prevent inconsistent data completely without leaning on the database via locks, constraints, or serializable transactions. And in my view, constraints are the most straightforward of these options: they don't require retry loops like serializable transactions do, they can't create deadlocks, and they have very little impact on performance.
Bottom line: I'm not happy with leaving data integrity to chance, especially when - as in the case of a doubly-booked property - an error would lead to very unhappy customers.
In my next post, I'll give some thoughts on when not to use constraints. I'll also suggest how to provide good a user and developer experience when you do use them.