db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <fisc...@seitenbau.net>
Subject AW: Best practice for entity update.
Date Mon, 12 Feb 2007 13:48:53 GMT
We have implemented something similar. What you are asking for is called
"optimistic locking" and it is usually done using triggers. See e.g.
http://orafaq.com/papers/locking.pdf

Our implementation is on oracle 9i: It increases a check column on each
update by one. The following sql creates the triggers:
--------------------------------------------------------
-- INSERT TRIGGER
--------------------------------------------------------
CREATE OR REPLACE TRIGGER ANSCHREIBEN_INS_CONCURRENCY
BEFORE INSERT ON ANSCHREIBEN FOR EACH ROW
begin
  SELECT 1 INTO :NEW.CONCURRENCY_VALUE FROM dual;
end;
.
run;

--------------------------------------------------------
-- UPDATE TRIGGER
--------------------------------------------------------
CREATE OR REPLACE TRIGGER ANSCHREIBEN_UPD_CONCURRENCY
BEFORE UPDATE ON ANSCHREIBEN FOR EACH ROW
declare
  ERROR_MESSAGE_T VARCHAR(200);
begin
  if( :NEW.CONCURRENCY_VALUE != :OLD.CONCURRENCY_VALUE) then
    select 'Concurrency failure. Old : '
            || CAST(:OLD.CONCURRENCY_VALUE AS VARCHAR2(50))
            || ' new: '
            || CAST(:NEW.CONCURRENCY_VALUE AS VARCHAR2(50))
        into ERROR_MESSAGE_T from dual;
    raise_application_error( -20000, ERROR_MESSAGE_T);
  end if;

  SELECT :OLD.CONCURRENCY_VALUE + 1 INTO :NEW.CONCURRENCY_VALUE FROM dual;
end;
.
run;

The save() and setConcurrencyValue() methods in the object needs to be
altered as well:

  public void save(Connection con) throws TorqueException
  {
    try
    {
      // first check whether object is modified
      // then save,
      // then increase concurrencyValue
      boolean modified = isModified();
      super.save(con);
      if (modified)
      {
        setConcurrencyValue(getConcurrencyValue() + 1);
      }
    }
    catch (TorqueException e)
    {
      logge.error(e);
      if (DatabaseHelper.isConcurrencyFailure(e))
      {
        throw new Exception(DefaultMessages.ERROR_CONCURRENT_MODIFICATION);
      }
      else
      {
        throw e;
      }
    }
  }

  public void setConcurrencyValue(long concurrencyValue)
  {
    // object mustnot be marked modified if concurrency_value is updated
after a save
    boolean modified = isModified();
    super.setConcurrencyValue(concurrencyValue);
    setModified(modified);
  }

On other databases the sql script does not run, but maybe it gives you the
general idea.

However, on oracle, this is problematic if BLOB and CLOB columns are used
with Sarav's patched village jar for oracle, so this needs also to be
patched in order to not use the CONCURRENCY_VALUE columns if columns are
selected for blob or clob update. If you are not using oracle, you need not
care about this.

       Thomas

"Thoralf Rickert" <thoralf.rickert@cadooz.de> schrieb am 09.02.2007
13:10:02:

> Hi!
>
> If it isn't possible to use a transaction connection you can use a
> timestamp column to verify if the row is changed in the database.
> Before saving the instance you can read the timestamp column from
> the DB and compare it with the timestamp that is set in your
> instance (this should be done in a "short transaction" during save()).
>
> bye
> Thoralf
>
> > -----Urspr√ľngliche Nachricht-----
> > Von: Christopher Thill [mailto:cthill69@yahoo.com]
> > Gesendet: Freitag, 9. Februar 2007 12:46
> > An: Apache Torque Users List
> > Betreff: Best practice for entity update.
> >
> >
> > I wanted to ask a question about the best way to
> > prevent users overwriting each others changes when
> > updating the database.
> >
> > If you use the simplest way
> >   IntakeTool intake = (IntakeTool)
> > context.get(ConstSpr.INTAKE_STRING);
> >   Group group =
> > intake.get(ConstSpr.INTAKE_SPRSA_CONTRACT,IntakeTool.DEFAULT_KEY);
> >   SprsaRsaContract sprsa = new SprsaRsaContract();
> >
> >   group.setProperties( sprsa );
> >   sprsa.save();
> >
> > You can over right another users changes to the
> > database.
> >
> > I have decided to stuff the torque object used to
> > build the form into the users temporary storage and
> > then when the user submits the form I retreive it and
> > do something like :
> >
> >    Criteria whereCriteria =
> > SpscmSalesCompanyPeer.buildCriteria(this);
> >    Criteria updateCriteria =
> > SpscmSalesCompanyPeer.buildCriteria(newValues);
> >
> >    SpscmSalesCompanyPeer.doUpdate(whereCriteria,
> > updateCriteria);
> >
> > Then check to see how many rows were updated.
> >
> > Any suggestions for this would be appreciated.
> >
> > Thanks,
> > Chris
> >
> > ---------------------------------------------------------------------
> > 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
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message