Re: (Mis)use of transactions

RedGrittyBrick 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?

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
.