Concurrent access and data overwrite protection

ddantoni · Post by **ddantoni** » 17.04.2008, 23:07

Hello,
I'm writing an application where user can fill record within a grid. When a certain field is filled, the record is considered for filled and locked.

My application will work on a network and many users will be able to fill the same record at the same time (on differents computers).

To prevent a second post that will erase precedent posted record, I use a field named LastModifiedTime where I store the last record modification time. So, before posting data, I would like check that the LastModifiedTime has not changed since my last SELECT, and if it's the case, ask for user if he would like to overwrite the record.

I use CachedUpdates and what I want to do technicaly it's to get my primary key value and the LastModifiedDate value before updating record to check if it has changed.

Is it the good method to make that (Concurrent access protection) ?
If true, how can I do ?

Thanks a lot,
David

Post by **mdaems** » 17.04.2008, 23:31

Bon soir David,

Well, you could use the changes added by Sandeep_c24
See http://zeos.firmos.at/viewtopic.php?t=1757 in combination with wmWhereAll : If zero rows are updated because the original data was changed, you get an error. Attention : Make sure what version you are using. It's NOT default 6.6 behaviour, so you need to use testing branch or do the patch yourself.

I don't know if better solutions exist, but I suppose this will have the desired effect...

Mark

cipto_kh · Post by **cipto_kh** » 18.04.2008, 02:03

When use chache update you can know the old value from that field by using "OldValue" in that persistant field property (before the call ApplyUpdates method). Example: ZQuery1MyField1.OldValue

Is that what you want? or I'm wrong?

ddantoni · Post by **ddantoni** » 19.04.2008, 14:58

Bonjour mdaems and cipto_kh,

mdaems I didn't know this method that consist to use where clause to specify data field on update and check the UpdateCount after.
Effectivly your solution is good for me and I will use it combined with LastModifiedTime.

For testing, I have add modification of sandeep_c24 in TZUpdateSQL.PostUpdates and add in my where clauses (Update and Delete) this : MyTable.ID=:OLD_ID AND MyTable.LastModified = :OLD_LastModified and it's ok.

Moreover, I can update and delete records manually (with IZStatement) without use every fields of my table.

The only thing that is not perfect in sandeep_c24 code (for me), it's there is not ErrorCode defined with Exception. So, I'll use CreateWithCode constructor for allowing me to identify properly the exception.

Thanks you so much,
David

ddantoni · Post by **ddantoni** » 19.04.2008, 17:34

I created this function that will replace my ApplyUpdates calls :

Code: Select all

Procedure TFrmMain.ConcurrentApplyUpdates;
Var lConcurrentError, B: Boolean;
Begin
  lConcurrentError := True;
  While lConcurrentError Do
  Begin
    Try
      ZQuery1.ApplyUpdates;
      lConcurrentError := False;
    Except
      On E: EZSQLException Do
        If E.ErrorCode<>2802{2802 is the code of exception (if ValidateUpdateCount and UpdatedRecordCount<>1)} Then // LastModifiedTime different
          Raise;

      On E: Exception Do
        Raise;
    End;

    If lConcurrentError Then
    Begin
      // For the test: I don't know why but OldRowAccessor is Nil
      if TZAbstractCachedResultSetAccess(TZAbstractDatasetAccess(zqPlanning).CachedResultSet).OldRowAccessor=nil then exit;

      With TZAbstractCachedResultSetAccess(TZAbstractDatasetAccess(zqPlanning).CachedResultSet).OldRowAccessor Do
        If AskQuestion(Format(
            'Record %s: %s %s modified by another person.'+sLineBreak+
            'Would you like to overwrite it and so erase data modified by this other person ?',
            [GetString(4, B), GetString(5, B), GetString(6, B)]))=Yes Then
          SetNull(15)
        Else
        Begin
          If PluginInst.hxdMsgMan.AskQuestion(MsgTitle, Format(
            'Would you like to cancel modifications of %s: %s %s', [GetString(4, B), GetString(5, B), GetString(6, B)]))=No Then
            Raise Exception.Create('Saving data process canceled by user.');

          // The data modified here are canceled => Cached update in queue is removed:
          With TZAbstractCachedResultSetAccess(TZAbstractDatasetAccess(zqPlanning).CachedResultSet) Do
          Begin
            // Delete the cached update
            InitialRowsList.Delete(0);
            CurrentRowsList.Delete(0);
          End;
        End;
    End;
  End;
End;

I don't know why but OldRowAccessor of CachedResultSet is nil after ZQuery1.ApplyUpdates call. I want use it to display the record informations (for user) and set the OLD_LastModifiedTime to null (see UpdateSQL below to understant this) if user want to overwrite db record.

My Update query (ZUpdate1.ModifySQL):

Code: Select all

UPDATE planning SET
  planning.Nom = :Nom,
  planning.Prenom = :Prenom,
  planning.Tel = :Tel,
  ...
  planning.LastModifiedTime = :LastModifiedTime
WHERE
  (planning.Planning_ID = :OLD_Planning_ID) and 
  ((:OLD_LastModifiedTime Is Null) Or 
   (planning.LastModifiedTime = :OLD_LastModifiedTime))

I hope that you will be able to tell me how use OldRowAccessor or other property to get (and set) data of the record that had caused the concurrent access error.

Thanks you,
David

ddantoni · Post by **ddantoni** » 25.04.2008, 20:33

Nobody to help me ?

Thanks in advance.
David

ddantoni · Post by **ddantoni** » 28.04.2008, 20:42

I have found the problem. We can't get implementing object of interface simply in casting the interface. And it's because I couldn't access to CachedResultSet object.

Here, the code that I use :

Code: Select all

// http://hallvards.blogspot.com/2004/07/hack-7-interface-to-object-in-delphi.html
function GetImplementingObject(const I: IInterface): TObject;
const   
  AddByte = $04244483;  
  AddLong = $04244481;  
type   
  PAdjustSelfThunk = ^TAdjustSelfThunk;   
  TAdjustSelfThunk = packed record     
    case AddInstruction: longint of       
      AddByte : (AdjustmentByte: shortint);       
      AddLong : (AdjustmentLong: longint);   
    end;   
  PInterfaceMT = ^TInterfaceMT;   
  TInterfaceMT = packed record     
    QueryInterfaceThunk: PAdjustSelfThunk;   
  end;   
  TInterfaceRef = ^PInterfaceMT; 
var   
  QueryInterfaceThunk: PAdjustSelfThunk; 
begin   
  Result := Pointer(I);   
  if Assigned(Result) then     
    try       
      QueryInterfaceThunk := TInterfaceRef(I)^.QueryInterfaceThunk;       
      case QueryInterfaceThunk.AddInstruction of         
        AddByte: Inc(PChar(Result), QueryInterfaceThunk.AdjustmentByte);         
        AddLong: Inc(PChar(Result), QueryInterfaceThunk.AdjustmentLong);         
      else     
        Result := nil;       
      end;     
    except       
      Result := nil;     
    end; 
end;

Function AskQuestion(Msg: String): Boolean;
Begin
  Result := MessageDlg(Msg, mtConfirmation, [mbYes,mbNo], 0)=mrYes;
End;

Procedure TForm1.ConcurrentApplyUpdates;
Var
  lConcurrentError, B: Boolean;
Begin
  lConcurrentError := True;
  While lConcurrentError Do
  Begin
    Try
      ZQuery1.ApplyUpdates;
      lConcurrentError := False;
    Except
      On E: EZSQLException Do
        If E.ErrorCode<>2802{2802 is the code of exception (if ValidateUpdateCount and UpdatedRecordCount<>1)} Then // LastModifiedTime different
          Raise;

      On E: Exception Do
        Raise;
    End;

    If lConcurrentError Then
      With TZAbstractCachedResultSetAccess(GetImplementingObject(TZAbstractDatasetAccess(ZQuery1).CachedResultSet)) Do
      Begin
        If AskQuestion(Format(
            'Record %s: %s %s modified by another person.'+sLineBreak+
            'Would you like to overwrite it and so erase data modified by this other person ?',
            [OldRowAccessor.GetString(2, B), OldRowAccessor.GetString(3, B), OldRowAccessor.GetString(4, B)])) Then
          OldRowAccessor.SetNull(5)
        Else
        Begin
          If Not AskQuestion(Format(
            'Would you like to cancel modifications of %s: %s %s', [OldRowAccessor.GetString(2, B), OldRowAccessor.GetString(3, B), OldRowAccessor.GetString(4, B)])) Then
            Raise Exception.Create('Saving data process canceled by user.');

          // The data modified here are canceled => Cached update in queue is removed:
          // Delete the cached update
          OldRowAccessor.Dispose;
          InitialRowsList.Delete(0);
          CurrentRowsList.Delete(0);
        End;
      End;
  End;
End;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ConcurrentApplyUpdates;
end;

procedure TForm1.ZQuery1BeforePost(DataSet: TDataSet);
begin
  DataSet.FieldByName('LastModified').AsDateTime := Now;
end;

The LastModified field should configured null by default.
Here the SQL modify query:

Code: Select all

UPDATE planning SET
  planning.Nom = :Nom,
  planning.LastModified = :LastModified
WHERE
  (planning.Planning_ID = :OLD_Planning_ID) and
  ((:OLD_LastModified Is Null) Or
   (planning.LastModified = :OLD_LastModified))

And the property for the TZQuery:

Code: Select all

ValidateUpdateCount=YES

David

ddantoni · Post by **ddantoni** » 04.05.2008, 18:55

I wrote a component that execute this kind of update.
The source code is here:

Code: Select all

Unit hxdZConcurrentUpdate;

{
  Modification to do ZSqlUpdate:
    Procedure TZUpdateSQL.PostUpdates(Sender: IZCachedResultSet;
            UpdateType: TZRowUpdateType; OldRowAccessor, NewRowAccessor: TZRowAccessor);

    if ExecuteStatement then
    begin
      //Statement.ExecutePrepared;
      lValidateUpdateCount := StrToBoolEx(
        Sender.GetStatement.GetParameters.Values['ValidateUpdateCount']);

      lUpdateCount := Statement.ExecuteUpdatePrepared;
      if  (lValidateUpdateCount) and (lUpdateCount <> 1) then
        raise EZSQLException.CreateWithCode(2802, Format('%d record(s) updated. Only one record should have been updated.', [lUpdateCount]));

  ***********************************

  Example of Update query:
    UPDATE planning SET
    planning.Nom = :Nom,
    planning.LastModified = :LastModified
  WHERE
    (planning.Planning_ID = :OLD_Planning_ID) and
    ((:OLD_LastModified Is Null) Or
     (planning.LastModified = :OLD_LastModified))

  To overwrite, use OldRowAccessor.SetNull for
  set OLD_LastModified to Null and set Action to Retry.
}

Interface

Uses Classes, SysUtils, ZDbcIntfs, ZDbcCache,
     ZAbstractDataset, ZDbcCachedResultSet;

Type
  TZAbstractDatasetAccess = Class(TZAbstractDataset);
  TZAbstractCachedResultSetAccess = Class(TZAbstractCachedResultSet);

  ThxdDbRecordChangedAction = (dbrcaRetry, dbrcaCancelThisUpdate, dbrcaStopUpdates);
  ThxdDbRecordChangedEvent = Procedure(Sender: TObject; OldRowAccessor: TZRowAccessor; Var Action: ThxdDbRecordChangedAction) Of Object;

  ThxdZConcurrentUpdate = Class(TComponent)
  Private
    FzDataset: TZAbstractDataset;
    FRefrechIfDbRecordsChanged: Boolean;
    FOnDbRecordChanged: ThxdDbRecordChangedEvent;

    Procedure SetZDataSet(AzDataset: TZAbstractDataset);
  Protected
    Procedure Notification(AComponent: TComponent; Operation: TOperation); override;
  Public
    Constructor Create(AOwner : TComponent); override;
    Destructor Destroy; override;

    Procedure ApplyUpdates;
  Published
    Property zDataset: TZAbstractDataset Read FzDataset Write SetZDataSet;
    Property RefrechIfDbRecordsChanged: Boolean Read FRefrechIfDbRecordsChanged Write FRefrechIfDbRecordsChanged Default True;
    Property OnDbRecordChanged: ThxdDbRecordChangedEvent Read FOnDbRecordChanged Write FOnDbRecordChanged;
  End;

Implementation

// http://hallvards.blogspot.com/2004/07/hack-7-interface-to-object-in-delphi.html
function GetImplementingObject(const I: IInterface): TObject;
const   
  AddByte = $04244483;  
  AddLong = $04244481;  
type   
  PAdjustSelfThunk = ^TAdjustSelfThunk;   
  TAdjustSelfThunk = packed record     
    case AddInstruction: longint of       
      AddByte : (AdjustmentByte: shortint);       
      AddLong : (AdjustmentLong: longint);   
    end;   
  PInterfaceMT = ^TInterfaceMT;   
  TInterfaceMT = packed record     
    QueryInterfaceThunk: PAdjustSelfThunk;   
  end;   
  TInterfaceRef = ^PInterfaceMT; 
var   
  QueryInterfaceThunk: PAdjustSelfThunk;
begin   
  Result := Pointer(I);
  if Assigned(Result) then
    try       
      QueryInterfaceThunk := TInterfaceRef(I)^.QueryInterfaceThunk;
      case QueryInterfaceThunk.AddInstruction of
        AddByte: Inc(PChar(Result), QueryInterfaceThunk.AdjustmentByte);
        AddLong: Inc(PChar(Result), QueryInterfaceThunk.AdjustmentLong);         
      else     
        Result := nil;       
      end;     
    except       
      Result := nil;     
    end; 
end;

{$REGION 'Constructor / Destructor'}
Constructor ThxdZConcurrentUpdate.Create(AOwner : TComponent);
Begin
  Inherited Create(AOwner);
  FzDataset := Nil;
  FRefrechIfDbRecordsChanged := True;
  FOnDbRecordChanged := Nil;
End;

Destructor ThxdZConcurrentUpdate.Destroy;
Begin
  zDataset := Nil;
  Inherited Destroy;
End;
{$ENDREGION}

Procedure ThxdZConcurrentUpdate.Notification(AComponent: TComponent; Operation: TOperation);
Begin
  Inherited Notification(AComponent, Operation);
  If (Operation=opRemove) And (AComponent=FzDataset) Then
    zDataset := Nil;
End;

Procedure ThxdZConcurrentUpdate.SetZDataSet(AzDataset: TZAbstractDataset);
Var I: Integer;
Begin
  If FzDataset<>AzDataset Then
  Begin
    If FzDataset<>Nil Then FzDataset.RemoveFreeNotification(Self);
    FzDataset := AzDataset;
    If FzDataset<>Nil Then
    Begin
      FzDataset.FreeNotification(Self);
      FzDataset.CachedUpdates := True;

      With TZAbstractDatasetAccess(FzDataset).Properties Do
      Begin
        I := IndexOfName('ValidateUpdateCount');
        If I<0 Then
          Add('ValidateUpdateCount=YES')
        Else If ValueFromIndex[I]<>'YES' Then
          ValueFromIndex[I] := 'YES';
      End;
    End;
  End;
End;

Procedure ThxdZConcurrentUpdate.ApplyUpdates;
Var
  lConcurrentError, DbRecordChanged: Boolean;
  Action: ThxdDbRecordChangedAction;
Begin
  If Not Assigned(FzDataset) Then
    Raise Exception.Create('ZDataset not defined.');
  If Not FzDataset.CachedUpdates Then
    Raise Exception.Create('ZDataset.CachedUpdates should be to true.');
  If Not Assigned(FOnDbRecordChanged) Then
    Raise Exception.Create('OnDbRecordChanged event not defined.');

  DbRecordChanged := False;
  lConcurrentError := True;
  While lConcurrentError Do
  Begin
    Try
      FzDataset.ApplyUpdates;
      lConcurrentError := False;
    Except
      On E: EZSQLException Do
        If E.ErrorCode<>2802{2802 is the code of exception (if ValidateUpdateCount and UpdatedRecordCount<>1)} Then // LastModifiedTime different
          Raise;

      On E: Exception Do
        Raise;
    End;

    If lConcurrentError Then
      With TZAbstractCachedResultSetAccess(GetImplementingObject(TZAbstractDatasetAccess(FzDataset).CachedResultSet)) Do
      Begin
        Action := dbrcaCancelThisUpdate;
        FOnDbRecordChanged(Self, OldRowAccessor, Action);

        Case Action Of
          //dbrcaRetry :
            // To overwrite, use OldRowAccessor.SetNull for set OLD_LastModified to Null

          dbrcaCancelThisUpdate :
          Begin
            DbRecordChanged := True;
            OldRowAccessor.Dispose;
            InitialRowsList.Delete(0);
            CurrentRowsList.Delete(0);
          End;

          dbrcaStopUpdates :
            Exit;
        End;
      End;
  End;

  If FRefrechIfDbRecordsChanged And DbRecordChanged Then
    FzDataset.Refresh;
End;

END.

Example of use (Messages are in french) :

Code: Select all

Procedure TFrmMain.zcuPlanningDbRecordChanged(Sender: TObject;
  OldRowAccessor: TZRowAccessor; var Action: ThxdDbRecordChangedAction);
Var B: Boolean;
Begin
  With OldRowAccessor Do
    Case RowBuffer.UpdateType Of
      utInserted :
      Begin
        If PluginInst.hxdMsgMan.AskQuestion(MsgTitle, Format(
          'Le rendez-vous de %s: %s %s a été modifié par quelqu''un d''autre.'+sLineBreak+
          'Voulez-vous écrire par dessus et ainsi effacer les modifications faites par cette autre personne ?',
              [GetString(4, B), GetString(5, B), GetString(6, B)]))=hmrYes Then
        Begin
          SetNull(15);
          Action := dbrcaRetry;
        End
        Else If PluginInst.hxdMsgMan.AskQuestion(MsgTitle, Format(
          'Voulez-vous annuler les modifications que vous avez faites sur le'+sLineBreak+
          'rendez-vous de %s: %s %s', [GetString(4, B), GetString(5, B), GetString(6, B)]))=hmrNo Then
          Action := dbrcaStopUpdates
        Else
          Action := dbrcaCancelThisUpdate;
      End;

      utDeleted :
      Begin
        If PluginInst.hxdMsgMan.AskQuestion(MsgTitle, Format(
          'Le rendez-vous de %s: %s %s a été modifié par quelqu''un d''autre.'+sLineBreak+
          'Voulez-vous quand même supprimer l''entrée planning correspondante ?',
              [GetString(4, B), GetString(5, B), GetString(6, B)]))=hmrYes Then
        Begin
          SetNull(15);
          Action := dbrcaRetry;
        End
        Else If PluginInst.hxdMsgMan.AskQuestion(MsgTitle, Format(
          'Voulez-vous annuler la suppression du '+sLineBreak+
          'rendez-vous de %s: %s %s', [GetString(4, B), GetString(5, B), GetString(6, B)]))=hmrNo Then
          Action := dbrcaStopUpdates
        Else
          Action := dbrcaCancelThisUpdate;
      End;
    End;
End;

David