FetchAll=False and Locks - Problem

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
tinof
Posts: 39
Joined: Fri 16 Dec 2005 07:41

FetchAll=False and Locks - Problem

Post by tinof » Thu 08 Jan 2009 10:36

Hi,

i have this problem, maybe i did'nt understand the FetchAll - Idea as well.

Situation:
A table i.g. Customers with up to 100.000 Records, additional up to 20 related tables in master-detail relation to this maintable.

Form[1]:
Based on the query SELECT * FROM Customers WHERE ID = [one id] it shows one record with all relations. First i open the query on base table, after that i open the details tables.
It works fine and quick.
All tables are FetchAll = True, editing data works well.

Form[2]:
Only a crDBGrid + Datasource + MSQuery with SELECT [some fields] FROM Customers.
MSQuery.Fetchall=false, MSQuery.Readonly=true,
MSQuery.Cursortype=ctDefaultResultSet (because of enabling local sorting)

My Idea is: first search with form[2] one record, than show detail data by calling form[1]. If i single user, it works fine.

But in Multi -User enviorment my Problem is:
The Form[2] (with the fetchall=false - query) locks records. Depending on sorting in the crdbgrid there are sometimes many, many locks at the DB - Tables. These locks prevent other users from changing data.

But i dont want any locking while searching with my Form[2].

How can i configure SDAC, that there are no locks when fetchall=false ?

Thanks
Tino

Edit:
Latest SDAC,
MSSQL Server 2000
Delphi 2006

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 08 Jan 2009 15:11

I can not reproduce the problem.
Please send me a complete small sample at dmitryg*devart*com to demonstrate it, including script to create and fill table.

oscar
Posts: 1
Joined: Tue 17 Feb 2009 09:14

Locking issue

Post by oscar » Tue 17 Feb 2009 11:19

We had a similar problem. An open cursor in a not completely fetched dataset results in a lock error for other queries on the same table. This dataset is used for browse only.
Using a fetchall is not desirable, because of the performance loss.

Our recommendation is to use the NOLOCK table hint for these queries. Don't use this table hint within transactions.

Post Reply