Board index » delphi » MS-SQL 6, query hangs - locking problem?

MS-SQL 6, query hangs - locking problem?

Hi, we're working in this environment:

Delphi 3.01, using BDE 4.01
MS-SQL 6, latest service pack.
All tables set to record locking.

Server OS is NT 4.0, sp3

Client runs Win95 or WinNT - makes no difference.

What happens?
Sometimes, a query does hang for 20 minutes until it finishes.
This even happens if there is only one user logged in.
It is not always the same query, but it always happens in the same job
(about 10 queries involved).

In 9 out of 10 times, the job is completed in 2 minutes. But once out of 10
times, one of the queries will hang and take 20 - 30 minutes. It will in
this situation still work fine.

Our product was originally written for Oracle & InterBase where we never had
such problems.
I know that MS uses page locking by default whereas Oracle & IB do
record-locking.
Therefore I already set all user tables to record locking, which seemed to
help in the beginning only..
What can I do against it?

The database is fairly small (the db device is 50 MB and not fully used at
all).
And, to point it out again, the problem occurs even in a test environment
when I am the only user.
The log files don't show anything either.

Any ideas what I can do?

Please help, the customer wants this to be "fixed" (other than using a
different DBMS) and I am clueless.

Thanks very much..
Peter Tiemann
pe...@preview.org

 

Re:MS-SQL 6, query hangs - locking problem?


Hi

I am having similar problems, which are related to the cached updates, and
the fact that the applyupdates method uses separate connections to perform
deletes, modifies and updates. This causes deadlock problems when these are
applied (because of page locking).

Could your problem be related to this?

Jeff

Quote
Peter Tiemann wrote in message <922922158.221...@news.remarQ.com>...
>Hi, we're working in this environment:

>Delphi 3.01, using BDE 4.01
>MS-SQL 6, latest service pack.
>All tables set to record locking.

>Server OS is NT 4.0, sp3

>Client runs Win95 or WinNT - makes no difference.

>What happens?
>Sometimes, a query does hang for 20 minutes until it finishes.
>This even happens if there is only one user logged in.
>It is not always the same query, but it always happens in the same job
>(about 10 queries involved).

>In 9 out of 10 times, the job is completed in 2 minutes. But once out of 10
>times, one of the queries will hang and take 20 - 30 minutes. It will in
>this situation still work fine.

>Our product was originally written for Oracle & InterBase where we never
had
>such problems.
>I know that MS uses page locking by default whereas Oracle & IB do
>record-locking.
>Therefore I already set all user tables to record locking, which seemed to
>help in the beginning only..
>What can I do against it?

>The database is fairly small (the db device is 50 MB and not fully used at
>all).
>And, to point it out again, the problem occurs even in a test environment
>when I am the only user.
>The log files don't show anything either.

>Any ideas what I can do?

>Please help, the customer wants this to be "fixed" (other than using a
>different DBMS) and I am clueless.

>Thanks very much..
>Peter Tiemann
>pe...@preview.org

Other Threads