would be quite counterproductive. In truth, stock applications would frequently use no lock-
ing when possible just because of the volume of changes, although actual trade updates
would require some locking.
1. Transactions affect the speed of applications in two ways: transactions are expens-
ive to commit, and the locking associated with transactions can prevent database
2. Those two effects are antagonistic: waiting too long to commit a transaction in-
creases the amount of time that locks associated with the transaction are held.
Especially for transactions using stricter semantics, the balance should be toward
committing more frequently rather than holding the locks longer.
3. For fine-grained control of transactions in JDBC, use a default
TRANSACTION_READ_UNCOMMITTED level and explicitly lock data as
Result Set Processing
Typical database applications will operate on a range of data. The stock application, for ex-
ample, deals with a history of prices for an individual stock. That history is loaded via a
single SELECT statement:
SELECT * FROM stockprice WHERE symbol = 'TPKS' AND
pricedate >= '2013-01-01' AND pricedate <= '2013-12-31';
That statement returns 261 rows of data. If the option prices for the stock are also required, a
similar query would be executed that would retrieve five times that amount of data. The SQL
to retrieve all data in the sample database (128 stocks covering 1 year) will retrieve 200,448
rows of data:
SELECT * FROM stockprice s, stockoptionprice o WHERE
o.symbol = s.symbol AND s.pricedate >= '2013-01-01'
AND s.pricedate <= '2013-12-31';