Menu
▾
▴
bodington-developers
|
From: Matthew B. <mat...@ou...> - 2006-06-14 08:15:17
|
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? -- -- Matthew Buckett, VLE Developer -- Learning Technologies Group, Oxford University Computing Services -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/ |
|
From: Jon M. <jo...@te...> - 2006-06-14 09:00:35
|
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? > > |
|
From: Matthew B. <mat...@ou...> - 2006-06-14 09:14:16
|
Jon Maber wrote: > Is this another solution? > *put a transaction manager in front of the database. By this do you mean move to using JTA? If so what do we gain from this over JDBC trasactions apart from abstracting transactions away from the implementation (which is a good thing)? Or are you suggesting that we manage transactions on our Java objects, in effect move to an EJB container? -- -- Matthew Buckett, VLE Developer -- Learning Technologies Group, Oxford University Computing Services -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/ |
|
From: Ian B. <ia...@ca...> - 2006-06-14 09:22:19
|
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 |
|
From: Matthew B. <mat...@ou...> - 2006-06-14 10:12:46
|
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? 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. > 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). -- -- Matthew Buckett, VLE Developer -- Learning Technologies Group, Oxford University Computing Services -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/ |
|
From: Ian B. <ia...@ca...> - 2006-06-14 10:37:23
|
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
|
|
From: Matthew B. <mat...@ou...> - 2006-06-14 11:00:09
|
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/ |
|
From: Ian B. <ia...@ca...> - 2006-06-14 11:25:49
|
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. > |
|
From: Jon M. <jo...@te...> - 2006-06-14 10:42:32
|
Matthew Buckett wrote:
> 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)
I'll let you and Ian discuss the general case (because id have to study
a book on the subject to catch up with you).
Did you say if your code is directly calls the SQL or wraps records in
the quotas table in a Bodington PersistentObject subclass?
If the latter then database reading and writing will operate through a
single instance of that class so you could add a method like this;
public synchronized boolean attemptUpload( long size )
{
// this class knows the value of current uploaded bytes
// return false if already over limit.
// add to uploaded bytes record.
// call standard save record routine
// return true
}
The important thing is that this routine does the 'over limit'
calculation and the save record part in the same synchronized block.
Jon
|
|
From: Matthew B. <mat...@ou...> - 2006-06-14 12:13:41
|
Jon Maber wrote:
> Matthew Buckett wrote:
>> 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)
> I'll let you and Ian discuss the general case (because id have to study
> a book on the subject to catch up with you).
>
> Did you say if your code is directly calls the SQL or wraps records in
> the quotas table in a Bodington PersistentObject subclass?
At the moment I don't have a quota table but use two metadata entries as
most of the time we don't need to know the quota so performance isn't
crucial. This could change in the future.
> If the latter then database reading and writing will operate through a
> single instance of that class so you could add a method like this;
>
> public synchronized boolean attemptUpload( long size )
> {
> // this class knows the value of current uploaded bytes
> // return false if already over limit.
> // add to uploaded bytes record.
> // call standard save record routine
> // return true
> }
>
> The important thing is that this routine does the 'over limit'
> calculation and the save record part in the same synchronized block.
Even if I don't have a Quota class I could do the synchronization on the
resource object to which the quota is attached. This only works in a
single JVM although there are some projects attempting to weave extra
bits in to allow locking primatives to work across JVMs.
http://www.terracottatech.com/terracotta_Spring.shtml
--
-- Matthew Buckett, VLE Developer
-- Learning Technologies Group, Oxford University Computing Services
-- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/
|
|
From: Matthew B. <mat...@ou...> - 2006-06-15 09:41:22
|
Jon Maber wrote: > Matthew Buckett wrote: > > Did you say if your code is directly calls the SQL or wraps records in > the quotas table in a Bodington PersistentObject subclass? > > If the latter then database reading and writing will operate through a > single instance of that class so you could add a method like this; Which reminds me we currently have a bit of a problem with SoftCache and transactions. If I edit a user (loading the User object then changing the surname) then attempt to save the user which fails (and the DB transaction rolls back). Softcache will still return the modified user. -- -- Matthew Buckett, VLE Developer -- Learning Technologies Group, Oxford University Computing Services -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/ |
|
From: Jon M. <jo...@te...> - 2006-06-15 13:11:26
|
Matthew Buckett wrote: > Jon Maber wrote: > >> Matthew Buckett wrote: >> >> Did you say if your code is directly calls the SQL or wraps records in >> the quotas table in a Bodington PersistentObject subclass? >> >> If the latter then database reading and writing will operate through a >> single instance of that class so you could add a method like this; >> > > Which reminds me we currently have a bit of a problem with SoftCache and > transactions. > > If I edit a user (loading the User object then changing the surname) > then attempt to save the user which fails (and the DB transaction rolls > back). Softcache will still return the modified user Just the kind of problem that you are infinitely qualified to sort out. ;-) Throwing in my tuppence worth, I think it might take some fairly serious work to get this right. The SQLDatabase class would have to keep track of all the PersistentObjects involved in the transaction. At the moment calling set methods on PersistentObjects doesn't count as part of a transaction - only saving to the database is, so a really thorough work over would have to find a solution to that. Perhaps if the PersistentObject set methods threw exceptions if calling them caused a constraint to be violated? In some circumstances you need to make two or more calls to various setter methods and the constraints are unavoidably broken during the intermediate stages - that could be an area to work on in terms of transactions. In my Vivida project I'm working to a much simpler data model which imposes limitations but makes things easier for the programmer and I hope more reliable. None of the data is in a relational database, it's all in XML files and a fairly simply file locking scheme is implemented. At present none of the data is cached, except via the OS file system cache but it would be fairly simple to implement. All data processing is done via XSLT (Saxon) which is forced to access the file system via URLResolvers which can be subclassed. The return value from these resolvers can reference a stream source or a DOM source or other kinds of source so while at present my customised resolvers simply implement some read and write file locking (so that each XSLT 'transaction' can be made atomic) in the future XML files could be cached as DOM representations. The rolling back of the transaction in the event of an error is quite simple - output URL resolvers direct output to temporary files and these are only copied over on top of the target output files at the end of the successful transaction (perhaps after they have all been validated). Of course unlike an RDB there are no referential integrity checks between XML files and only limited validation within them but that need not necessarily be a huge drawback. So success of the transaction is fairly simply judged - did the transaction manage to get write locks for all the output files, did the transaction put well formed XML into all the output files and optionally did the output files validate against their schemas? At present Vivida is intended for a very small web site with no particular requirement to do complex or deep queries of the data so applying XPath expressions on individual XML files via XSLT is fine. In the future an XML indexer could be tacked on to index the XML files and allowing higher performance queries that span multiple files. This could also help cope with the obvious problem that would occur with a large installation - massive XML files. For example how would the Bodington users table be implemented in XML? It could be maintained conceptually as a single XML file but stored within an XML indexing store which is capable of locking portions of the file independently or it could be split into many XML files and the indexer used to find the right file for a given user. This kind of basic user data is likely to be the least suitable for storage in XML files when there are large numbers of users. However, the data that is most suitable is the tool content data, e.g. MCQ, Questionnaire, Short answer paper questions etc. would probably be better stored as IMS QTI files. Resource specific properties would probably be best as XML files of some kind. In fact probably the resources table would be better as one XML file per resource - instead of using index fields in the database to store the tree structure Bodington could simply use the host file system - one sub directory per resource. The XMLRepository is already used to find resources via XML representations of their metadata so why bother with the resources table at all? Just a thought. Jon |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X