HN Explorer

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

9 points by edw519 ↗ HN
A simple scenario:

At 1:00, Jane pulls a record into her browser: Lakeside High School, 123 Main St., 555-1111.

At 1:05, Fred pulls the same record into his browser.

At 1:06, Fred changes the address to 123 Oak St.

At 1:08, Mary changes the phone number to 555-2222.

Questions:

How to you handle the database locking?

- Pessimistic, lock the row at 1:00 and don't let Fred have it?

- Optimistic, let everyone take anything they want, but don't let Mary update?

- Column level locking?

- Special processing in you app?

How do you maintain state?

- Using sessions?

- Keeping a "before image" of the row? In the client? On the server? As a separate data base record?

I realize there is not one correct answer. Just curious how other YC'ers handle situations like this.

21 comments

[ 4.8 ms ] story [ 62.7 ms ] thread
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.

edw519
What do you mean by "faking" it? Example please.

Are you not too concerned about this because the real validation happens on the server anyway?

brlewis
Suppose two people are updating info about a T shirt: size=M color=green

Suppose Al is in charge of color and Betty is in charge of size. They both get the form at the same time.

Al sends oldsize=M newsize=M oldcolor=green newcolor=blue

Betty sends oldsize=M newsize=S oldcolor=green newcolor=green

The server ignores Al's oldsize/newsize and Betty's oldcolor/newcolor because the values are unchanged.

You end up with size=S and color=blue no matter who sends the update first.

By "faking it" I refer to the fact that the old values are user-supplied data. Validation of the new data is a separate issue. Someone could "fake" an old value. This merely means they would change a value that they were already authorized to change. Messing with the "old" values on the client side has no hack value. That's why I'm not at all concerned about it.

edw519
What if they changed the old value to the same value as the new value? Then your client side app would do nothing. Right?
brlewis
Correct. I am not concerned about someone deliberately hacking the system in such a way as to do nothing.
some
Using Javascript, the client could as well only send the fields that have changed.
wlievens
Exactly what I was thinking.
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.
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...

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

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.
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.

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.
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.

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.
jkush
I tend to like history-like approaches.