Database Reference
In-Depth Information
Optimistic locking with conditional updates
As the MyStatus service becomes more popular, we're likely to see organizations beginning
to create accounts that are managed by multiple people. Once we have multiple people ac-
cessing the same resource at the same time, we need to think about what might go wrong
when different people try to make conflicting changes to the same piece of data.
Let's create a new account on behalf of an organization called HappyCorp. We'll just start
with some basic information:
INSERT INTO "users"
("username", "email", "encrypted_password")
VALUES
('happycorp', 'media@happycorp.com',
0x368200fa910c16cc644f3512e63b541c85fa2a3c)
IF NOT EXISTS;
We use a conditional update, of course, to ensure that we're not overwriting another row
with the same primary key.
Now, let's imagine that two members of the HappyCorp social media team decide to update
the location listed in the company's profile on MyStatus at the same time. One of them
elects to enter New York, the location of HappyCorp's corporate offices, and the other
thinks it would make more sense to list Palto Alto, where the company's main R&D cam-
pus is located. Unfortunately, the two employees decide to make this update at the exact
same time; so each one sees a blank location field, types a value in, and tries to save it
without realizing that they are stomping on each other's work.
A popular solution to this sort of problem is known as
optimistic locking
, which uses a
version column to ensure that two simultaneous updates will not conflict with each other.
Each time a record is displayed to a user for the purpose of updating it, the application
keeps track of what version was displayed (on the web, this might be done using a hidden
form field). Changes are saved to the database using a
conditional update
, ensuring that
they're only applied if the
current
version in the database is the same as the version that
was initially displayed to the user. As part of the operation, we also update the version it-
self.
To see how this works, let's first add a
version
column to the
users
table. We could do
this with a simple integer, incrementing it each time we update a row; however, given Cas-
