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