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

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

More comments below (quick this time promise)

Matthew Buckett wrote:
> 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 don't know the code, but it the request cycles are detached (ie all 
the steps are in separate request cycles) and the transactions are 
completed the optimistic locking works.

If there are just 2 cycles, one for user1 and one for use2 then the 
transaction manager will solve the problem......

*but* be wary of long transactions..... you *will* get deadlocks under 
load if you use long transactions, Oracle you can configure to avoid, by 
MySQL+InnoDB there is nothing you can do, there is a bit in the manual 
about it. Select for Update locks neighboring records in the index in 
MySQL, in Oracle the lock is maintained in a part of the DB block 
(inittrans)

Transaction locking only works if the transactions are intertwined, the 
the for update controls things.

Just in case you were tempted (which Im almost certain you are not) dont 
go for pessimistic locking in the DB unless you want to become great 
friends with you DBA's (not), in Sakai 1.0 there were db pessimistic 
locks.... what fun!

Ian

On the other hand
you might have great DBA's who you want to get 'friendly' with :)

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