You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by BEN BOOKEY <br...@hotmail.com> on 2003/08/04 17:22:19 UTC
Master-Details with torque
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
Re: Master-Details with torque
Posted by BEN BOOKEY <br...@hotmail.com>.
Dear Michel and mailing list.
The list is down at the moment so I hope this email makes it to everyone !
What you are saying is absolutely correct, but (with oracle and JDBC) if a
record in T1 has been deleted or updated, and I try to update EXACTLY like
you describe, Oracle returns a 0 records updated message (i.e. returns no
error!!!!!). We would like 1) to inform the user when data has changed or
been deleted and he updates which isnt possible (with oracle) the way you
describe. 2) In some cases I would like to lock a row (its necessary
sometimes if we are doing bulk import operations), and we want to stop other
users from interfering. We have found Table level locking methods in Torque
but no row level locking. Does torque support row level locking???????
With Oracle db one can submit a SQL query to lock a record with "SELECT *
from T1 where Columm1='ben' for update nowait" / this is useful sometimes
for a short transaction so no one interferes with the record, ( e.g. to
check the OriginalDBvalues with the cachedValues). If I want to execute
something like this is there a command using the peer class, using for
example executeQuery method which doesnt seem to work. Any help out there ?
Kind regards,
Ben Bookey.
----- Original Message -----
From: "Michel Beijlevelt / Lucka" <mb...@lucka.nl>
To: "Apache Torque Users List" <to...@db.apache.org>
Sent: Tuesday, August 05, 2003 9:24 AM
Subject: Re: Master-Details with torque
> 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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Master-Details with torque
Posted by Michel Beijlevelt / Lucka <mb...@lucka.nl>.
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