Tricky Record Locking Problems

[Ryan]

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 ?).

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.

No other tables should be in use at this point.

Any suggestions ?

Thanks

Ryan

/* Code for query component - users navigate to the record they need
to edit
there could be 15,000 records showing as outstanding - perhaps this
is the area that I need to re-visit so that less records can be edited
? */

SELECT *

FROM
PostReceived

WHERE
ToDelete = 0 AND
Completed <> 1

ORDER BY
PostID

/* Table in question */

if exists (select * from sysobjects where id =
object_id(N'[dbo].[PostReceived]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[PostReceived]
GO

CREATE TABLE [dbo].[PostReceived] (
[PostID] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [varchar] (100) NULL ,
[ClientsName] [varchar] (100) NULL ,
[DateReceived] [datetime] NULL ,
[EnteredBy] [varchar] (100) NULL ,
[AssignedTo] [varchar] (100) NULL ,
[DateAssignedTo] [datetime] NULL ,
[Adviser] [varchar] (100) NULL ,
[TargetDate] [datetime] NULL ,
[CompletionDate] [datetime] NULL ,
[Completed] [bit] NULL ,
[KeyAccount] [varchar] (100) NULL ,
[Notes] [text] NULL ,
[Specific1] [varchar] (20) NULL ,
[Specific2] [varchar] (20) NULL ,
[Specific3] [varchar] (20) NULL ,
[Specific4] [varchar] (20) NULL ,
[Specific5] [varchar] (20) NULL ,
[ToDelete] [bit] NULL ,
[EnterUser] [varchar] (20) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

[Erland Sommarskog]

Ryan (ry********@hot mail.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, so****@algonet. se

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

[Ryan]

Sorry, ignore it. I spotted the problem. I left a copy of the table
open which I should have closed when the user entered that form. It
then locks the table, hence my problem.

Ryan

[Ryan]

Erland, thanks for the reply, I managed to post mine before I saw
yours. The aba_lockinfo SP really helped me (once I re-visited it). I
used it whilst trying various things out with while debugging the app
and eventually found one particular way of getting into the data entry
screen wasn't doing what I wanted it to do (under a specific
scenario). By using your sp, I watched what happened on the server and
found where the problem was. I could reliably re-create the problem. I
fixed it and tested with the sp, and it seems to have done the trick.
Once again, thanks.