Board index » delphi » BDE / Locks / MS SQL Server 7.0 - Help!

BDE / Locks / MS SQL Server 7.0 - Help!

We have ported a large Delphi 4.0 application from Paradox to SQL
Server 7.0 and are experiencing serious concurrency problems.
Eventually we will have to fix these by redesigning the application to
use TQueries instead of TTables, but we badly need a short-term fix to
some basic problems to get us out of difficulty.

The situation is this:

1. We have opened a table in Read-only mode.

2. We are scanning the table sequentially (i.e. First, While not eof do
Next)

3. We do not care if any of the records are being modified while we are
reading them.

Our observations imply:

SQL Server initially applies page locks on the table and then escalates
these to an Intent-shared table lock. As a result no other process or
user can update any records in the table until the scan has completed!
When they post their record they are left{*word*154} with the hourglass
cursor waiting for a lock timeout. This can be many minutes with a
large database.

The problem seems to be caused by the fact that SQL Server's default
transaction isolation mode is Read-Committed.

We have been trying to fix it by:

Finding a way to set the transaction isolation mode to be Read-
Uncommitted in these situations. We have attempted to do this:

1. We are using a call to DBIQExecDirect to set the transaction
isolation mode.

2. We have set the SQL Links SQLPASSTHRU option to SHARED AUTOCOMMIT so
that passthrough SQL uses existing connections.

3. Unfortunately, the BDE call which generates the SELECT statement
which results in the locks, is creating its own SQL connection and thus
reverts to the default (Read-uncommitted) isolation mode.

Does anybody know of a way (using Delphi TTables / BDE / MS SQL Server
7.0) that we can read a large table without locking it?

Chris Burrows
chr...@geac.com.au

Sent via Deja.com http://www.deja.com/
Before you buy.

 

Re:BDE / Locks / MS SQL Server 7.0 - Help!


The database.TransIsolation property probably doesn't help you here.
Have you posted a msg in one of the SQLServer forums, you may be able
to get round it by creating a View or something.
This is a good one if you don't know of it:
microsoft.public.sqlserver.programming
Good luck, I'd be interested to know a solution to this, if one exists,
as I may have the same predicament in the near future.
lou

Sent via Deja.com http://www.deja.com/
Before you buy.

Re:BDE / Locks / MS SQL Server 7.0 - Help!


One option would be to create a views for the tables and add the nolock
compiler hint

eg:

create view v_table1 as
select * from table1 (nolock)

Not 100% sure this works though...

Trevor Dunsford

Quote
<chrisb4...@my-deja.com> wrote in message

news:8b6fib$7j8$1@nnrp1.deja.com...
Quote
> We have ported a large Delphi 4.0 application from Paradox to SQL
> Server 7.0 and are experiencing serious concurrency problems.
> Eventually we will have to fix these by redesigning the application to
> use TQueries instead of TTables, but we badly need a short-term fix to
> some basic problems to get us out of difficulty.

> The situation is this:

> 1. We have opened a table in Read-only mode.

> 2. We are scanning the table sequentially (i.e. First, While not eof do
> Next)

> 3. We do not care if any of the records are being modified while we are
> reading them.

> Our observations imply:

> SQL Server initially applies page locks on the table and then escalates
> these to an Intent-shared table lock. As a result no other process or
> user can update any records in the table until the scan has completed!
> When they post their record they are left{*word*154} with the hourglass
> cursor waiting for a lock timeout. This can be many minutes with a
> large database.

> The problem seems to be caused by the fact that SQL Server's default
> transaction isolation mode is Read-Committed.

> We have been trying to fix it by:

> Finding a way to set the transaction isolation mode to be Read-
> Uncommitted in these situations. We have attempted to do this:

> 1. We are using a call to DBIQExecDirect to set the transaction
> isolation mode.

> 2. We have set the SQL Links SQLPASSTHRU option to SHARED AUTOCOMMIT so
> that passthrough SQL uses existing connections.

> 3. Unfortunately, the BDE call which generates the SELECT statement
> which results in the locks, is creating its own SQL connection and thus
> reverts to the default (Read-uncommitted) isolation mode.

> Does anybody know of a way (using Delphi TTables / BDE / MS SQL Server
> 7.0) that we can read a large table without locking it?

> Chris Burrows
> chr...@geac.com.au

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Re:BDE / Locks / MS SQL Server 7.0 - Help!


We had the same problem.

The solution is: turn off "implicit transactions"
on the enterprise manager.

This implicit transaction wraps "begin tran" and
"commit" around sql statements. Since bde uses 2
different connections to insert/update a record,
those conections hang one another (much like a
deadlock).

This (hang) happens at client level, the other
clients does not lock (unless theyre trying to
acess the same record)

I am supposing that you are using record locks
and not page locks.

Greetings from S?o Paulo, Brazil.
Paulo Egidio.

In article <eFpC4.33319
$pA.108...@typhoon.mbnet.mb.ca>,
  "Trevor Dunsford" <tdunsf...@bison.ca> wrote:

Quote
> One option would be to create a views for the

tables and add the nolock
Quote
> compiler hint

> eg:

> create view v_table1 as
> select * from table1 (nolock)

> Not 100% sure this works though...

> Trevor Dunsford

> <chrisb4...@my-deja.com> wrote in message
> news:8b6fib$7j8$1@nnrp1.deja.com...
> > We have ported a large Delphi 4.0 application
from Paradox to SQL
> > Server 7.0 and are experiencing serious

concurrency problems.
Quote
> > Eventually we will have to fix these by

redesigning the application to
Quote
> > use TQueries instead of TTables, but we badly

need a short-term fix to
Quote
> > some basic problems to get us out of
difficulty.

> > The situation is this:

> > 1. We have opened a table in Read-only mode.

> > 2. We are scanning the table sequentially

(i.e. First, While not eof do
Quote
> > Next)

> > 3. We do not care if any of the records are

being modified while we are
Quote
> > reading them.

> > Our observations imply:

> > SQL Server initially applies page locks on

the table and then escalates
Quote
> > these to an Intent-shared table lock. As a

result no other process or
Quote
> > user can update any records in the table

until the scan has completed!
Quote
> > When they post their record they are left

hanging with the hourglass
Quote
> > cursor waiting for a lock timeout. This can

be many minutes with a
Quote
> > large database.

> > The problem seems to be caused by the fact

that SQL Server's default
Quote
> > transaction isolation mode is Read-Committed.

> > We have been trying to fix it by:

> > Finding a way to set the transaction

isolation mode to be Read-
Quote
> > Uncommitted in these situations. We have

attempted to do this:
Quote

> > 1. We are using a call to DBIQExecDirect to
set the transaction
> > isolation mode.

> > 2. We have set the SQL Links SQLPASSTHRU

option to SHARED AUTOCOMMIT so
Quote
> > that passthrough SQL uses existing
connections.

> > 3. Unfortunately, the BDE call which

generates the SELECT statement
Quote
> > which results in the locks, is creating its

own SQL connection and thus
Quote
> > reverts to the default (Read-uncommitted)
isolation mode.

> > Does anybody know of a way (using Delphi

TTables / BDE / MS SQL Server

Quote
> > 7.0) that we can read a large table without
locking it?

> > Chris Burrows
> > chr...@geac.com.au

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.

Other Threads