The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database locking in a web environment

Hello, I would like to provide some kind of Locking, in particular Row Based Locking for the EDIT pages of a web based database management interface.

My question is this:
What if the following scenario happens:
* TEh record gets locked when user X requests a Row for update.
* The user X loses is internet connection
* User Y tries to access the same Row - cant

So how do you deal with this?

Thanks.
CODING IN THE NAME
Saturday, October 20, 2007
 
 
Don't do locking.
Put timestamp of last edit in database and send it on edit form with other data. When your user posts changes, compare timestamp from edit form with value in record.
If they differ, ask user to reconcile data.
You can send back both new data from database and data that user entered and let him decide.
moronica
Saturday, October 20, 2007
 
 
CODING IN THE NAME,

Many databases come with a lock timeout value (I know for a fact that DB2 does this and I am sure that Oracle does...)  Not sure about MySQL or PostGRES but I am almost sure that they do as well.

So set the DB lock time out to the lowest value possible.  When the systems recognizes that the lock is older then X, it simply releases it.

Hope this helps!

Paul
Paul A. Fortin Send private email
Saturday, October 20, 2007
 
 
Don't use locks.  Use the techniques that Moronica was saying.

Or, you can adopt a policy of "last save wins".

The important part is that you're consistent so that the users know what the expected behavior is.
xampl Send private email
Saturday, October 20, 2007
 
 
This is a hard problem to solve, and I'm not convinced that there is an "ideal" solution. All have compromises and as xampl mentioned consistency is crucial.

The idea of passing back two (or more) versions and getting the user to reconcile breaks down pretty quickly for non-trivial cases. This is especially true for cases where changes have implications outside of the raw data itself and/or if the other user has done more work based on the (reasonable) assumption that their changes are going to stick.

Although locking has problems of its own, I prefer it, compared to the alternative. In particular, I tend to use pessimistic locking in non-trivial applications.

Users seem happy to tolerate the occasional call to IT support to get a record unlocked and appreciate the advantages that pessimistic locking gives them (and me too, as the developer).

I wouldn't like to have to explain how to sort out the mess that the "last update wins", etc scenarios could leave you in.

These scenarios seem fine in theory, for trivial applications, but how exactly do you present the data to the user when a conflict occurs? What would that UI look like? You'd have to train every user to do conflict resolution. I'm not convinced that this would ever work, especially as it'd be a hopefully rare occurrence.
Entries of Confusion Send private email
Sunday, October 21, 2007
 
 
For 99% of websites use optimistic locking.

The most common way of implementing this to to have each row contain a sequence number column. Pass this sequence number back to client code on a read request, and then back to the database on an update request. In the update, test that the sequence number on the row is the same as the sequence number from the client.

If they agree, update the row and increment the sequence number. If they disagree, somebody updated the row earlier, so reject this latest update and pass the new row information back to the client. So first person to do the update wins.

If you don't want (or are not allowed) to change the table schema, you can also implement optimistic locking using a binary checksum of the column values.

The only time I don't do this is in a very high-volume scenario, e.g. tick data. In this case, every update is actually implemented as a new insert.
Mark Pearce Send private email
Sunday, October 21, 2007
 
 
There is a place for pessimistic locking. You shouldn't have to use it on every table in your database, but sometimes the situation lends itself to pessimistic locking.

An easy database agnostic way of doing this is to define two tables in your database:

CREATE TABLE Session(
  Id int,
  UserId int,
  AppId int,
  Etc ...
);

CREATE TABLE RecordLock(
  SessionId int,
  TableName varchar(100),
  RowId int,
  StartTime datetime default( getdate() ),
  Etc ... (other info about the lock)
);

Basically, whenever a session needs a lock, it checks to see if theres already a lock in the RecordLock table. If not, it inserts one (the check and insert happen in a transaction). When the user is done with the record, the RecordLock row for that record is deleted...

Obviously, this only works if the table that you want to lock has a single Integer as it's primary key. Otherwise, you have to define a seperate RecordLock type table for the tables that you want to lock OR add the fields to the actual table.
Wayne Bloss Send private email
Sunday, October 21, 2007
 
 
Also, if you want automatic unlocking in the case when a user disconnects from the site without unlocking it, just check the StartTime of the RecordLock and if it's been > X minutes, just overwrite the lock. This basically expires the other user's lock and they should not be able to save the record until they acquire a new lock.
Wayne Bloss Send private email
Sunday, October 21, 2007
 
 
Hello and thanks for your comments.

The several solutions are interesting and I think I will go with the simpler one, adding an extra column and increment it's value.

Thanks.
CODING IN THE NAME
Sunday, October 21, 2007
 
 
CODING IN THE NAME: "The several solutions are interesting and I think I will go with the simpler one, adding an extra column and increment it's value."

Although pessimistic locking has some disadvantages, I prefer them, as they are simple to implement and simple to explain. You can also expire them if you wish, as suggested above.

The main advantage is that users are never left in a position where their work must be abandoned. In general, I have found that users will tolerate having to phone support to get the occasional record unlocked, but they will not tolerate losing their work.

Explanations from IT about how optimistic locking works and how it is a great thing don't seem convincing to users and this may translate into a general bad feeling about the new system. That is, even though it is doing what it was designed to do, users may think it is unreliable, as it loses their work. After all, if their work gets lost due to a bug, or system outage, how is that different to lost work due to locking?

I still find the argument that you can present both versions to the user and get them to do the conflict resolution themselves to be a massive red herring. I do not believe that would ever be successful in non-trivial cases.
Entries of Confusion Send private email
Monday, October 22, 2007
 
 
Troels Knak-Nielsen Send private email
Monday, October 22, 2007
 
 
>> That is, even though it is doing what it was designed to do, users may think it is unreliable, as it loses their work. After all, if their work gets lost due to a bug, or system outage, how is that different to lost work due to locking? <<

Optimistic locking is implemented in the database tier, and is completely neutral about dropping anybody's work. If you want to keep the work of a losing update, that's reltively simple to implement client-side. You just bring back the new sequence number and then inform the end-user that the information she's trying to update has changed underneath her. All she has to do is press "Okay" again if she still wants to update - two clicks instead of one.

>> I still find the argument that you can present both versions to the user and get them to do the conflict resolution themselves to be a massive red herring. <<

Agreed.
Mark Pearce Send private email
Monday, October 22, 2007
 
 
The lock table approach won't work due to one session not seeing the transaction of another. (unless your RDBMS allows dirty reads, ugh)
JimK Send private email
Monday, October 22, 2007
 
 
Mark Pearce: "You just bring back the new sequence number and then inform the end-user that the information she's trying to update has changed underneath her. All she has to do is press "Okay" again if she still wants to update - two clicks instead of one."

Mark, that is fine, but for non-trivial cases, how can you expect the user to make an informed decision, especially if the data change has down-stream implications?

Or, what if another user changes data in a related table, an action based upon the value in the current table? If you save the data now, the first transaction becomes meaningless, although there is no way to undo it, or even know there is a problem.

My bet is that many users will just blindly click "Okay", with little or no regard to the implications of their "decision".

This could lead to (logical) inconsistencies in the data, which would be difficult to detect and eradicate.
Entries of Confusion Send private email
Monday, October 22, 2007
 
 
Entries of Confusion: I understand what you are saying regarding user perception, but this work is not for commercial usages, its a school project. Most of my colleagues (whose work I followed) aren't even doing any kind of Locking. I'm just going for a better grade and more experience.
CODING IN THE NAME
Monday, October 22, 2007
 
 
BTW I'm working with MySql (maybe someone has specific advice).

Does anyone know if it's possible to know if a Table or Row is Locked, in Mysql?

With that info one could do it at load time and send a message to the user: The table is being updated. Try again in a few minutes.

Thanks.
CODING IN THE NAME
Monday, October 22, 2007
 
 
==>Basically, whenever a session needs a lock, it checks to see if theres already a lock in the RecordLock table. If not, it inserts one (the check and insert happen in a transaction). When the user is done with the record, the RecordLock row for that record is deleted...

And then you get to figure out a nice clean way to clean up any left-over-orphaned-locks. (HINT: There ain't one -- it *can* be done, but it ain't clean-and-easy)

Obvious example: User grabs a lock, starts editing. Goes to lunch. Loses connection. Forgets about locked record for a week. Nobody can edit record. Q.E.D.

BTDT. What we ended up doing was firing up a regularly scheduled job to find and remove all locks older than X (for sufficient values of "X").

I'd seriously be considering a different model on this one. The whole "I've got this record, it's mine, nobody else can have it 'til I'm done" model -- you'll regret it in the end. I know I do.
Sgt.Sausage
Monday, October 22, 2007
 
 
CODING IN THE NAME

I use MySQL 5 with Delphi for my apps... I use a TIMESTAMP type column in all of my tables called RowVersion. All my stored procs that select data return this, and I pass it back via my update procedures and check this value against the current value of the RowVersion.

For business apps, I find optimistic locking works best for general concurrency control. Very few collisions occur.

"Error saving changes - This [object] was updated by [user] at [timestamp]."

"Batch" type procedures may require a different locking scheme.
Jason T
Monday, October 22, 2007
 
 
"its a school project. "

Then just use the ideas presented that have you compare against the value of a of sequence number column. If it has been changed since you retrieve the row show an error and stop. Most commercial products just do this anyway. Simple to program and gives you "real world" experience. Don't waste any more time than that to get a better grade. No one will appreciate it and no one will test out concurency on your app anyway.
anon
Monday, October 22, 2007
 
 
>> Mark, that is fine, but for non-trivial cases, how can you expect the user to make an informed decision, especially if the data change has down-stream implications? <<

I don't understand what problems the user is going to have in this context. For example, say she updated a salary number from £80K to £100K, and she's then told that before she did so,somebody else updated that salary number to £90K. This is a perfectly normal business situation, and shouldn't tax her brain too much.

>> Or, what if another user changes data in a related table, an action based upon the value in the current table? If you save the data now, the first transaction becomes meaningless, although there is no way to undo it, or even know there is a problem. <<

I don't understand what this has to do with locking, because this type of situation can arise at any time. The database should prevent any change that violates business rules. For example, a database update trigger works very well for enforcing this rule.
Mark Pearce Send private email
Monday, October 22, 2007
 
 
"For example, say she updated a salary number from £80K to £100K, and she's then told that before she did so,somebody else updated that salary number to £90K. "

But that's just it isn't it... are you really going to TELL her "somebody else updated the salary number to £90K"? Or are you just going to show some generic "concurrency violation" error message like most commercial apps do? Are you showing all the columns that have actually been changed? That isn't actually very easy to do.

Do they even know what table/columns they are editing? Does the field on the screen labeled "Salary" actually correspond to an aptly named column in the DB? If not, how are you mapping columns to UI fields? You can surely see where this is all starting to fall apart...
anon
Monday, October 22, 2007
 
 
>> But that's just it isn't it... are you really going to TELL her "somebody else updated the salary number to £90K"? Or are you just going to show some generic "concurrency violation" error message like most commercial apps do? <<

I show her all the new row information that was retrieved in the course of the failed update. Then I display a message saying something like "Sorry - somebody else changed Mark Pearce's information before you did. Please review the updated information and click 'Okay' again if you still want to change it."

>> Do they even know what table/columns they are editing? <<

No, why should she care? She wants to update some data, and needs to review the latest version of that data before she can proceed. There's no need to confuse her with technical gobbledygook.
Mark Pearce Send private email
Tuesday, October 23, 2007
 
 
@Mark Pearce
Just to change your scenario slightly. Alice is busy changing Mark's salary from £80k to £90k, but has to take a phone call half way through the transaction.

Meanwhile, Betty (the boss's PA) is told that Mark has been exceptional and should get a big raise and be put onto the "Executive Programme" and get additional benefits, reserved for top-level employees. Betty goes ahead and increases Mark's salary to £120k and attaches a package of benefits.

Now, Alice has finished talking to her boyfriend about their planned trip and returns to the screen. She clicks "save" and a message pops up about another user who she doesn't know modifying the record. She has a choice, accept the modified record and lose her changes, or keep her changes. Which will she choose?

The fact that you give a choice means you have to expect that sometimes the incorrect option will be selected. Clearly, in this case, Alice should abandon her work and allow Betty's change to stand, but how do you explain that to Alice in a meaningful way? She may not know who Betty is, she may not know about the executive benefit scheme.

What I mean is, that in at least some cases Alice will blindly click save again to overwrite Betty's changes. This could lead to data inconsistency, as there may be a business rule, that Alice isn't aware of, that says that nobody on less than £100k gets the executive benefits.

Of course, you can say that business rules would be checked on the 2nd save attempt and violated, so once again we present a message to Alice asking what to do. Once again, we have the opportunity for an error, and we are now wasting even more time.

What if Alice insists on her changes being kept, the system would have to remove the executive benefits, to prevent the business rule being violated. Now the system doesn't reflect what the management of the company want. Some time later, the boss says to Mark "How are those new benefits working out for you?". Mark replies "What benefits?".

Even with a trivial scenario like this, optimistic locking seems like it can get into trouble. Compare the pessimistic locking scenario:

Alice is busy changing Mark's salary from £80k to £90k. Meanwhile, Betty opens Mark's record, and the system immediately informs her that it is locked by Alice. She knows she can just come back to this later.

Alice finishes her update and as the record is unlocked, the system sends a notification to Betty that Mark's record is now available. Betty goes in and after briefly reviewing Alice's change, goes ahead and completes her change.

I prefer the latter scenario for a number of reasons, and I'm pretty convinced that my business users prefer it too.
Entries of Confusion Send private email
Tuesday, October 23, 2007
 
 
>> The fact that you give a choice means you have to expect that sometimes the incorrect option will be selected. <<

Ensuring that the correct choice is made every time can't be done with any form of locking. It's the app's job to ensure that the end-user bases her choice on the latest information, and to enforce the appropriate business rules.

>> Clearly, in this case, Alice should abandon her work and allow Betty's change to stand, but how do you explain that to Alice in a meaningful way? <<

In your specific scenario, the correct choice is probably to allow Betty's change to stand. But let's change the scenario slightly. Betty received an email from an HR person telling her to make the change, but in fact that email was based on outdated information. So now it's Betty that's made an incorrect change, and Alice should overwrite Betty's change.

There are many possibilities, and you (as the app developer) can never know which is the correct one.

>> Of course, you can say that business rules would be checked on the 2nd save attempt and violated, so once again we present a message to Alice asking what to do. <<

Absolutely not. We present a message saying Alice cannot do the update because it violates a business rule. There is no opportunity for an error, because we don't give Alice a choice.

>> I prefer the latter scenario for a number of reasons, and I'm pretty convinced that my business users prefer it too. <<

I can see that that <shrug>. My case is exactly the opposite, and plainly neither of us has convinced the other.
Mark Pearce Send private email
Tuesday, October 23, 2007
 
 
Hi Mark,

Yeah, we will agree to differ on this one.

I think we both make good arguments, but accept that neither scheme is perfect.

Best,

EoC.
Entries of Confusion Send private email
Tuesday, October 23, 2007
 
 
>> Mark, that is fine, but for non-trivial cases, how can you expect the user to make an informed decision, especially if the data change has down-stream implications? <<

"I don't understand what problems the user is going to have in this context. For example, say she updated a salary number from £80K to £100K, and she's then told that before she did so,somebody else updated that salary number to £90K. This is a perfectly normal business situation, and shouldn't tax her brain too much."

Why would she be told what the change was?  Both values would have to be stored for that to be possible.

What if the other change to be overridden/overwritten is, say, the employee's address?

Sincerely,

Gene Wirchenko
Gene Wirchenko Send private email
Tuesday, October 23, 2007
 
 
>> Why would she be told what the change was?  Both values would have to be stored for that to be possible. <<

I normally show only the new number (£90K) - I don't show the old number (£80K).

But I do keep track of the new update (£100K) that she was trying to apply, so that she can re-apply it if necessary.
Mark Pearce Send private email
Wednesday, October 24, 2007
 
 
"And then you get to figure out a nice clean way to clean up any left-over-orphaned-locks. (HINT: There ain't one -- it *can* be done, but it ain't clean-and-easy)

Obvious example: User grabs a lock, starts editing. Goes to lunch. Loses connection. Forgets about locked record for a week. Nobody can edit record. Q.E.D."

Sgt.Sausage, there's an easier way than having a scheduled job to remove errant locks...

Every time you check to see if you can lock a table, if there's already a lock, you check to see what time that lock started...if it's past 15 minutes, you overwrite that lock with your new one.

Then, when the other user comes back from lunch and tries to save his work, it fails saying that his lock expired.
Wayne Bloss Send private email
Friday, October 26, 2007
 
 
>> Then, when the other user comes back from lunch and tries to save his work, it fails saying that his lock expired. <<

How does this differ in substance from an optimistic lock telling the user that her update has failed because somebody updated earlier?

In fact, it's probably worse. Most end-users have no concept of "contention locks" on their data.

BTW, you can almost guarantee that in most systems where view and update is done from the same screen, the time-out from a pessimistic lock will happen much more often than people clashing over concurrent updates.

This is similar to the argument over source control. Older source control tools take a pessimistic view: you have to check-out a file, and that creates a pessimistic lock. Newer source control tools take an optimistic view: no check-out required, and concurrent updates have to be resolved with a merge or overwrite.

And what's evolved over time in source control is an overwhelming move towards the optimistic model.
Mark Pearce Send private email
Friday, October 26, 2007
 
 
"And what's evolved over time in source control is an overwhelming move towards the optimistic model."

Perhaps this is because the people using source control are better equipped to deal with these issues.
Entries of Confusion Send private email
Saturday, October 27, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz