Keith Kwiatek wrote:
>
> If you are using JSP to do web applications, how are you using JSP
> to hand the fact that the web server connection to the database is
> non-persistant?
Well, before we talk about update issues, you should really consider
Connection pooling, whereby you keep a VM-wide set of connections to
your database and make them available by static methods in a
ConnectionPool (or similar) class. This will remove the rather drastic
performance hit of opening a Connection to a database for every
transaction.
> For example, User "A" fetches a row for display in web page (db connection
> ends). At the same time user"B" fetches the same data for display in a web
> page (db connection ends). Then user "B" updates the row that user "A" is
> reading (db connection ends), then user "A" updates the row, thus
> overwritting user "B's" update)
This is a classic problem in client-server database design where the
client can change some data without directly updating the back-end
server (e.g., some client-side caching). There are two classical ways
to detect/prevent the problem:
[1] Optimistic concurrency. Add a column to your table that increments
every time an update is made. Let's call it a `timestamp' (because
that's what Sybase calls theirs). RDBMS' usually have a column type
with this behavior, though it's easy to implement yourself. When the
client app selects one or more rows, it keeps the timestamp column
hidden from the user. When the client app is ready to commit the
update, it must only perform the update if the row has remained
unchanged, ala:
UPDATE tabname SET c1=?,c2=?,c3=? WHERE id=? AND timestamp=TTT
replacing TTT with the hidden timestamp value from the SELECT. You can
then check the number of rows affected which should == 1 on a successful
update, or 0 on a failed update. You can then present the user with
options to re-do their changes based on the updated data, force their
data to be saved, or perform a more complex conflict resolution
operation.
[2] Pessimistic concurrency. Add a column to your table that acts as a
lock. When the user checks out a row for editing, that value gets
changed to a `locked' status. When the user commits their changes, the
value gets changed to an `available' status. During the time when the
row is locked, no other user can check-out the row for editing.
The problem with this technique, as you might guess, is that the client
can disappear at any time, leaving the row in a permanently locked
state. This could be solved with appropriate use of timeouts, etc, but
starts to get complicated really fast.
Most people in high-transaction-volume operations tend to opt for [1],
as it puts the least strain on the server and relies on the probability
that `most of the time there will be no conflict'. If you frequently
have the possibility of two users updating the same row, you should
really reconsider your database design.
Some vendors have Cached RowSet libraries that implement [1] and some of
which even implement basic conflict resolution for you, indicating which
columns are in conflict with the prior update.
> Also, how are you using JSP to handle updates to a row in which only SOME of
> the update fields change (instead of updating all of the fields in the
> rows)?
Well, you could keep an original copy of each column, then build an
UPDATE statement on-the-fly that only updates the changed columns, but
see my mention of conflict resolution for [1] above...
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
FAQs on JSP can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
