Obviously there must be something wrong with this code, but I cannot see what it is. Should I perhaps be closing the (empty) result set to commit the transaction?
To commit your transaction, you need to call Connection.commit() (or put your connection into auto-commit mode). Closing your result sets is desirable, but by itself it won't commit the transaction. I think the first thing you should try is to put a Connection.commit() into your password validation subroutine, and re-analyze the locking behaviors to see how they change. You should also close your statements when you no longer need them. Derby has finalizers for ResultSet, Statement, and Connection objects, but waiting for the Java garbage collector to collect your JDBC objects can be very problematic, as they can accumulate quickly and can hold quite a bit of resources. So it's good to keep in the habit of closing them as soon as possible. One thing that programmers often overlook is that you need to commit your read-only transactions as well as committing your update transactions. Read-only transactions take locks, too! Another thing you can do is to configure the transaction's isolation level; the default isolation level holds a lot of locks, and many applications find that they can run successfully using a lower level of isolation. It definitely seems that you've done the overall analysis correctly, and I think you're getting the hang of reading your derby trace logs. As you continue to practice reading those logs, you'll become more comfortable with them, and you'll find that you get more and more information out of them. But it does take a bit of practice, so keep it up :) Here's a couple resources that should prove useful: http://db.apache.org/derby/docs/10.6/devguide/cdevconcepts19173.html http://db.apache.org/derby/docs/10.6/devguide/cdevconcepts30291.html http://download.oracle.com/javase/tutorial/jdbc/basics/transactions.html http://download.oracle.com/javase/1.4.2/docs/api/java/sql/Connection.html Good luck and let us know about your further experiments! bryan
