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

[Ian B. <ia...@ca...>]

Quick comments below...

Matthew Buckett wrote:
> 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?


yes that you have to remember which ones and carry them all around 
though the request cycle, it really easy if you always take a version 
field with all objects.




> 
> 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.

thats probably ok, if you dont mind maintaining the SQL. The additional 
performance hit of 1 long/timestamp to 4 or 5 varchars probably isnt 
significant. (but don't do it with a BLOB/CLOB, not that you can anyway :) )


> 
>> 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).


If the quota had a version stamp, then you could check that for changes 
in the update statement

This is Oracle speak, the updates might have to be in 2 steps in MySQL 4

eg
 > User 1: get current quota 8.5MB of 10MB (ver= 12345)
SELECT
	currentQuota,
	limitQuota,
	version
    FROM quotas
   WHERE resource = 1;
request version now 12345

 > User 2: get current quota 8.5MB of 10MB
SELECT
	currentQuota,
	limitQuota,
	version
    FROM quotas
   WHERE resource = 1;
request version now 12345

User 2: set quota to 9.5MB
UPDATE quotas
	SET currentQuota = currentQuota+fileSize,
	version = 123444
   WHERE resource = 1
     and version = 12345;

request version now = 123444

and upload 1MB file.
UPDATE
	resources r,
	qota q
     SET r.body = filedata,
	q.version = 12555
   WHERE r.resource = q.resource
     and q.version = 12444
     and q.resource = 1

request version now = 123555

 > User 1: set quota to 10.5MB and upload 1MB file.


> 
UPDATE quotas
	SET currentQuota = currentQuota+fileSize,
	version = 123666
   WHERE resource = 1
     and version = 12345;

BOOM! no update
request version still 12345

and upload 1MB file.
UPDATE
	resources r,
	qota q
     SET r.body = filedata,
	q.version = 12777
   WHERE r.resource = q.resource
     and q.version = 12345
     and q.resource = 1

BOOM! no update,
request version still 12345

user is informed