Re: (Mis)use of transactions

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


On Thu, 09 Aug 2007 21:08:37 +0100, RedGrittyBrick
<RedGrittyBrick@xxxxxxxxxxxxx> wrote:
It's a bit quiet in comp.lang.java.databases so I hope no-one minds a
database question that isn't particularly related to Java or JDBC
(though that's what I am actually using).

I have an app, several bits of which allow a user to maintain simple
tabular reference data that is stored in database tables.

When the user elects to maintain one of these tables, I pop up a new
window that contains a JPanel containing a JTable, an "OK" button and a
"Cancel" button.

In the JPanel's constructor I get a java.sql.Connection instance and
thus open a connection to the DBMS.

Whilst the user edits the data in the JTable I am firing off the
appropriate executeUpdate methods to send updates to the DBMS.

When the user clicks OK I invoke commit() and close() on the connection.

If the user clicks Cancel I invoke rollback() and close() on the connection.

-- * --

However if the user does a few changes and then wanders off for lunch,
the database table is left in a locked state that obviously is likely to
cause problems for other users (or other threads in the user's app).

The only improvements I can think of are to track the changes inside the
app and only do getConnection(), executeUpdate(), close() in the "OK"
button's ActionListener.

Keeping a log of changes or comparing before and after versions of a
List<foo> in a table model seems a bit tedious.

Maybe I should do the getConnection(), executeUpdate(), close() for each
little change to a table row and remove the "Cancel" button?

What do you do?
.