Record locking

[Murray Collingwood <mu...@focus-computing.com.au>]

Hi all

As long as I have been on this list I have never heard anybody discuss record locking.  
The scenario is very simple:

1. User A clicks an "edit" option to edit a record.
2. While user A is editing the record user B clicks the same "edit" option
3. Both users are now editing the same record (or so they think)
4. User A clicks "save" and the record is updated and displays the changes made by 
user A - user A is happy
5. User B clicks "save" and these changes overwirte the changes made by user A, 
however user B doesn't know this and the changes made by user B appear.

Sometime later user A realises that the changes made have disappeared and gets 
angry and begins throwing large objects around the room and making lots of loudish 
gutteral noises in an attempt to disperse inner emotional anxiety.

Well, you get the picture.

Q. Does Struts assume that the developer will code in the appropriate record locks in 
the database?

Q. Does anybody actually do this?

Q. How?  What type of record locking do you use?

TIA
mc

FOCUS Computing - web design
Mob: 0415 24 26 24
murray@focus-computing.com.au
http://www.focus-computing.com.au




-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.12.6/152 - Release Date: 31/10/2005


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org

Re: Record locking

["Frank W. Zammetti" <fz...@omnytex.com>]

I couldn't agree more with Wendy.  But, as with more things, it comes 
down to your application.

I work at a large financial institution, so I deal with these types of 
situations quite a bit.  It all depends on what is being edited.

If we are talking about something like, say, a fund load fee reference 
table, it might be appropriate to use an pessimistic locking mechanism 
where once one user obtains a lock on a record, no one else can edit it 
until their changes have been committed (whether you allow reads or not 
is still a question).  What I've done in these instances is (a) tied the 
unlocking code to a SessionListener so that if the user walks away, I 
unlock any record they had locked when their session expires (typically 
no more than 20 minutes).  Their changes are lost of course, and (b) 
clear all record locks at app startup, in case it goes down and is 
restarted (which is more interesting than you might think since we're 
hosted in a clustered environment, but that's a whole other ball of wax!)

If it's more of a transactional system, or a system where a record can't 
be locked for any real length of time, i.e., maybe a customer record 
that can't truly be locked for more than a few seconds, there are plenty 
of games to be played, but they all generally come down to some sort of 
timestamp and logic to work with it.  I also generally have some sort of 
background/periodic process to check for stale locks and get rid of 
them.  The details of this are usually app-specific and so there 
probably isn't any general-purpose answer to give.

As Wendy said though, locking is usually best handled in the database 
itself.  The one big exception is something like a wizard interface 
where the lock has to persist across multiple requests for a given 
session.  If you don't have such a requirement though, if a record is a 
per-request thing, I for one would definitely council against doing 
anything but locking in the RDBMS itself, and I would generally use a 
pessimistic locking scheme, with the understanding that this directly 
impacts scalability.  Again, it comes down to what the app is and has to 
do though, that might be acceptable, or it might not be.

Frank

Wendy Smoak wrote:
> On 11/1/05, Murray Collingwood <mu...@focus-computing.com.au> wrote:
> 
> 
>>The scenario is very simple:
>>
>>1. User A clicks an "edit" option to edit a record.
>>2. While user A is editing the record user B clicks the same "edit" option
>>3. Both users are now editing the same record (or so they think)
>>4. User A clicks "save" and the record is updated and displays the changes
>>made by
>>user A - user A is happy
>>5. User B clicks "save" and these changes overwirte the changes made by
>>user A,
>>however user B doesn't know this and the changes made by user B appear.
> 
> 
> 
> Trying to hold real database-level record locks from a web interface is just
> asking for trouble. The user wanders away, the connection drops...
> meanwhile, no one else can get to that record.
> 
> I'm in a situation where I have to work around a telnet app that thinks it
> is the only thing accessing the database. I calculate a checksum when I read
> a record, and then check again before writing the record back. If the
> checksum has changed, then someone else touched the record (most likely from
> the telnet interface) and the web-user loses.
> 
> This happens in the data access layer, Struts doesn't know anything about it
> other than being configured to handle a RecordModifiedException.
> 
> --
> Wendy
> 

-- 
Frank W. Zammetti
Founder and Chief Software Architect
Omnytex Technologies
http://www.omnytex.com
AIM: fzammetti
Yahoo: fzammetti
MSN: fzammetti@hotmail.com

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org

Re: Record locking

[Wendy Smoak <ws...@gmail.com>]

On 11/1/05, Murray Collingwood <mu...@focus-computing.com.au> wrote:

> The scenario is very simple:
>
> 1. User A clicks an "edit" option to edit a record.
> 2. While user A is editing the record user B clicks the same "edit" option
> 3. Both users are now editing the same record (or so they think)
> 4. User A clicks "save" and the record is updated and displays the changes
> made by
> user A - user A is happy
> 5. User B clicks "save" and these changes overwirte the changes made by
> user A,
> however user B doesn't know this and the changes made by user B appear.


Trying to hold real database-level record locks from a web interface is just
asking for trouble. The user wanders away, the connection drops...
meanwhile, no one else can get to that record.

I'm in a situation where I have to work around a telnet app that thinks it
is the only thing accessing the database. I calculate a checksum when I read
a record, and then check again before writing the record back. If the
checksum has changed, then someone else touched the record (most likely from
the telnet interface) and the web-user loses.

This happens in the data access layer, Struts doesn't know anything about it
other than being configured to handle a RecordModifiedException.

--
Wendy

Re: Record locking

[Larry Meadors <lm...@apache.org>]

I agree with Jason's assertion 100%.

Your best bet on the web is record versioning.

Larry


On 11/1/05, Jason King <jh...@airmail.net> wrote:
> This isn't a struts question so much as a persistence layer question.
> There are several approaches you can use depending on your data and your
> users.
> For infrequently updated tables you can hope for the best and probably
> be safe.
> If you're in an EJB world the EBJ layer takes care of that (I think).
> If you're using JDBC you can:
> a) implement a last_updated column and a trigger to populate it in your
> tables (assuming the db supports it) and only do an update if the
> current last_updated matches the one with the new values (that means
> user b errors out).  He won't be thrilled, but you'll have no lost updates.
> b)  Save old values (possibly in a session-level object) and only
> perform the update if all the current row values match the saved ones
> from the session.  This works for trigger-impaired databases, but also
> causes User B to get an error message.
> Using Ibatis a similar approach to raw JDBC should work.  Never having
> used Hibernate I can't tell you if it has any provisions to avoid the
> "lost update" problem you described.  I can fairly confidently state
> that you won't be able to solve the problem via record-locking.  Given
> the largely stateless nature of web applications and the likelyhood of
> connections dropping, you'd be setting yourself up for hell if you tried
> to maintain a database transaction across multiple submits.
> Murray Collingwood wrote:
>
> >Hi all
> >
> >As long as I have been on this list I have never heard anybody discuss record locking.
> >The scenario is very simple:
> >
> >1. User A clicks an "edit" option to edit a record.
> >2. While user A is editing the record user B clicks the same "edit" option
> >3. Both users are now editing the same record (or so they think)
> >4. User A clicks "save" and the record is updated and displays the changes made by
> >user A - user A is happy
> >5. User B clicks "save" and these changes overwirte the changes made by user A,
> >however user B doesn't know this and the changes made by user B appear.
> >
> >Sometime later user A realises that the changes made have disappeared and gets
> >angry and begins throwing large objects around the room and making lots of loudish
> >gutteral noises in an attempt to disperse inner emotional anxiety.
> >
> >Well, you get the picture.
> >
> >Q. Does Struts assume that the developer will code in the appropriate record locks in
> >the database?
> >
> >Q. Does anybody actually do this?
> >
> >Q. How?  What type of record locking do you use?
> >
> >TIA
> >mc
> >
> >FOCUS Computing - web design
> >Mob: 0415 24 26 24
> >murray@focus-computing.com.au
> >http://www.focus-computing.com.au
> >
> >
> >
> >
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> For additional commands, e-mail: user-help@struts.apache.org
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org

Re: Record locking

[Jason King <jh...@airmail.net>]

This isn't a struts question so much as a persistence layer question.  
There are several approaches you can use depending on your data and your 
users.
For infrequently updated tables you can hope for the best and probably 
be safe. 
If you're in an EJB world the EBJ layer takes care of that (I think).
If you're using JDBC you can:
a) implement a last_updated column and a trigger to populate it in your 
tables (assuming the db supports it) and only do an update if the 
current last_updated matches the one with the new values (that means 
user b errors out).  He won't be thrilled, but you'll have no lost updates.
b)  Save old values (possibly in a session-level object) and only 
perform the update if all the current row values match the saved ones 
from the session.  This works for trigger-impaired databases, but also 
causes User B to get an error message.
Using Ibatis a similar approach to raw JDBC should work.  Never having 
used Hibernate I can't tell you if it has any provisions to avoid the 
"lost update" problem you described.  I can fairly confidently state 
that you won't be able to solve the problem via record-locking.  Given 
the largely stateless nature of web applications and the likelyhood of 
connections dropping, you'd be setting yourself up for hell if you tried 
to maintain a database transaction across multiple submits.
Murray Collingwood wrote:

>Hi all
>
>As long as I have been on this list I have never heard anybody discuss record locking.  
>The scenario is very simple:
>
>1. User A clicks an "edit" option to edit a record.
>2. While user A is editing the record user B clicks the same "edit" option
>3. Both users are now editing the same record (or so they think)
>4. User A clicks "save" and the record is updated and displays the changes made by 
>user A - user A is happy
>5. User B clicks "save" and these changes overwirte the changes made by user A, 
>however user B doesn't know this and the changes made by user B appear.
>
>Sometime later user A realises that the changes made have disappeared and gets 
>angry and begins throwing large objects around the room and making lots of loudish 
>gutteral noises in an attempt to disperse inner emotional anxiety.
>
>Well, you get the picture.
>
>Q. Does Struts assume that the developer will code in the appropriate record locks in 
>the database?
>
>Q. Does anybody actually do this?
>
>Q. How?  What type of record locking do you use?
>
>TIA
>mc
>
>FOCUS Computing - web design
>Mob: 0415 24 26 24
>murray@focus-computing.com.au
>http://www.focus-computing.com.au
>
>
>
>
>  
>


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org

Re: Record locking

[Hubert Rabago <hr...@gmail.com>]

For web applications, I tend to think that the more appropriate
approach is (what I know to be called) optimistic locking.  When a
user edits a record, I keep track of the update timestamp (or even
better, an update sequence number) of the record selected.  This value
gets sent back when the user attempts to save the changes.  Before
persisting the changes to the DB, I check if the timestamp (or seqnbr)
still matches the one on the DB.  If so, then no one else has modified
the data and the update can proceed.  If not, then it's up to you -
you can show the user the new record values and the changes that s/he
made, or ask that the user re-enter changes, etc.

In your example, User B will be shown an error message saying the
record has changed since he began and can start over or go ahead and
proceed with his changes anyway.

Hubert

On 11/1/05, Murray Collingwood <mu...@focus-computing.com.au> wrote:
> Hi all
>
> As long as I have been on this list I have never heard anybody discuss record locking.
> The scenario is very simple:
>
> 1. User A clicks an "edit" option to edit a record.
> 2. While user A is editing the record user B clicks the same "edit" option
> 3. Both users are now editing the same record (or so they think)
> 4. User A clicks "save" and the record is updated and displays the changes made by
> user A - user A is happy
> 5. User B clicks "save" and these changes overwirte the changes made by user A,
> however user B doesn't know this and the changes made by user B appear.
>
> Sometime later user A realises that the changes made have disappeared and gets
> angry and begins throwing large objects around the room and making lots of loudish
> gutteral noises in an attempt to disperse inner emotional anxiety.
>
> Well, you get the picture.
>
> Q. Does Struts assume that the developer will code in the appropriate record locks in
> the database?
>
> Q. Does anybody actually do this?
>
> Q. How?  What type of record locking do you use?
>
> TIA
> mc
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org