db-torque-user mailing list archives

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: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org