Hi,
I've been using Delphi and MSSQL for three years now. Currently I've been
using Delphi 3 and MSSQL 6.5. I desperately wish to get rid of my locking
problem.

I use Tables and Queries to connect to the database.

The Tables have a Database.StartTransaction on Before Open and Before Post
events.
The Tables have a Database.StartCommit on After Open and After Post events.

All my SQL Statements use (NOLOCK) and have a BEGIN TRAN and COMMIT TRAN in
it. But I have Active Table Locks when I check my SQL Server. I guess Delphi
is placing those locks on the Table when the Table.Active is set to true
because it trys to place a lock on the Table until the entire Table has been
cached (so I've heard).

Anyway, when I try to update the Table my application locks up and when I
look in Enterprise Manager a Select lock blocks the Update Lock. I cannot
Kill the Select Lock. This doesn't happen each and every time but happens
like 80% of the time.

When I look into the details of the Active Lock, the SQL statement looks
some thing like this.

"Select column1, column2, column3, colu "

It is not complete but is active.

I hope I've made my problem clear.

Does any one out there have this problem? If so how did you solve it??

Thank you very much and sorry for the trouble.

Kannan Raj

Hi.

I ran into the same problem on SqlServer 6.5, even though I don't use
TTables at all, but I do use cached updates on TQuerys and do a
FetchAll after TQuery.Open.  "NoLock" optimiser hints didn't help.

In my case it turned out that it was the instance of the TQuery that I
had set Active = True <b>in the IDE</b> which was causing the blocking
lock - if I set the TQuery Active = False in the IDE and re-loaded the
project, the problem didn't occur.

So, what I did in the end - and which seems, touch wood, to work - is
to derive a TQuery descendant (see below).  The reason for the
not-very-elegant Loaded override is that without it, if the Query is
saved with Active = True, when the IDE next loads it, it gets opened,
but by <b>bypassing</b> my SetActive.  So, my Loaded makes sure that
FetchAll gets called, even for the query instance in the IDE.

HTT, Martyn

type
  TmQuery = Class(TQuery)
    protected
    FFetchAllOnOpen : Boolean;
    public
    function GetActive : Boolean;
    procedure SetActive(NewValue : Boolean);
    procedure Open;
    procedure ApplyUpdates;
    procedure Loaded; override;
    published
    property Active : Boolean read GetActive write SetActive;
    property FetchAllOnOpen : Boolean read FFetchAllOnOpen write
FFetchAllOnOpen;
  end;

implementation

function TmQuery.GetActive : Boolean;
begin
  Result := inherited Active;
end;

procedure TmQuery.Loaded;
begin
  inherited Loaded;
  if Active then begin
    Close;
    Open;
  end;
end;

procedure TmQuery.Open;
begin
  Active := True;
end;

procedure TmQuery.SetActive(NewValue : Boolean);
begin
  if Active = NewValue then Exit;
  if NewValue and FetchAllOnOpen then
    DisableControls;

  inherited Active := newValue;

  if Active and FetchAllOnOpen then begin
    FetchAll;
    First;
    EnableControls;
  end;
end;

Hey, this not only your problem but guess it concern Microsoft  who  not
introduce multigeneration database conception yet that eliminate any prgrammer's
troubles about any lock: just do:
   BEGIN TRANSACTION
     ...... you transaction details....
   COMMIT

And that's ALL!!!!! ( like in IB )
It's too trivial but I think too hard such monster as MS!!!

But nevertherless ( while MS is thinking  what to do ) see in this conf subject
MS SQL DEADLOCK on 22.12.98.

Good luck!

Alexander Kozlov
sa...@nalog.east-suberia.ru