Board index » delphi » MS SQL6.5 Lock ups

MS SQL6.5 Lock ups

I Have a simple delphi 2.0 application that just goes to a record and puts
the table in
edit mode and updates a field in the table. This worked for weeks with no
problem
now accessing just certain rows locks the application on diffrent machines
any advice..
code is as follows..

if product.findkey([Product_value]) then
begin
  product.edit;
  productstatus.value := 'SHIPPED';
  product.post;
end;

 

Re:MS SQL6.5 Lock ups


I am also running Delphi 2.0 C/S with MS SQL 6.5 with Update Patch 1 and I
am getting locking problems as well.

When my application first starts up I use a TTABLE object for the users to
browse the data.
Then when they want to change a record I use the type of code that you do.
IE Put the Table in edit mode then make the change and Post.

And every so often a user will lock up and have to cntl-alt-del and end
task to kill the program

I watched the current activity in the SQL Enterprise manager program to
view what was happening with locking and it showed that when a user would
try to update a record with the post code it was being blocked.

Now I it looks like when the client program first opens a TTABLE or moves
to another record in this TTABLE object I see a select statement in the
current activity program with a page lock. I do not think this should
happen but it does. And with multiply users eventually show update is
blocked and hangs that user.

My quick fix is that after a user updates or moves to another record I do a
TTABLE.REFRESH and this seems to drop the page lock that the select for the
TTABLE creates.

But I still do not really understand what is happening that causes a select
to create a page lock?

Thanks
Darren Wolchyn
Systems Engineer
[email protected]

Re:MS SQL6.5 Lock ups


Hi Darren,

Quote
You wrote:
> I am also running Delphi 2.0 C/S with MS SQL 6.5 with Update Patch 1 and I
> am getting locking problems as well.

> When my application first starts up I use a TTABLE object for the users to
> browse the data.
> Then when they want to change a record I use the type of code that you do.
> IE Put the Table in edit mode then make the change and Post.

> And every so often a user will lock up and have to cntl-alt-del and end
> task to kill the program

> I watched the current activity in the SQL Enterprise manager program to
> view what was happening with locking and it showed that when a user would
> try to update a record with the post code it was being blocked.

> Now I it looks like when the client program first opens a TTABLE or moves
> to another record in this TTABLE object I see a select statement in the
> current activity program with a page lock. I do not think this should
> happen but it does. And with multiply users eventually show update is
> blocked and hangs that user.

> My quick fix is that after a user updates or moves to another record I do a
> TTABLE.REFRESH and this seems to drop the page lock that the select for the
> TTABLE creates.

> But I still do not really understand what is happening that causes a select
> to create a page lock?

Depending what isolation levels (eg. Read Serializable) you're using, SELECTs
almost always generate some form of lock. Remember that SQL Server doesn't yet
support row-locking, so the lowest atomic lock is done at page level.

Regards,

Rob
--
Rob McGillivray
Software Development Manager
DataSoft - "Voted Top Development House in South Africa - 1994"
ODBCExpress: the top ODBC data access tool for Delphi, http://www.odbcexpress.com

Other Threads