[Bodington-developers] Locking Issues (Java, Explicit DB, Optimistic DB)

[Matthew B. <mat...@ou...>]

For my work on quotas I need to get the current quota, see if there is 
enough available to upload the new file, if so upload the file and if it 
succeeds increase the quota by the size of the file.

This is easy unless two people are uploading the file in the same place 
at the same time.

Solutions:

Java Synchronisation - Portable across all platforms. Causes problems if 
we ever wanted to cluster Bodington across servlets engines. Initally 
would cause coarse locking although we could improve this.

Explicit DB Locking - By using updatable ResultsSets rows in the 
database can be locked for an update so that they cannot be read by 
anyone else. This is an optional part of the JDBC spec but it looks like 
the DB platforms we use support it (MSSQL, PgSQL, MySQL).

Optimistic DB Updates - As we seem to have the database in 
TRANSACTION_READ_COMMITTED then we can do queries specifying the old 
object values such as

UPDATE person SET surname = "Smith" WHERE id = 3 and forenames = "Dave" 
and surname = "Jones";

This way if someone else has modified the row underneath us the update 
fails and the application can decide what to do (try again, fail). This 
should work on all DBs but requires more work on the side of the 
application.

Change Transaction Level - At the moment we have a isolation level of 
TRANSACTION_READ_COMMITTED (on PgSQL at least as we don't explicitly set 
it). If we updated to 	TRANSACTION_REPEATABLE_READ then we don't have to 
worry about a read row being modified under our feet. The problem is 
that not all DBs support this and we end up have lots of DB rows locked 
that don't need to be and as a result the DB has to work much harder. I 
think this is a non starter.

Comments?

-- 
  -- Matthew Buckett, VLE Developer
  -- Learning Technologies Group, Oxford University Computing Services
  -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/