Ryan (ryanofford@hotmail.com) writes:
> I have a problem with record locking / blocking within an application.
> The app is quite straight forward. Written in Delphi 5 using BDE to
> access a SQL 7 database (Win2K server).
>
> Every so often the users (when they bother to tell me) find that the
> application locks up and they are unable to work. No errors are
> produced (error trapping in the app is good). They 'shout round' to
> each other and get someone to exit the data entry screen. This seems
> to free up the locking/blocking issue.
>
> There are about 50,000 records in the table (script below) and it is
> accessed through a simple query (script below). All users will access
> this in the same way. I'm assuming that a new record is being edited
> when the problem occurs, but this shouldn't cause locking/blocking
> until it gets committed (right ?).

It depends on how Delphi handles the data. I don't know what access
methods Delphi uses, so I will talk in terms of ADO that I know at least
something about. Say that your application would get the data into a
recordset with a client-side cursor. In this case you should not get any
locking problems, because data is read once and then released.

But if you wuuld use a server-side cursor with perssimistic locking,
you would get blocking all over the place, because each user would
hold locks ons his data set, and no one else would be able to update.

There are a couple of variations on this theme. If the application
fails to commit an update, so the user clings to the locks, then
no user will be able to get any rows, because with the WHERE clause
of your query, it seems that a table scan is the only way to get the
rows, and thus SQL Server has to access the updated and locked rows
to see if they qualify. This particular situation you could overcome
with a READPAST hint. However, since we don't really know what is
causing the problem, it is difficult to tell.

> The problem is tracking down the source of this and finding the
> pattern which I can work back from. I've used Erland's aba_lockinfo
> script (a few months back admittedly so will re-visit this), but
> nothing obvious is jumping out at me.

Running it in a blocking situation could give some information of
what is going on. But in the end knowledge about what the Delphi
does is necessary.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp