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

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

Ian Boston wrote:
> Quick comments below...
> 
>> 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?)

As quotas are generally overallocated do we care about people exceeding 
them a little and then prevent the next upload?

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

Yep:

> 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

SELECT currentQuota, limitQuota FROM quotas WHERE resource = 1 FOR
UPDATE;

which succeeds.

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


SELECT currentQuota, limitQuota FROM quotas WHERE resource = 1 FOR
UPDATE;

which blocks until the first transaction completes.

[...snipped..]

> user is informed

I know there are several ways of solving the problem and am just 
wondering which we should head down. Which comes back to the question of 
caring about multiple JVMs.

I haven't started doing any code for this yet.

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