Hi RedGrittyBrick,

I never, ever leave control of a DBMS transaction in the hands of user, for
just the reasons you point out.  Indeed, I do everything possible to
minimize the time between starting a transaction and then either committing
it or rolling it back.

Thus, I suggest exactly what you proposed: Cache locally all the edits the
user is making and then only start the transaction and commit it once the
user says "OK".  Of course, there's always the possibility that another user
might be editing the data at the same time.  So, if it's reasonable enough
(e.g., the entire reference table isn't too large), after the user has
pressed OK but before I start the transaction, I'll requery the table and
compare it to what the user is seeing (i.e., the local cache).  If there has
been a change, I notify the user that the database has changed since they
started editing and prompt them if they'd like to review what's changed
before committing their work.  If the reference table does not have a small
number of rows, then I'll at least notify them of rows that others have
changed that the local user is also trying to change.  This user feedback
becomes especially important if another user has changed the primary key
(P.K.) of a row that the local user also edited or if another user has
deleted one of the rows the local user has edited.

Tracking the changes that the user has made should be pretty easy, if it's a
straightforward reference / lookup table.  If it's just a "Code" and
"Description" table, then I think a Nx4 array/matrix should suffice.  Column
1: Type (I,D,U for insert, delete, update, respectively), column 2: old
primary key value, column 3: new primary key value (just copy old p.k. value
if user didn't change), column 4: new description.  The N rows are the
actions taken by the user, in order.  Here's an example of what such an
in-memory change matrix might look like.  There are just two columns, Code
and Description, and Code is the P.K.  The example is taken from a reference
table of psychological "presenting problems" occurring at a crisis
intervention:

    Old   New   New                            My
Type Code  Code  Description                    Comments
---- ----- ----- ------------------------------ ------------------------------
 I  ATOT  ATOT  Atypical Behavior - Other      User added new code/desc.
 I  ATUB  ATUB  Unusual / Bizarre Behavior     ""
 I  ATVO  ATVO  Violent / Out of Control       ""
 D  ME00  ME00  Medical / Somatic              User deleted code/desc.
 D  MR00  MR00  Mental Retardation             ""
 U  PSDE  PSDE  Depression                     User fixed misspell: Depresion
 U  PSDA  PSDL  Delusional                     User changed code (P.K.)
 U  PSPA  PSPN  Paranoria, NOS                 User changed both code/desc.

A simple in-memory table like this also makes it very easy to implement an
Undo function for the user: To undo, just pop off (the bottom) of the stack.
Actually, you should probably add a fifth column: Old Description.  This
would allow you to check and see if another user has changed the Description
column while the local user was editing, as well as allow your Undo function
to undo edits to the Description column.

All pretty easy, I'd think, and shouldn't require too many LOC at all.

A variant on all this would be to copy of the reference table into a
temporary table and let the user make all of her edits on the temporary
table.  This variant thus would allow you to leave the transaction open as
long as you like w/o affecting other users.  Unlike the in-memory option,
though, it consumes server-side resources; plus, transactions usually
timeout eventually.  The upside is that if your local client crashes, and
you're using the in-memory approach, the edits are lost, but temp tables can
often be configured to persist until explicitly deleted, so the local
client, once re-started, could reconnect to the temp table and recover the
lost edits.  That said, though, I'd still recommend against allocating and
holding open server-side resources when it's not essential.  If you wanted
this level of crash recovery, you could just as well periodically write out
your in-memory changes table (example above) to a local disk file for
recovering after a local client crash.

Finally, you already mentioned the third option I can think of, namely
committing each user-specified change as you go.  I would NOT close and
re-open the connection, though, as you offered.  It's not necessary, and
will likely be incredibly slow, because creating and tearing down DB
connections is generally resource-intensive and latency-bound due to the
lock-step messages that have to go back and forth over the wire.  Just leave
the connection open as long as your UI dialog is open, but start, execute,
and commit the transaction all at once.

Hope this helps.

Doug

Sounds like very confused code to me. Is it a UI class or a database
class? In any case, I'd avoid subclassing unnecessarily.

And quite possibly the connection times out causing all of the users
data to be discarded. I'm guessing most people don't like it when that
happens.

No particular need to close each time, although it is worth being able
to reopen then connection if it fails.

From a usability perspective I like applications that are as immediate
as possible. That means no OK/Cancel buttons. However, if you want
locking you can still implement your own locking system on top of the
database.

There is a problem in that updates that other actors make while the
window is open wont be automatically shown. You can do that relatively
efficiently by putting triggers on the main table that copy updates into
shadow tables.

Tom Hawtin

I think you should drop database builtin locking and go to
application based locking.

Search for optimistic locking and pessimistic locking.

There are well known techniques to handle database operations
in human time (minutes) instead of computer time (milliseconds).

Arne