Blocking lock being caused on Database, Isolation level?

[rwerning]

I'm trying to narrow down a problem we're seeing now that we've upgraded our application to Delphi XE, and using the latest (4.75.27) dbexpsda40 driver, connected to SqlServer 2008.

In a TDatasetProvider.AfterUpdateRecord event, we're doing a query on the table that is being updated (need to perform an insert/update elsewhere). This design worked fine before we updated from Delphi 2007 & dbexpsda40 4.55.23. This still works in Oracle - we need to support both database types - I tested it and it works without a problem.

If I remove the query (select * from ) against the table that is being updated in the afterupdaterecord, the blocking lock is gone. If I debug the code and set a break point in the AfterUpdateRecord, before the select query is executed, and run a simple select against the table from another application on another PC, that too locks. So it has to do with the lock being applied to the table.

I believe it has to do with the Isolation level, but I'm not exactly sure what to do at this point. I'd rather change it on the driver level if possible. If we need to change the code pattern it'll be tough as we have a very large application that would need to be reviewed and changed. However I believe that there are bigger implications if we change the isolation level on the database.

So I suppose what I'm looking for here is any advice, thoughts, comments or links to more info that anyone might have, to help me decide how to resolve this issue.

Thanks
Rich Werning
TIP Technologies, Inc.

[AndreyZ]

Hello,

The point is that dbExpress uses transactions when executing ApplyUpdates. And SQL Server locks the tables that are modified during transaction. To solve the problem you should use the same connection in the AfterUpdateRecord event in the following way:

Code: Select all

procedure TMainForm.BitBtnOpenClick(Sender: TObject);
begin
  SQLQuery.SQLConnection := SQLConnection;
  SQLQuery.SQL.Text := 'select * from your_table';
  DataSetProvider.DataSet := SQLQuery;
  ClientDataSet.ProviderName := 'DataSetProvider';
  ClientDataSet.Open;
end;

procedure TMainForm.BitBtnSaveClick(Sender: TObject);
begin
  ClientDataSet.ApplyUpdates(0);
end;

procedure TMainForm.DataSetProviderAfterUpdateRecord(Sender: TObject;
  SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind);
var
  q: TSQLQuery;
begin
  q := TSQLQuery.Create(nil);
  try
    q.SQLConnection := SQLConnection;
    q.SQL.Text := 'select * from your_table';
    q.Open;
    //your logic
    q.Close;
  finally
    q.Free;
  end;
end;