If you don't have tight performance requirement, use
TRANSACTION_SERIALIZABLE, that is safest way,
otherwise, look at REPEATABLE_READ or READ_COMMITTED.
For details, look at any book/article talking about
J2EE transactions.

--- "Gupta, Rajan" <Rajan.Gupta@(protected):

> And what do u suggest that isolation level might be,
> which will provide high
> levels concurrency & prevent lock escalation in the
> database.
>
> -----Original Message-----
> From: An interest list for Sun Java Center J2EE
> Pattern Catalog
> [mailto:J2EEPATTERNS-INTEREST@(protected)
> Of Yongqin Xu
> Sent: Wednesday, September 29, 2004 10:56 AM
> To: J2EEPATTERNS-INTEREST@(protected)
> Subject: Re: How to prevent a user from deleting a
> record while another
> is edi ting it
>
>
> As J2EE promised, why we still have to deal with
> low level transaction synchornization issues like
> this. The container with CMP will take care of this
> through transaction isolation levels (those static
> final integer in java.sql.Connection). Do not
> reinvent
> the wheel and waste your time here.
>
> --- "Gupta, Rajan" <Rajan.Gupta@(protected):
>
> > The solution described by Nicholas is what I have
> > implemented. I have a
> > beingLockedBy field which holds the userid of the
> > record who is currently
> > editing the record. My question is what in CMP
> will
> > prevent two users
> > updating the record at the same time? Let us
> assume
> > that in a cluster
> > environment, two users access the record at the
> same
> > time, what type of
> > locking one can use to prevent simultaneous
> update.
> > EJB 2.0 does not have
> > FOR UPDATE (besides this is evil anyway).
> >
> > -----Original Message-----
> > From: An interest list for Sun Java Center J2EE
> > Pattern Catalog
> > [mailto:J2EEPATTERNS-INTEREST@(protected)
> Behalf
> > Of Nicholas Wee
> > Sent: Tuesday, September 28, 2004 9:55 PM
> > To: J2EEPATTERNS-INTEREST@(protected)
> > Subject: Re: How to prevent a user from deleting a
> > record while another is
> > edi ting it
> >
> >
> > Hello,
> >
> > I agree that the requirement is rather strange,
> i.e.
> > to deny delete if
> > someone is modifying, in a multi-tiered system,
> but
> > totally permissible
> > given what I seen users ask for :).
> >
> > In a multi-tiered system, our normal practice in
> > handling a race condition
> > is to use a version field (of type long integer)
> to
> > control concurrent
> > updates. So, an additional WHERE condition is
> used
> > to check that the record
> > in buffer (such as in HTTP Session) that was
> > retrieved earlier for display
> > to the user for the update (or delete) transaction
> > has the same version
> > number as in the database. Otherwise, the update
> > (or delete) transaction is
> > denied with an exception. And in that same SQL
> > transaction, the version
> > number is also incremented. E.g UPDATE TableA SET
> > A='XYZ', SET VER_NUM =
> > VER_NUM + 1 WHERE VER_NUM = BUF_VER_NUM;.
> >
> > Also, in our practice, records in the database are
> > never really
> > deleted...but rather are marked for deletion using
> > an additional field, e.g.
> > IS_DELETED, that acts as a flag. Then using a
> > housekeeping job, these
> > marked records are archieved out of the system.
> >
> > But I digress, and coming back to this
> > requirement...a solution may be to
> > add an addition field, e.g. IS_MODIFYING, to flag
> > that a record has been
> > retrieved for update. This is set on, and the
> > version number is incremented
> > (in the same transaction). Then any subsequent
> > retrieval, including those
> > for delete will know that a record has been marked
> > for editing or
> > modification. Then, when the update completes,
> this
> > flag can be set off.
> > However, I think you will need a housekeeping job
> to
> > clear flags
> > automatically at the end of a business day (for
> > example), and an
> > administrative transaction to manually clear such
> > flags for 'urgent' cases.
> > These are to handle situations where the browser
> > performing the
> > modifications is terminated for whatever reasons.
> >
> > Just my suggestion.
> >
> > Regards
> > Nicholas
> >
> > Gerard Toonstra <toonstra@(protected):
> >
> > Although the timestamp approach is somehow valid,
> I
> > think the requirement
> > is very strange. The requirement to me re-reads
> as:
> >
> > "Normally we would allow a user to delete the
> data,
> > *except* when someone
> > else is just about to modify it."
> >
> > That doesn't really make sense to me. What makes
> > more sense to me is:
> >
> > "When you are editing data that someone else has
> > deleted in the meantime,
> > the application will return an error."
> >
> >
> > How many seconds should elapse after the timestamp
> > before the record
> > *may* be deleted by someone else? It is possible
> > that someone starts to
> > modify
> > the data, then goes on doing something completely
> > different. Person B then
> > cannot delete the data until X minutes expire and
> > this original requirement
> > thereby causes more frustration then it does good.
> > Does this time need to be
> > configurable as well??? ..... ->>
> >
> > The cause is that something recording the start of
> a
> > particular transaction
> > is
> > not as volatile as the transaction itself and is
> > fully detached from that
> > transaction.
> >
> >
> > I would solve this requirement differently, but it
> > requires more effort:
> >
> > 1. Do a lookup of the data.
> > 2. Present the data to the user. Are you sure you
> > want to delete the data
> > as you
> > see it on screen?
> > 3. If yes, record a delete request in a table,
> > together with a version
> > identifier of
> > the data "as it is seen". ( use timestamp here? ).
> > 4. Delete the data X hours later from a batch
> > process, when no intermediate
> > updates
> > have occurred ( timestamp > recorded version
> > timestamp ).
> >
> > If the data is then *actually* modified before
> these
> > X hours, the data will
> > not be deleted.
> > Otherwise it goes to the trashbin. Having said all
> > this, it's definitely
> > preferable
> > to do something simpler or not anything at all :)
> >
> > The simplest approach is for the modify action to
> > delete all outstanding
> > delete
> > requests as it updates data. No timestamp is then
> > needed.
> > Just record when the delete request is created for
>
=== message truncated ===


=====
'Focused action beats intellectual brilliance every time in the market place of human affairs' --Mark Sanborn
---------------------------------------------------------------------
       Yongqin Xu
       Senior Software Engineer
       Level 3 Communications Inc.

 *  Oracle9i Certified Database Administrator
 *  Sun Certified Enterprise Architect in J2EE
 *  Sun Certified Web Component Developer
 *  Sun Certified Java2 Developer
 *  Sun Certified Java2 Programmer
---------------------------------------------------------------------



__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

====================================================================
Companion Site: http://www.corej2eepatterns.com
J2EE BluePrints: http://java.sun.com/blueprints/corej2eepatterns
List Archive: http://archives.java.sun.com/archives/j2eepatterns-interest.html
Unsubscribing: email "signoff J2EEPATTERNS-INTEREST" to listserv@(protected)