Board index » delphi » SQL Server 6.5, Transactions, Locking, Cached Updates

SQL Server 6.5, Transactions, Locking, Cached Updates

I have a problem with locking on Sql Server 6.5 which i think is to do
with page locks, but I can't think of a way round.

Within one transaction I am trying to update a row in a table and
insert a new row into the same table.  It seems that cached updates
uses one connection for the update and another connectionfor the
insert and both want to lock the page and both get blocked.

        Query.FieldByName('Name').AsString := 'Jim';
        Query.Append
        Query.FieldByName('Name').AsString := 'Mellish';

        Database.StartTransaction
        try
                Query.ApplyUpdates;
        except
                Database.Rollback;
        end;

Either my analysis of the problem is wrong or the method I am using to
post is wrong.

Any Help ?

Jim Mellish

 

Re:SQL Server 6.5, Transactions, Locking, Cached Updates


Jim

I encountered the same problem with a multiple update transaction. I had to
close the query and re-open it between updates. It's not a sensible
all-purpose solution but I haven't had time to investigate further.

Dave

Re:SQL Server 6.5, Transactions, Locking, Cached Updates


Hi Jim,

You should use DataBase.Commit after ApplyUpdates.At the end, if successful
do CommitUpdates. See the relevant examples in Delphi's help files

Quote
Jim Mellish wrote in message <375b69b6.24896...@forums.borland.com>...
>I have a problem with locking on Sql Server 6.5 which i think is to do
>with page locks, but I can't think of a way round.

>Within one transaction I am trying to update a row in a table and
>insert a new row into the same table.  It seems that cached updates
>uses one connection for the update and another connectionfor the
>insert and both want to lock the page and both get blocked.

> Query.FieldByName('Name').AsString := 'Jim';
> Query.Append
> Query.FieldByName('Name').AsString := 'Mellish';

> Database.StartTransaction
> try
> Query.ApplyUpdates;
> except
> Database.Rollback;
> end;

>Either my analysis of the problem is wrong or the method I am using to
>post is wrong.

>Any Help ?

>Jim Mellish

Re:SQL Server 6.5, Transactions, Locking, Cached Updates


Hi Jim!

On Mon, 07 Jun 1999 06:51:32 GMT, j...@functionpoint.demon.co.uk (Jim

Quote
Mellish) wrote:
>Within one transaction I am trying to update a row in a table and
>insert a new row into the same table.  It seems that cached updates
>uses one connection for the update and another connectionfor the
>insert and both want to lock the page and both get blocked.

This is not true. Only one connection is used, that is for sure.
Changes are applyed one by one.

Problem may be with the way you are applying updates to the database.
Are you using TUpdateSQL? Can you post InserSQL and ModifySQL
propertyis of your TUpdateSQL.

This problem you have should not be a problem because that is typical
situation and must work.

Quote
>    Query.FieldByName('Name').AsString := 'Jim';
>    Query.Append
>    Query.FieldByName('Name').AsString := 'Mellish';

>    Database.StartTransaction
>    try
>            Query.ApplyUpdates;
>    except
>            Database.Rollback;
>    end;

Why don't you just save your time and use

        Database.ApplyUpdates([Query])

instead of all that code which is incorect by the way.

If you look in the VCL source you will find the proper way to apply
updates to database (for more than one dataset as well).

Hope that helps.

Other Threads