Page 1 of 1
Concurrent access and data overwrite protection
Posted: 17.04.2008, 23:07
by ddantoni
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
Posted: 17.04.2008, 23:31
by mdaems
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
Posted: 18.04.2008, 02:03
by cipto_kh
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?
Posted: 19.04.2008, 14:58
by ddantoni
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
Posted: 19.04.2008, 17:34
by ddantoni
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
Posted: 25.04.2008, 20:33
by ddantoni
Nobody to help me ?
Thanks in advance.
David
Posted: 28.04.2008, 20:42
by ddantoni
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:
David
Posted: 04.05.2008, 18:55
by ddantoni
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