Board index » delphi » MS SQL 6.5 Page Intent Lock

Max Murazzan

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

MS SQL 6.5 Page Intent Lock

We make a client/server application that can work with Paradox or MS SQL 6.5
SP 4.
We have Delphi 3.02 and BDE 4.51.
We use a TTable for accessing data.
With Paradox work fine.
With MS SQL we have a lot of problem with Page Intent Lock.
Can someone tell us if there is a solution with Delphi & Bde (with new
version) or if there is a path for MS SQL 6.5 or if there is an Bde
alternative product.
If possible only tested solutions.

Thanks in advance.

 
Kevin Frever

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Page Intent Lock


Max

   Avoid using TTables with any SQL (Oracle, MSSQL, Interbase etc) server.
The best (and tested) solution is to used TQuerys and SQL.

Good luck,
krf

Max Murazzano <m...@isiline.it> wrote in article
<704dnh$m...@forums.borland.com>...

Quote
> We make a client/server application that can work with Paradox or MS SQL
6.5
> SP 4.
> We have Delphi 3.02 and BDE 4.51.
> We use a TTable for accessing data.
> With Paradox work fine.
> With MS SQL we have a lot of problem with Page Intent Lock.
> Can someone tell us if there is a solution with Delphi & Bde (with new
> version) or if there is a path for MS SQL 6.5 or if there is an Bde
> alternative product.
> If possible only tested solutions.

> Thanks in advance.

lesa..

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Page Intent Lock


Hi,

From my point of view, I think it is the TTable components whom are causing
the Page Intent Locks on the SQL Server.  When developing Delphi Applications
for the SQL server, it is better to switch from using TTable components to
TQuery components.  It will help you a lot.  When you are using TTable
components on an MS SQL Server things should be considered :

   *  TTable reads all the records from a table, even if a filter is set.
      This can cause a lot of problems (I think this is what is causing your
      Page Intent Locks)

   *  When handling larger amounts of data, a TTable component is slower than
      a TQuery component.  (whith small tables the speed is almost the same)

   *  When using TQuery components, you can use SQL statements

If you want to read more about using Delphi to program SQL server
applications, you should pay a visit to the folowing web page :

         http://www.kalista.com/TechPapers/DelphiSQL/DelphiSQL.htm

There you will find a White Paper on using Delphi in combination with SQL
Server.  It focuses on several issues, like Identity Fields, Transactions,
Isolation Levels, Locking, ... And it also tells a great deal about Page
Intent Locks and Table Intent Locks.

I hope this will be of any help to you.

Greetings and best regards,

Kingetje

In article <704dnh$m...@forums.borland.com>,
  "Max Murazzano" <m...@isiline.it> wrote:

Quote
> We make a client/server application that can work with Paradox or MS SQL 6.5
> SP 4.
> We have Delphi 3.02 and BDE 4.51.
> We use a TTable for accessing data.
> With Paradox work fine.
> With MS SQL we have a lot of problem with Page Intent Lock.
> Can someone tell us if there is a solution with Delphi & Bde (with new
> version) or if there is a path for MS SQL 6.5 or if there is an Bde
> alternative product.
> If possible only tested solutions.

> Thanks in advance.

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    
Reid Roma

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Page Intent Lock


Quote
Max Murazzano wrote:

> We make a client/server application that can work with Paradox or MS SQL 6.5
> SP 4.
> We have Delphi 3.02 and BDE 4.51.
> We use a TTable for accessing data.
> With Paradox work fine.
> With MS SQL we have a lot of problem with Page Intent Lock.
> Can someone tell us if there is a solution with Delphi & Bde (with new
> version) or if there is a path for MS SQL 6.5 or if there is an Bde
> alternative product.

The problem with a TTable in a client / server (SQL) environment is that
the
table needs to maintain at least on additional connection of it's own so
that that it
can deal with the cursor.

You are also fetching ALL the rows of a dataset by using the TTable, as
where a TQuery
will allow you to narrow the scope..

Hope that help

Rkr

--
                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. Delphi Programmer / Analyst             .
. TVisualBasic:=class(None);              .
. May the Source be With You              .
-------------------------------------------
. Auto-By-Tel (http://www.autobytel.com)  .
. Irvine, CA U.S.A                        .
. E-Mail : rkroman (at) pacbell (dot) net .
. or reidr (at) autobytel (dot) com       .
-------------------------------------------

Max Murazzan

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Page Intent Lock


With TTable Fetch all seems not work correctly.

Reid Roman ha scritto nel messaggio <36260BFB.82022...@home.com>...

Quote

>Max Murazzano wrote:

>> We make a client/server application that can work with Paradox or MS SQL
6.5
>> SP 4.
>> We have Delphi 3.02 and BDE 4.51.
>> We use a TTable for accessing data.
>> With Paradox work fine.
>> With MS SQL we have a lot of problem with Page Intent Lock.
>> Can someone tell us if there is a solution with Delphi & Bde (with new
>> version) or if there is a path for MS SQL 6.5 or if there is an Bde
>> alternative product.

>The problem with a TTable in a client / server (SQL) environment is that
>the
>table needs to maintain at least on additional connection of it's own so
>that that it
>can deal with the cursor.

>You are also fetching ALL the rows of a dataset by using the TTable, as
>where a TQuery
>will allow you to narrow the scope..

>Hope that help

>Rkr

>--
>                   \|||/
>                   /'^'\
>                  ( 0 0 )
>--------------oOOO--(_)--OOOo--------------
>. Reid Roman           .
>. Delphi Programmer / Analyst           .
>. TVisualBasic:=class(None);           .
>. May the Source be With You   .
>-------------------------------------------
>. Auto-By-Tel (http://www.autobytel.com)  .
>. Irvine, CA U.S.A   .
>. E-Mail : rkroman (at) pacbell (dot) net .
>. or reidr (at) autobytel (dot) com   .
>-------------------------------------------

Max Murazzan

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Page Intent Lock


I know that with TQuery I can resolve problem but I must rewrite a lot of
code. There is a solution using a TTable or other component similar TTable
of third party?

Thanks.

Kevin Frevert ha scritto nel messaggio
<01bdf833$ce7d9880$2519320a@super30>...

Quote
>Max

>   Avoid using TTables with any SQL (Oracle, MSSQL, Interbase etc) server.
>The best (and tested) solution is to used TQuerys and SQL.

>Good luck,
>krf

>Max Murazzano <m...@isiline.it> wrote in article
><704dnh$m...@forums.borland.com>...
>> We make a client/server application that can work with Paradox or MS SQL
>6.5
>> SP 4.
>> We have Delphi 3.02 and BDE 4.51.
>> We use a TTable for accessing data.
>> With Paradox work fine.
>> With MS SQL we have a lot of problem with Page Intent Lock.
>> Can someone tell us if there is a solution with Delphi & Bde (with new
>> version) or if there is a path for MS SQL 6.5 or if there is an Bde
>> alternative product.
>> If possible only tested solutions.

>> Thanks in advance.

Kevin Frever

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Page Intent Lock


Max,

   One solution is to before opening a TTable, set the filter to narrow
down the result set (and obviously set filtered = true).  When you do this,
the TTable issues a 'SELECT * FROM <whatever table> WHERE <your filter
condition>'.  To minimize connections, only open TTables you need, and
close them as soon as your done.

Does this help?  I know switching to TQuerys would be a lot of work in the
short term,  but the long term benefit of increased performance,
maintenance, etc will be worth it.

Good luck,
krf

Max Murazzano <m...@isiline.it> wrote in article
<706pum$pv...@forums.borland.com>...

Quote
> I know that with TQuery I can resolve problem but I must rewrite a lot of
> code. There is a solution using a TTable or other component similar
TTable
> of third party?

> Thanks.

> Kevin Frevert ha scritto nel messaggio
> <01bdf833$ce7d9880$2519320a@super30>...
> >Max

> >   Avoid using TTables with any SQL (Oracle, MSSQL, Interbase etc)
server.
> >The best (and tested) solution is to used TQuerys and SQL.

> >Good luck,
> >krf

> >Max Murazzano <m...@isiline.it> wrote in article
> ><704dnh$m...@forums.borland.com>...
> >> We make a client/server application that can work with Paradox or MS
SQL
> >6.5
> >> SP 4.
> >> We have Delphi 3.02 and BDE 4.51.
> >> We use a TTable for accessing data.
> >> With Paradox work fine.
> >> With MS SQL we have a lot of problem with Page Intent Lock.
> >> Can someone tell us if there is a solution with Delphi & Bde (with new
> >> version) or if there is a path for MS SQL 6.5 or if there is an Bde
> >> alternative product.
> >> If possible only tested solutions.

> >> Thanks in advance.

Reid Roma

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Page Intent Lock


Quote
Max Murazzano wrote:

> With TTable Fetch all seems not work correctly.

<SNIP>

The TTable object by default will fetch ALL the records in the dataset..

What do you mean "Not working Correctly"?

Rkr

--
                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. Delphi Programmer / Analyst             .
. TVisualBasic:=class(None);              .
. May the Source be With You              .
-------------------------------------------
. Auto-By-Tel (http://www.autobytel.com)  .
. Irvine, CA U.S.A                        .
. E-Mail : rkroman (at) pacbell (dot) net .
. or reidr (at) autobytel (dot) com       .
-------------------------------------------

Max Murazzan

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Page Intent Lock


I mean that I've tried with Fetch all and I'm always the same problem.

Thanks.

Reid Roman ha scritto nel messaggio <3627607D.25E9C...@home.com>...

Quote

>Max Murazzano wrote:

>> With TTable Fetch all seems not work correctly.

><SNIP>

>The TTable object by default will fetch ALL the records in the dataset..

>What do you mean "Not working Correctly"?

>Rkr

>--
>                   \|||/
>                   /'^'\
>                  ( 0 0 )
>--------------oOOO--(_)--OOOo--------------
>. Reid Roman           .
>. Delphi Programmer / Analyst           .
>. TVisualBasic:=class(None);           .
>. May the Source be With You   .
>-------------------------------------------
>. Auto-By-Tel (http://www.autobytel.com)  .
>. Irvine, CA U.S.A   .
>. E-Mail : rkroman (at) pacbell (dot) net .
>. or reidr (at) autobytel (dot) com   .
>-------------------------------------------

Max Murazzan

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Page Intent Lock


Ok, now we think to work for query in our applications.

Thanks.

Kevin Frevert ha scritto nel messaggio
<01bdf902$0fea3520$2519320a@super30>...

Quote
>Max,

>   One solution is to before opening a TTable, set the filter to narrow
>down the result set (and obviously set filtered = true).  When you do this,
>the TTable issues a 'SELECT * FROM <whatever table> WHERE <your filter
>condition>'.  To minimize connections, only open TTables you need, and
>close them as soon as your done.

>Does this help?  I know switching to TQuerys would be a lot of work in the
>short term,  but the long term benefit of increased performance,
>maintenance, etc will be worth it.

>Good luck,
>krf

>Max Murazzano <m...@isiline.it> wrote in article
><706pum$pv...@forums.borland.com>...
>> I know that with TQuery I can resolve problem but I must rewrite a lot of
>> code. There is a solution using a TTable or other component similar
>TTable
>> of third party?

>> Thanks.

>> Kevin Frevert ha scritto nel messaggio
>> <01bdf833$ce7d9880$2519320a@super30>...
>> >Max

>> >   Avoid using TTables with any SQL (Oracle, MSSQL, Interbase etc)
>server.
>> >The best (and tested) solution is to used TQuerys and SQL.

>> >Good luck,
>> >krf

>> >Max Murazzano <m...@isiline.it> wrote in article
>> ><704dnh$m...@forums.borland.com>...
>> >> We make a client/server application that can work with Paradox or MS
>SQL
>> >6.5
>> >> SP 4.
>> >> We have Delphi 3.02 and BDE 4.51.
>> >> We use a TTable for accessing data.
>> >> With Paradox work fine.
>> >> With MS SQL we have a lot of problem with Page Intent Lock.
>> >> Can someone tell us if there is a solution with Delphi & Bde (with new
>> >> version) or if there is a path for MS SQL 6.5 or if there is an Bde
>> >> alternative product.
>> >> If possible only tested solutions.

>> >> Thanks in advance.

John Gibe

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Page Intent Lock


This is a hack but it works.  Call the BDE function DbiForceReread in the after
scroll event of the ttable for SQL Server.  This function  is used in the
refresh method of a TTable.  If you look at the results of sp_lock you wil
notice your intent locks go away when you hit the refresh button.

John Gibel

Quote
Max Murazzano wrote:
> We make a client/server application that can work with Paradox or MS SQL 6.5
> SP 4.
> We have Delphi 3.02 and BDE 4.51.
> We use a TTable for accessing data.
> With Paradox work fine.
> With MS SQL we have a lot of problem with Page Intent Lock.
> Can someone tell us if there is a solution with Delphi & Bde (with new
> version) or if there is a path for MS SQL 6.5 or if there is an Bde
> alternative product.
> If possible only tested solutions.

> Thanks in advance.

Other Threads

1. BDE Leaves Table Intent Lock in SQL 6.5, Patch 4

2. TTable,Page locks!!! and MS SQL server 6.5

3. TTable,Page Locks!!! and MS SQL Server 6.5

4. SQL Page Intent Lock blocking problem

5. D2, MS SQL 6.5, App Locks up on Update

6. MS SQL 6.5 exclusive lock to row

7. Delphi 3 and MS SQL Server 6.5 Locking Problem

8. Maximum number of locks in MS SQL 6.5

9. MS SQL 6.5 Record locking with TTable

10. Delphi Locking MS SQL 6.5 Server