Solved

Help! blocking problem! BDE-SQL_SELECT

Posted on 2000-03-02
25
871 Views
Last Modified: 2013-11-23
Hi, HELP!

we are facing a VERY VERY serious problem that
  some insert/update statements passed to M$-SQL server are blocked in high frequency.
After analysis, we found that the SQL server will perform an INTEND-SHARE lock at once
  on the table[assume with LARGE size] that a TTABLE associated
  once the TTABLE is active. (will not happen on small tables)
We trace the SQL execution and found that
  BDE will generate a SQL select statement and pass it to SQL server for execution
  but never end because it did NOT receive all records
  til a TTable.Refresh or TTable.Close;
 Hence, sometimes, some updates on the table unfortunately were blocked.
 At this moment, we have to set some guidelines : such as,
   always append Table1.Refresh after Table1.Open at once.
   but it just can reduce the frequence.
 I knew change TDS PACKET SIZE to highest value can reduce the frequence
   but some tables are in VERY large size(> max(TDS Packet Size))
Any advices/comments are appreciated.
  (MORE points are being prepared.)
0
Comment
Question by:yk030299
  • 11
  • 5
  • 3
  • +4
25 Comments
 
LVL 6

Expert Comment

by:DrDelphi
ID: 2578954
Hi.
  I am working right now on a project in which I have to either update or delete records from a HUGE MSSQL database. What I ended up doing to make the process run through with as little lag time as possible was to iterate through the table comparing record to record, inserting and/or modifying where appropriate. I am using the ADO Query components to avoid any BDE lags. So far, I am able to process about 40,000 14 field records in about 13 minutes. I don't know if  this helps you or not. I just mention it because I know where you coming from. <g>



-Good luck!!
0
 
LVL 1

Author Comment

by:yk030299
ID: 2579230
hi, Dr.Delphi!

Thank you very much. :-)
I knew that U remembered I raised the problem 1 month before. [AutoDeleted]
I knew using ADO can aboid BDE lags.
I think you remember that
I don't want to modify all old projects designed with BDE.

Do you know which DBMS can work with BDE without this problem?
[maybe IntraBase, I did not fully test it]
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2579551
hi yk,

this found at borland

--- paste begin


Question and Answer Database

    FAQ200B.txt   Avoiding server side locking (including DEADLOCK)
    Category   :Database (MSSQL)
    Platform    :All
    Product    :Applies to all  

    Question:
    What can I do to help avoid server side locking (including DEADLOCK)
    problems when working with Microsoft SQL Server (DBLIB) and Sybase
    SQL Server (DBLIB)?

    Answer:
    The following suggestions may help you tune your application and
    server.  The server side suggestions may not apply to all server
    and database installations.

    From the client application you may want to take greater control
    over the size of results sets (this may mean using TQueries),
    minimize the length of transactions (usually not an issue if the
    SQLPASSTHRU MODE is set to ...AUTOCOMMIT), and only open dbaware
    controls when necessary to help minimize resource drain on the
    server and, possibly in this case, deadlock potential.

    The means by which the BDE selects data from each of the supported
    servers does not make assumptions about how each server chooses
    to ensure data integrity.

    Delphi/BDE suggestions:

    Work with smaller result sets (TQueries, server views, etc.)
    also see above form more info.
    Check the SQL Links MSSQL Driver "TDS Packet Size" param
    making sure that it is set to, at least, 4096.
    Minimize the length of transactions.
    Investigate creating appropriate indexes.
    Filter results before opening a dataset or use tqueries
    (live or otherwise) to limit the number of rows selected.
    Investigate using the BDE SQL PASSTHRU MODE parameter
    "NOT SHARED" (please see BDEADMIN.HLP and BDE32.HLP for
    addtional information on the SQL PASSTHRU MODE parameter)

    Please note:
      BDE/SQL Links 4.01 will not only detect and raise a deadlock
      error but it will "reset" its database transaction state when
      it detects an MSSQL error 1205. It is not necessary to rollback
      the explicit transaction (Database1.rollback) after the
      deadlock has been detected.

      The error 1205 signals to the client that the server has
      "resolved" a deadlock and chosen one of the users to end the
      deadlock. This user's transaction is automatically rolled back.
      Please refer to the MS SQL Server documentation for more
      information on deadlock detection and server error 1205.

    MS SQL and Sybase Server topics: (the following is by no means a
    comprehensive list. Please check your Sybase and MS SQL Server
    docs for tips on optimizing your server and databases)

    Create indexes on the remote tables where possible (the
    server may require more locks for unindexed tables.)
    TEXT and IMAGE columns can take up more pages (columns
    can be omitted from a SELECT statement if working with TQueries
    whose REQUEST LIVE property is false)
    page sizes on the server can be adjusted to better match
    expected row sizes (this can help prevent the server from locking
    adjacent rows.)
    The server will create a table lock if the LOCK ESCALATION
    level is reached (part of sp_configure)

    Please also see:
      MS SQl Server documentation (printed or Books Online)
      If using TQueries:
        TABLOCKX
        UPDLOCK

    For more information on the options above:
      Analyzing locks Topic
      (also see Database Developer's Companion Errata)

--- paste end

may it help you

meikl
0
 

Expert Comment

by:mrissmann
ID: 2585483
Interbase works great. I don't really understand your problem but we have been using interbase C/S for 4 years now and haven't had any problems.  We are switching over to SQL 7 and have been experiencing timeouts and such.

Interbase is such a forgiving Database and very simple to.  If you try it use ver 5.6 or greater or 5.4 or less.  5.5 was a horrible release.

Any questions,

Feel free to ask.

Mark Rissmann
0
 
LVL 1

Author Comment

by:yk030299
ID: 2586302
hi, everybody!
I think your information is quite valuable.
thank you very much!
Any advices/comments are strongly appreciated.

hi Meikl, let me read your comment carefully.

hi Mark, my problem is that there are blocking while more than 1 applications associated via BDE to the same table[ in LARGE size] and some want to update records in this table.

I am still digging in it. :-(
0
 

Expert Comment

by:mrissmann
ID: 2586545
I am pretty sure that Interbase does not have a problem with this.  We have heard of very few locks happenning to users.

Are you using SQL7?  Any advice for us that are moving on to SQL7?

Mark
0
 
LVL 1

Author Comment

by:yk030299
ID: 2586561
For solving this blocking problem, we bought SQL7 but it still exists.
So that we did not move data at once because we must test if it conflited with our existing program.
Anyways, SQL7 should be better than SQL65 because we can use Row-lock in SQL7 and Unicode string type althought BDE doesnot support now!

Could you tell me how IntraBase 55 is?
What happened when using?
Thanks
0
 

Expert Comment

by:mrissmann
ID: 2588842
The main problem that we had with the 5.5 version was database corruption.

There is a free upgrade available for 5.6:

http://www.interbase.com/downloads/summaries/72.html

Mark Rissmann
0
 
LVL 1

Author Comment

by:yk030299
ID: 2590149
hi meikl, Could you tell me what is the unit of [TDS packet size]? Is it in Byte?

Hi Mark, let me try the ib56
0
 

Expert Comment

by:zb_russia
ID: 2590573
:)

You can purchase Interbase, Oracle, e.t.c. - but while you will work with TTable for want of reversion to sql server to data bases - for you there will be problems. I work with MS SQL 6.5 already more than 4 years. By blood and then born skill - only TQuery, only stored procedure.

Not look on announced new possibilities in MS SQL 7, it all the same is block server and the use TTable will generate and to generate problems. Infortunately, unique, that I can suggest you is to look on new projecting of "critical" plots of a system (if it still probably).
0
 

Expert Comment

by:mrissmann
ID: 2592675
InterBase has very little problems pertaining to TTables in Interbase.
TTables are a perfect match for Interbase.  Don't forget Borland wrote Interbase and Borland wrote the BDE and TTables so they are going to work.  Borland did not write SQL Server thus they don't know the ins/outs of SQL Server.

Interbase is now open source and also Delphi Client/Server ships with Interbase so if you have it you don't have to purchase it.

Mark Rissmann
0
 
LVL 1

Author Comment

by:yk030299
ID: 2594209
:) using IB may avoid this problem.
:( but it is difficult for me to persuade my boss to change it because we just bought the new SQL7 and we didnot use IB before.

I am still waiting for more helps.
I prepared points for all helpers.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Expert Comment

by:mrissmann
ID: 2594685
So,  are you having this problem in SQL7?  If so, can you give me a little app based on the demo database that comes with SQL7 and I can see what I can do.

Mark Rissmann
0
 
LVL 1

Author Comment

by:yk030299
ID: 2598674
hi, Mark!

It is so difficult for me to transfer my large table.
To simulate my case, you can create a table [e.g. named as T(id integer,name varchar(128))] and insert records in large amount[E.g. ~100000].

Open a project, just drop a TDatabase & a TTable/TQuery[select * from T] associated to the Table T, also drop a TDatasource to associate the TTable/Tquery and TDBGrid to the Datasource.

During my testing, I run 2 instances of this program at the same time.
actually, the share-lock of the Table T has already existed once 1 instance is running or opened in design-time. [check by sp_who/sp_lock].

Thanks
0
 

Expert Comment

by:Chelly_the_dog
ID: 2601194
I think the BDE opens a Cursor to the database blocking all other traffic until you fetch all data from the table.
It is not enough to open the table or query, you have to force a FetchAll
Statement, otherwise the BDE keeps the Cursor open !
You can also avoid this problem by decresing the number of records to fetch to the number that can be displayed in the grid at a time or perform a "last" command.
The BDE fetches only a few datasets at a time cause it would take a while to get all data from the database.
I dont think this is a spcific problem of MS SQL, but i am not sure !
But of course you shold never use a TTable Control to work with so much records ! Always use TQuery and of course never fetch so much records at all, who will read or edit them ?
0
 
LVL 1

Author Comment

by:yk030299
ID: 2603102
hi Chelly,
Thank you for joining this problem.
Before I evaluate your answer, I'd like to make sure I did not miss your idea.
1. When should I force a FetchAll Statement? when I bind a TDBedit to the TTable/Tquery, it may position the cursor to the current record.
2. any sugguest to decresing the no. of records to fetch ...
When should I perform a "last"?
3. >>who will read or edit them ?
SomeUsers only append data to the table;
   SomeUsers only update partial attributes(fields) of this table in some conditions, one by one.
   SomeUsers only run the statistical reports based on the records.

(:-) I prepared at least 1k points)
0
 

Expert Comment

by:Chelly_the_dog
ID: 2603781
Hi yk,
1. When should I force a FetchAll Statement?

Directly after the open Statement, because the BDE keeps a open Connection to the SQL Server until all records are fetched ore you execute a Close statement.
This is called open cursor on the database and has nothing to do with the cursor on your application. It is BDE stuff and is handled via Callbacks

2. any sugguest to decresing the no. of records to fetch ...

Use SQL with a TQuery !
Never do such things like "Select * From Table"
ALWAYS use "Select Name, Adress, age, sex from Table where CustomerId > 1234567 and InvoiceDate > '1.1.2000' and ............" inm tables with lots of records !
I know, depending on what to display this is much more work, but you have something you can call performance !

When should I perform a "last"?

Good luck !

This should do the same as "Fetchall" but try both !

Var TempQuery : TQuery

Begin
  TempQuery := TQuery.Create(self);
  TempQuery.SQL.Add('Select..........');
  TempQuery.Open;
  TempQuery.Last;
  TempQuery.FetchAll;
  { And now connect your DataSource and your Grid and whatever !
    This is the best method, but of course you dont have to do this all at         runtime}
end;
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2603822
? may this solve the initially problem ?
0
 
LVL 1

Author Comment

by:yk030299
ID: 2622814
Adjusted points from 500 to 600
0
 
LVL 1

Author Comment

by:yk030299
ID: 2622815
Sorry! it don;t work!
0
 
LVL 1

Author Comment

by:yk030299
ID: 2622825
when I want to use the DBgrid, I can't assign some filter critirias for the where clause! :-(
so that FetchAll causes waiting to receive all records.

Are there any method to abort the sql-executing if I set time-out=5 seconds.
0
 
LVL 1

Expert Comment

by:DValery
ID: 2699070
Hi, yk
I'd recomend you use TQuery and everywhere in SELECT use
(NOLOCK) option like:

SELECT * FROM GOODS (NOLOCK)

You may to use TTable on SQL-View? where also use (NOLOCK)

0
 
LVL 1

Author Comment

by:yk030299
ID: 2707164
hi DValery,
Thanks for you joining.
We had already applied this kind of approach.
But the problem is that we have to code for each insert/update/delete statement for each TDataset.
It really is a heavy job. :-(
That is what I did. Any comments on it? TIA!

const sql1='select * from T_ASSIGNMENT';

procedure TForm1.BitBtnOpenClick(Sender: TObject);
begin
  wwTable1.Query.text:=sql1+' (no lock)';
  wwDataSource1.DataSet.Open;
end;
procedure TForm1.BitBtnCloseClick(Sender: TObject);
begin
  wwDataSource1.DataSet.Close;
end;
procedure TForm1.OpenMyTable(IsLive:boolean);
var i:integer;
var cond:string;
var isStr:boolean;
var IsNullField:boolean;
var IsPrimaryKey:boolean;
var SavePlace: TBookmark;
begin
  if IsLive then begin
    with wwQueryForEdit do begin
      Close;
      cond:=' WHERE ';
      for i:=0 to wwTable1.Fields.Count-1 do begin
        IsNullField:=wwTable1.Fields[i].IsNull;
        IsPrimaryKey:=wwTable1.Fields[i].IsIndexField; //PrimaryKey Should be
//        if IsPrimaryKey=false then continue;
        IsStr:=wwTable1.Fields[i].DataType in
        [ftString, ftDate, ftTime, ftDateTime,
         ftBytes, ftVarBytes, ftBlob, ftMemo,
         ftGraphic, ftFmtMemo, ftParadoxOle,
         ftDBaseOle, ftTypedBinary, ftFixedChar,
         ftWideString,  ftADT];
        if isnullfield=true then begin
          cond:=cond+wwTable1.Fields[i].FieldName+'=';
          cond:=cond+'null'
        end else begin
          cond:=cond+wwTable1.Fields[i].FieldName+'=';
          if IsStr=true then cond:=cond+'''';
          cond:=cond+wwTable1.Fields[i].AsString;
          if IsStr=true then cond:=cond+'''';
        end;
        cond:=cond+' and ';
      end;
      if length(cond)>12 then cond:=copy(cond,1,length(cond)-5);
      SQL.Text:=(sql1+cond);

Memo1.Lines.Text:=(sql1+cond);

      Open;
    end;
    wwDataSource1.DataSet:=wwQueryForEdit;
  end else begin
    with wwTable1 do begin
//      Refresh;
      SavePlace := GetBookmark;
      Close; Open;
      GotoBookmark(SavePlace);  //Wrong Position when RecordCount changed
    end;
    wwDataSource1.DataSet:=wwTable1;
    wwQueryForEdit.Close;
  end;

  if (wwDataSource1.DataSet is TQuery) then begin
    Memo1.Lines.Text:=(wwDataSource1.DataSet AS TQuery).SQL.Text;
  end else begin (*wwDataSource1.DataSet is TwwTable*)
    Memo1.Lines.Text:=(wwDataSource1.DataSet AS TwwTable).Query.Text;
  end;
end;
0
 

Expert Comment

by:Chelly_the_dog
ID: 2707262
Hey yk,
I'am back from holiday and it seems, that you're still facing the old problem !?
Maybe you should mail me a example-project, so I can help you fix your problems. I dont really understand the sense of the piece of code you've published.
You are using these Infopower Comps, eh ? What Version ?

Ciao, Chelly
0
 
LVL 1

Accepted Solution

by:
DValery earned 600 total points
ID: 2707291
Hi, yk

Here my sample with corrections ;o)

---------------cut------------------
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, StdCtrls, Buttons, Grids, DBGrids;

type
  TForm1 = class(TForm)
    BitBtnOpen: TBitBtn;
    BitBtnClose: TBitBtn;
    wwTable1: TQuery;
    wwQueryForEdit: TQuery;
    wwDataSource1: TDataSource;
    Memo1: TMemo;
    DBGrid1: TDBGrid;
    BitBtnEdit: TBitBtn;
    Database1: TDatabase;
    procedure BitBtnOpenClick(Sender: TObject);
    procedure BitBtnCloseClick(Sender: TObject);
    procedure OpenMyTable(IsLive:boolean);
    procedure BitBtnEditClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

const sql1='select * from T_ASSIGNMENT';

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.BitBtnOpenClick(Sender: TObject);
begin
     wwDataSource1.DataSet := wwTable1;
     wwTable1.SQL.text:= sql1+' (nolock) ';
     wwDataSource1.DataSet.Open;
end;

procedure TForm1.BitBtnCloseClick(Sender: TObject);
begin
     wwDataSource1.DataSet.Close;
end;

procedure TForm1.OpenMyTable(IsLive:boolean);
var i:integer;
var cond:string;
var isStr:boolean;
var IsNullField:boolean;
var IsPrimaryKey:boolean;
var SavePlace: TBookmark;
begin
  if IsLive then begin
    with wwQueryForEdit do begin
      Close;
      cond:=' WHERE ';
      for i:=0 to wwTable1.Fields.Count-1 do begin
        IsNullField:=wwTable1.Fields[i].IsNull;
        IsPrimaryKey:=wwTable1.Fields[i].IsIndexField; //PrimaryKey Should be
//        if IsPrimaryKey=false then continue;
        IsStr := wwTable1.Fields[i].DataType in
        [ftString, ftDate, ftTime, ftDateTime,
         ftBytes, ftVarBytes, ftBlob, ftMemo,
         ftGraphic, ftFmtMemo, ftParadoxOle,
         ftDBaseOle, ftTypedBinary, ftFixedChar,
         ftWideString,  ftADT];
        if isnullfield then begin
          cond:=cond + wwTable1.Fields[i].FieldName + ' is null';
        end else begin
          cond:=cond+wwTable1.Fields[i].FieldName+'=';
          if IsStr=true then cond:=cond+'''';
          cond:=cond+wwTable1.Fields[i].AsString;
          if IsStr=true then cond:=cond+'''';
        end;
        cond:=cond+' and ';
      end;
      if length(cond)>12 then cond:=copy(cond,1,length(cond)-5);
      SQL.Text:=(sql1+' (nolock) ' + cond);

      Memo1.Lines.Text:=(sql1+cond);

      Open;
    end;
    wwDataSource1.DataSet:=wwQueryForEdit;
  end else begin
    with wwTable1 do begin
//      Refresh;
      SavePlace := GetBookmark;
      Close; Open;
      GotoBookmark(SavePlace);  //Wrong Position when RecordCount changed
    end;
    wwDataSource1.DataSet:=wwTable1;
    wwQueryForEdit.Close;
  end;

  if (wwDataSource1.DataSet is TQuery) then begin
    Memo1.Lines.Text:=(wwDataSource1.DataSet AS TQuery).SQL.Text;
  end else begin (*wwDataSource1.DataSet is TwwTable*)
    Memo1.Lines.Text:=(wwDataSource1.DataSet as TQuery).SQL.Text;
  end;
end;


procedure TForm1.BitBtnEditClick(Sender: TObject);
begin
     OpenMyTable(True);
end;

end.
---------------cut------------------
object Form1: TForm1
  Left = 185
  Top = 125
  Width = 607
  Height = 453
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object BitBtnOpen: TBitBtn
    Left = 8
    Top = 6
    Width = 75
    Height = 25
    Caption = 'BitBtnOpen'
    TabOrder = 0
    OnClick = BitBtnOpenClick
  end
  object BitBtnClose: TBitBtn
    Left = 181
    Top = 7
    Width = 75
    Height = 25
    Caption = 'BitBtnClose'
    TabOrder = 1
    OnClick = BitBtnCloseClick
  end
  object Memo1: TMemo
    Left = 8
    Top = 286
    Width = 585
    Height = 132
    TabOrder = 2
  end
  object DBGrid1: TDBGrid
    Left = 8
    Top = 38
    Width = 585
    Height = 241
    DataSource = wwDataSource1
    TabOrder = 3
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object BitBtnEdit: TBitBtn
    Left = 96
    Top = 6
    Width = 75
    Height = 25
    Caption = 'BitBtnEdit'
    TabOrder = 4
    OnClick = BitBtnEditClick
  end
  object wwTable1: TQuery
    DatabaseName = 'DBTest'
    Left = 424
    Top = 32
  end
  object wwQueryForEdit: TQuery
    DatabaseName = 'DBTest'
    Left = 464
    Top = 32
  end
  object wwDataSource1: TDataSource
    DataSet = wwTable1
    Left = 392
    Top = 32
  end
  object Database1: TDatabase
    DatabaseName = 'DBTest'
    SessionName = 'Default'
    Left = 504
    Top = 32
  end
end

---------------cut------------------
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now