Re: Master-Details with torque

[Michel Beijlevelt / Lucka]

Howdy BEN,

in concurrent database environments it's uncommon to use locking 
strategies as you described. This way transactions become way too long 
and your application will underperform.

In this case I'd recommend you use optimistic locking. In other words: 
use the previous state of the row you want to update as a safeguard in 
the where-clause of the update statement, or maybe just the key columns, 
instead of all columns.

For example:

update T
set T.A = 'newA'
where T.ID = 'id'
becomes:

update T
set T.A = 'newA'
where T.ID = 'id'
and T.A = 'oldA'
and T.B = 'oldB'
and T.C = 'oldC'
..etc...
So if any of the attributes of the row have been updated by another user 
between the moment you retreived the record and the moment you perform 
the update of the record  the latter statement will not update the row 
after which you could inform the user to refresh his data.

You could also add timestamp columns to your tables; only update the row 
if the tamestamp is older than or equal to the one you got when you 
retrieved the row that you're updating. If the timestamp is newer, heck, 
somebody modified the record in the meantime! :)

gr. Michel

PS Note: this is an issue for all concurrent database systems and 
doesn't really relate to Torque

BEN BOOKEY wrote:

Dear List,

Can anyone tell me how you deal with multi-user master-detail tables using torque. ? and if there is anyone who has done this ?

I would be very grateful for someone to explain how they overcame this.  ie. USER 1 is not allowed to DELETE parent T1 table while USER2 is editting child table T2. 

Because the applications are using a JDBC cache at the end of the day, and not within the DB itself. I suspect that the Torque solution would be " USER 2 will get an error when he tries to update T2 when its related row in its parent T1 no longer exists". In ORACLE's BC4J a lock is place to prevent someone from deleting the parent during this operation.

Am I right..? Please help me !! 

regards

Ben



 



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]