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

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

Ian Boston wrote:

Thanks Ian...

> Transaction manager would work,
> if using Spring you could use Spring JDBC, which just places the 
> transaction manager in by defining the methods to manage.
> Alternatively (if feeling brave) you could do an AOP point cut (is that 
> the right term ?) to intercept the calls (in essence what Spring does).
> 
> However the transaction manager solution will only work for the Same 
> JVM, and will not address the syncronisation issue between requests.

Do we care about this? Do we ever plan to allow bodington to run across 
several JVMs?

> eg
> 
> User1 goes to web page, reads doc, decides to update.
> User2 reads doc (faster:)), updates
> User2 begins upload and overwrites user1's docs.
> 
> The solution is to use Optimistic Locking implemented as a Timestamp or 
> long/bigint in the record. (ed System.currentTimeMillis() ), this 
> travels around with the web page, so when User1 tries to overwrite, they 
> are told that the *point* at which they were overwriting from is now 
> invalid.

This is the general problem which bodington at the moment doesn't solve :-(

> This is a standard Hibernate technique, there is an explicit version 
> field in hbm files to do optimistic locking, but it can just as well be 
> done with JDBC. Its a bit more robust and generic than the collection of 
> fields approach, and works on all db objects.

Yep. For Bodington this might be a reasonably easy fix as all our DB 
objects are linked to the objects table so we could add the locking 
field there.

As long as you have TRANSACTION_READ_COMMITTED why is the collection of 
fields approach problematic?

I can see that you may end up with BIG update statements if the table 
has a large number of columns, or some columns with big fields.

> It doesn't rely on any one transaction isolation mechanism (other than 
> no transaction), provided that there is a transaction in place.
> 
> If you have a well know point through which all db object access flows, 
> you could put it in as an option on all db objects.
> 
> 
> (I use this in Sakai RWiki, and Sakai Search to do optimistic locking 
> and to ask the user which version they want to commit to the database, 
> old or new)

Even if you don't involve the web layer I have the problem with quota in 
that I need to do:

SELECT currentQuota, limitQuota FROM quotas WHERE resource = 1;
check in Java if uploading the new file will push the current quota over 
the limit, if not update the quota and upload the file:
UPDATE quotas SET currentQuota = currentQuota+fileSize WHERE resource = 1;

The problem is that two people may be uploading at the same time so you 
might get:

User 1: get current quota 8.5MB of 10MB
User 2: get current quota 8.5MB of 10MB
User 2: set quota to 9.5MB and upload 1MB file.
User 1: set quota to 10.5MB and upload 1MB file.

Now user 1 has exceeded the quota but still has the file uploaded which 
shouldn't be allowed (or should it?)

The other option is to check after changing the quota with another 
select that the quota is set to the expected value (although this really 
is just an expansion of the collection of fields update).

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