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

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

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

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)

HTH
Ian

Jon Maber wrote:
> Is this another solution?
> *put a transaction manager in front of the database.
> Jon
> 
> Matthew Buckett wrote:
>> 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?
>>
>>   
> 
> 
> 
> _______________________________________________
> Bodington-developers mailing list
> Bod...@li...
> https://lists.sourceforge.net/lists/listinfo/bodington-developers