I'm working on the same project. I've been picking away at it for
months now because I am fully employed on a contract. However, my entire
back end is finished (and unit tested
) and what remains is for me
to pick away at the front end. Though I wont give you details on what I've done, I will tell you what you need to consider in your design.
FYI - I'm an architect, but am doing this damn assignment because I paid
$250 for it years ago and I want the "Street Cred" of SCJD.
Looking at your ideas, you havent fully thought through ALL of the concurrency issues. They are subtle. You can expect to spend sizeable time
to get it right. You will be handsomely rewarded in intellectual value though, since you will get first hand experience in database internals.
Some things to think about, which will reveal issues with concurrency:
1. What happens if a thread calls lock() twice in a row?
2. Lets say you call lock(). Now you are wait()-ing because someone else
holds the lock. What happens when your wait ends and you are woken back
up? Is it possible that the other thread that you were waiting for did
a delete on that row?
3. Is there a primary (possibly composite) key for this table ??
3a. If there is a candidate key, what happens if you UPDATE a row and
the requested updates collide with the primary key? How can you
3b. Lets imagine you have two threads. One is INSERTING and the other is
UPDATEing. Is there a situation where the INSERT and the UPDATE
happen close enough that two rows end up in the table that create
a primary key violation? The race condition requires that the check
for key constraints race at the right time.
These are some things to think about. Deletes Racing Row Locks, Updates
and Inserts Racing each other, Updates (as well as inserts) causing Key
Next, you might think about Row Locks as well as Table Locks to resolve
these races. However, whenever you have two (or more threads) and two (or
more) locks, there is a possibility of deadlock. So, you must design your
locking protocol to escalate, as well as be deadlock free. Design wise,
your database needs to be sequentially consistent ; so, as a start, all
reads and writes need to be atomic.
This is GOOD STUFF and the only place you get to play with this (other
than the SCJD) is a good, solid graduate level course in Database Theory
that includes a Systems approach. Finding courses taught from this perspective is tough - usually only full-time day programs at big campuses.
It sucks writing this, but doing it JUST ONCE is rewarding as you may
never get a chance to do real systems level design once you move onto
J2EE and what not.
If you want true illumination - seek out "Principles of Transaction Processing" by Jim Gray. Everything you take for granted with CMP and
JTA is spelled out there.