

Ask YC: How do you handle locking in your app? - edw519

A simple scenario:<p>At 1:00, Jane pulls a record into her browser: Lakeside High School, 123 Main St., 555-1111.<p>At 1:05, Fred pulls the same record into his browser.<p>At 1:06, Fred changes the address to 123 Oak St.<p>At 1:08, Mary changes the phone number to 555-2222.<p>Questions:<p>How to you handle the database locking?<p>- Pessimistic, lock the row at 1:00 and don't let Fred have it?<p>- Optimistic, let everyone take anything they want, but don't let Mary update?<p>- Column level locking?<p>- Special processing in you app?<p>How do you maintain state?<p>- Using sessions?<p>- Keeping a "before image" of the row?  In the client?  On the server?  As a separate data base record?<p>I realize there is not one correct answer.  Just curious how other YC'ers handle situations like this.
======
fauigerzigerk
I usually use optimistic row level locking for typical structured data in a
3NF data model. If there is so much contention for a particular record that
pessimistic or column level locking is necessary there might be a problem with
the design of the data model or workflow. There are of course rare situations
where pessimistic locking is justified. The canonical one is multi hop flight
reservations.

Handling semi structured data (documents of some sort) is way trickier and
depends a great deal on the particular data format and patterns of use.
Merging is desirable but very hard to achieve in many cases.

One other thing that is worth thinking about is how the locking strategy
scales. The most popular idea these days is to seperate read and write
operations so all writes go to one DBMS server and the reads are distributed
to replicas. That's ugly to integrate after the fact, so if you expect your
app to grow fast you should probably design it like that from the start.

------
chaostheory
someone can correct me but most of the time optimistic locking is ideal (since
for most apps, the same data typically has a low chance of being updated by
different people). Anyways we use that in addition to transactions

pessimistic locking in general is really tricky

[http://www.ibm.com/developerworks/websphere/techjournal/0603...](http://www.ibm.com/developerworks/websphere/techjournal/0603_ilechko/0603_ilechko.html)

I forgot to add that if you're using a good framework, it typically already
has a built-in mechanism for optimistic locking

------
mynameishere
Here's your answer:

[http://worsethanfailure.com/Articles/I_Think_I_0x27_ll_Call_...](http://worsethanfailure.com/Articles/I_Think_I_0x27_ll_Call_Them__0x26_quot_0x3b_Transactions_0x26_quot_0x3b_.aspx)

------
izak30
Currently I compare the changed values, and if mary and fred have the same
permissions, I let mary see the changes that fred made that she did not, and
say 'would you just like to change this row (phone)'. I'm working with a small
data set, and also I keep history, but I change the value of a hidden form
element (mine is a web app) when <input name="phone" onChange="THIS()"> THIS
is invoked, for example. and if mary never changed address, but the value in
address is different than the value that she passes in, it gives an error
condition and lets Mary figure it out.

------
jey
In this particular case, you could store a version in the record. When Mary
submits her edit, your backend will note that Mary submitted an edit to
revision "5" but the current revision is "6", and ask Mary to confirm her
changes. You'd just use a database transaction to atomically increment the
version number when a new revision is created. I think this is analogous to
what Wikipedia does.

------
jojoleflaire
I know databases aren't fashionable around here, but this is _exactly_ what
they are for. Seriously.

~~~
paulgb
How does a database solve this? They provide a way to apply a lock, but it
comes down to the programmer to choose the type of locking to use.

~~~
jojoleflaire
I guess I read a few things into the question. My experience is that a
"record" is more often than not a series of rows in different tables, and the
tricky question in these scenarios is making sure that updates either work or
don't work in their entirety. This is what I was meant by the "this is what
databases are for" line.

It is practically impossible to maintain a useful, enforceable lock on a
record in a web application, given the lack of a persistent connection between
the client (browser) and the back end. So you are usually left with two
choices:

1\. Last one in wins: given that the two users presumably have good reasons
for modifying the record in question, let them figure it out if there is a
conflict. As long as updates are atomic, consistent, blah, blah this works
best.

2\. Versioning: Every record has a version number associated with it and the
database rejects updates with a version != to what is in there.

The real trick is to set yourself up so that you have a single-writer for any
given piece of data in the common case and defer whatever locking is necessary
optimistically to the database.

~~~
edw519
"lack of a persistent connection"

IMO, this is the heart of the problem because it renders pessimistic (what
we've used in the enterprise for years) virtually useless.

Fortunately, there are techniques to get around them. Several interesting
lines of thought are presented in this thread. Thanks to all who posted them.
You gave me quite a bit to think about.

------
mdkersey
I try to establish

1) when Mary got the record,

2) whether she got the same record as Jane and Fred, and finally

3) whether Mary = Jane or not!8-))

[Hint: the example was not properly stated.]

~~~
edw519
Oops. Mary = Jane. Sorry. I guess I should have used pessimistic locking on
brain synapse 7E8B32.

------
digito
Why would you keep a before image on the client? Isn't this dangerous?

~~~
brlewis
The client sends the before/after values, and you only update the ones that
have changed. If people are changing different fields there's no conflict.

Yes, the client could fake it, but such faking would not open up new
capabilities that the client didn't already have.

~~~
some
Using Javascript, the client could as well only send the fields that have
changed.

~~~
brlewis
That's true. However, depending on the needs of the application, you might
want to take things a step farther and engage the client in a dialogue about
values changed by others. For that purpose it helps to tell the server what
the values were when the client loaded the form.

------
jkush
I tend to like history-like approaches.

