Board index » delphi » Help!! SQL Server7 Record locking

Help!! SQL Server7 Record locking

Who can help

We have a problem with sql server, we had an application running on sql
anywhere where we:
1. Set a table in edit mode.
2. Exec a store procedure to calculate a value.

we now have the folloing problem, the procedure waits for the row to lock.
we have set the transaction to dirty read but have had no success.

anny sugestions?

Thanks

CPI

 

Re:Help!! SQL Server7 Record locking


Quote
CPI wrote:

> Who can help

> We have a problem with sql server, we had an application running on sql
> anywhere where we:
> 1. Set a table in edit mode.
> 2. Exec a store procedure to calculate a value.

> we now have the folloing problem, the procedure waits for the row to lock.
> we have set the transaction to dirty read but have had no success.

> anny sugestions?

> Thanks

> CPI

Two suggestions. First, don't use a ttable. TQuery is made for working
with SQL databases, TTAbles are for local file databases.

That being said, why are you putting the table into edit mode? Why not
get the record from the TQuery, pass the data you need in your
calculation to the stored proc. When you get the answer back from the
stored proc, use another TQuery to update the target row with the result
of the calculation.

Hope this helps
Bradford C. Miller

Re:Help!! SQL Server7 Record locking


Hi Bradford.

The company I work for does not do this they use a query and set it in edit
mode.
what do you think, sugestions, other then re-writing the 407 query's.

I think that this is wher I have to go to.

CPI
Bradford C. Miller <millerb...@spicedhamawayaol.com> wrote in message
news:394A68AF.A0396172@spicedhamawayaol.com...

Quote
> CPI wrote:

> > Who can help

> > We have a problem with sql server, we had an application running on sql
> > anywhere where we:
> > 1. Set a table in edit mode.
> > 2. Exec a store procedure to calculate a value.

> > we now have the folloing problem, the procedure waits for the row to
lock.
> > we have set the transaction to dirty read but have had no success.

> > anny sugestions?

> > Thanks

> > CPI

> Two suggestions. First, don't use a ttable. TQuery is made for working
> with SQL databases, TTAbles are for local file databases.

> That being said, why are you putting the table into edit mode? Why not
> get the record from the TQuery, pass the data you need in your
> calculation to the stored proc. When you get the answer back from the
> stored proc, use another TQuery to update the target row with the result
> of the calculation.

> Hope this helps
> Bradford C. Miller

Re:Help!! SQL Server7 Record locking


The problem was in transaction managment. SQL server locked the record.

Bradford C. Miller <millerb...@spicedhamawayaol.com> wrote in message
news:394A68AF.A0396172@spicedhamawayaol.com...

Quote
> CPI wrote:

> > Who can help

> > We have a problem with sql server, we had an application running on sql
> > anywhere where we:
> > 1. Set a table in edit mode.
> > 2. Exec a store procedure to calculate a value.

> > we now have the folloing problem, the procedure waits for the row to
lock.
> > we have set the transaction to dirty read but have had no success.

> > anny sugestions?

> > Thanks

> > CPI

> Two suggestions. First, don't use a ttable. TQuery is made for working
> with SQL databases, TTAbles are for local file databases.

> That being said, why are you putting the table into edit mode? Why not
> get the record from the TQuery, pass the data you need in your
> calculation to the stored proc. When you get the answer back from the
> stored proc, use another TQuery to update the target row with the result
> of the calculation.

> Hope this helps
> Bradford C. Miller

Re:Help!! SQL Server7 Record locking


Eplain further please!  I am starting a fairly large SQL server project.

Thanks,
Mike

Quote
CPI <C...@cpi.com> wrote in message news:3950daa7@dnews...
> The problem was in transaction managment. SQL server locked the record.

Re:Help!! SQL Server7 Record locking


If you use transactional management in delphi (TDatabase.begintransaction )
then the query's conected to the database using the TDatabase will lock rows
ian will not alow reading of records as delphi's odbc and bde drivers do not
alow dirty read.

you have to manage transactional work on the database, i had to merg several
query's in one store proc and perform a action or role back from it. You
will see the problem that query's will not open ore procedures thatsould
take mili sec wait and wait for ever.

here is sql server's explination

Microsoft? SQL ServerT uses locking to ensure transactional integrity and
database consistency. Locking prevents users from reading data being changed
by other users, and prevents multiple users from changing the same data at
the same time. If locking is not used, data within the database may become
logically incorrect, and queries executed against that data may produce
unexpected results.

Although SQL Server enforces locking automatically, you can design more
efficient applications by understanding and customizing locking in your
applications.

 Four Concurrency Problems
If locking is not available and several users access a database
concurrently, four problems may occur if their transactions use the same
data at the same time. The four problems are:

The lost, or buried, update problem.

The uncommitted dependency problem (dirty read).

The inconsistent analysis problem (nonrepeatable read).

The phantom read problem (phantom).
The Lost Update Problem
The lost update problem arises when two or more transactions select the same
row and then update the row based on the value originally selected. Because
each transaction is unaware of other transactions, the last update
overwrites the updates made by the other transactions. Therefore, data has
been lost.

For example, two editors make an electronic copy of the same document. Each
editor changes her copy independently and then saves the changed copy,
thereby overwriting the original document. The editor who saves her changed
copy last overwrites changes made by the first editor. The first editor's
work has been lost without her knowing it, causing problems later on. It
would be better if the second editor could not make changes until the first
editor had finished.

The Uncommitted Dependency Problem (Dirty Read)
For example, an editor is making changes to an electronic document. During
the changes, a second editor takes a copy of the document that includes all
the changes made so far, and distributes it to the intended audience. The
first editor then decides the changes made so far are wrong and removes the
edits and saves the document. The distributed document contains edits that
no longer exist, and should be treated as if they never existed. It would be
better if no one could read the changed document until the first editor
determined that the changes were final.

The Inconsistent Analysis Problem (Nonrepeatable Read)
For example, an editor reads the same document twice, but between each
reading, the writer rewrites the document. When the editor reads the
document for the second time, it has completely changed. The original read
was not repeatable, leading to confusion. It would be better if the editor
could read the document only after the writer has completely finished
writing it.

The Phantom Read Problem (Phantom)
For example, an editor reads and edits a document submitted by a writer, but
when the edits are being incorporated into the master copy of the document
by the production department, they find that new, unedited material has been
added to the document by the author. The document contains new material that
previously did not exist, leading to confusion and problems. It would be
better if no one could add new material to the document until the editor and
production department finishes working with the original document.

If you need locking or unlocking, lets say you have a procedure that will
calculate a total of the table that you or some one else have in edit mode.
you will need to do this

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select Max(SALES)  from SHOPTABLE WHERE SaleDate = Date()

Syntax
SET TRANSACTION ISOLATION LEVEL
    { READ COMMITTED
        | READ UNCOMMITTED
        | REPEATABLE READ
        | SERIALIZABLE
    }

Arguments
READ COMMITTED

Specifies that shared locks are held while the data is being read to avoid
dirty reads, but the data can be changed before the end of the transaction,
resulting in nonrepeatable reads or phantom data. This option is the SQL
Server default.

READ UNCOMMITTED

Implements dirty read, or isolation level 0 locking, which means that no
shared locks are issued and no exclusive locks are honored. When this option
is set, it is possible to read uncommitted or dirty data; values in the data
can be changed and rows can appear or disappear in the data set before the
end of the transaction. This option has the same effect as setting NOLOCK on
all tables in all SELECT statements in a transaction. This is the least
restrictive of the four isolation levels.

REPEATABLE READ

Locks are placed on all data that is used in a query, preventing other users
from updating the data, but new phantom rows can be inserted into the data
set by another user and are included in later reads in the current
transaction. Because concurrency is lower than the default isolation level,
use this option only when necessary.

SERIALIZABLE

Places a range lock on the data set, preventing other users from updating or
inserting rows into the data set until the transaction is complete. This is
the most restrictive of the four isolation levels. Because concurrency is
lower, use this option only when necessary. This option has the same effect
as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Remarks
Only one of the options can be set at a time, and it remains set for that
connection until it is explicitly changed. This becomes the default behavior
unless an optimization option is specified at the table level in the FROM
clause of the statement.

The setting of SET TRANSACTION ISOLATION LEVEL is set at execute or run time
and not at parse time.

Examples
This example sets the TRANSACTION ISOLATION LEVEL for the session. For each
Transact-SQL statement that follows, SQL Server holds all of the shared
locks until the end of the transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
SELECT * FROM publishers
SELECT * FROM authors
...
COMMIT TRANSACTION

Related Topics

Quote
----- Original Message -----
From: Mike <akinREMOVET...@theshop.net>

Newsgroups: borland.public.delphi.database.sqlservers
Sent: Friday, June 23, 2000 8:11 PM
Subject: Re: Help!! SQL Server7 Record locking

> Eplain further please!  I am starting a fairly large SQL server project.

> Thanks,
> Mike

> CPI <C...@cpi.com> wrote in message news:3950daa7@dnews...
> > The problem was in transaction managment. SQL server locked the record.

Mike <akinREMOVET...@theshop.net> wrote in message
news:8j08kq$i6614@bornews.borland.com...
> Eplain further please!  I am starting a fairly large SQL server project.

> Thanks,
> Mike

> CPI <C...@cpi.com> wrote in message news:3950daa7@dnews...
> > The problem was in transaction managment. SQL server locked the record.

Other Threads