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

[Jon M. <jo...@te...>]

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