Board index » delphi » Locking Problem?

Locking Problem?


2004-03-05 07:00:45 AM
delphi17
I use Delphi 5 enterprise with Sybase SQLAnywhere 8.0.2 databases. Since
upgrading the databases from 6 to 8 there seem to be problems updating
records. Sometimes the app will just hang when an update is applied and it
will be several hours before the update will be successful. For example a
user tried to delete a set of master/detail records this afternoon. The app
hung after clicking the save button. I tried to delete the records and the
delete was successful for me. I then tried to delete a second set of
master/detail records from the same two tables and that transaction hung. I
am using cached updates. Why would the first delete be fine and the second
one hang? Can anyone explain to me what is going on? Does this have
anything to do with isolation levels and locking of records? Thanks.
 
 

Re:Locking Problem?

In article <XXXX@XXXXX.COM>,
XXXX@XXXXX.COM says...
Quote
I use Delphi 5 enterprise with Sybase SQLAnywhere 8.0.2 databases. Since
upgrading the databases from 6 to 8 there seem to be problems updating
records. Sometimes the app will just hang when an update is applied and it
will be several hours before the update will be successful. For example a
user tried to delete a set of master/detail records this afternoon. The app
hung after clicking the save button. I tried to delete the records and the
delete was successful for me. I then tried to delete a second set of
master/detail records from the same two tables and that transaction hung. I
am using cached updates. Why would the first delete be fine and the second
one hang? Can anyone explain to me what is going on? Does this have
anything to do with isolation levels and locking of records? Thanks.



Were your deletes dropping unique sets of records?? Or was their overlap
in the detail records?
Did you do a Commit and ApplyUpdates after the first delete??
Your second delete may have been waiting for the first transaction to
complete. Surprised that it hung that long. You should have received a
timeout error, unless you app does automatic retries, in which case you
can be easily hosed like this.
Generally I find that calling a stored proc is more efficient for doing
deletes than using client code. YMMV.
Allen.
 

Re:Locking Problem?

The deletes were for unique records. I have this code in the after post
event of the master query:
if Query_TransItems.State <>dsBrowse then
Query_TransItems.Post;
if Query_Transfer.State <>dsBrowse then
Query_Transfer.Post;
DataModule_Beaver.DataBase_BeaverPlastics.StartTransaction;
try
Query_Transfer.ApplyUpdates;
Query_TransItems.ApplyUpdates;
DataModule_Beaver.DataBase_BeaverPlastics.Commit;
Except
DataModule_Beaver.DataBase_BeaverPlastics.Rollback;
raise;
end;//try except
Query_Transfer.CommitUpdates;
Query_TransItems.CommitUpdates;
Excuse my ignorance but I don't know if my app does automatic retries, how
do I find out? The weird thing is users never experienced this problem until
we upgraded to version 8 of Sybase. Thanks.
"G. Allen Casteran" <XXXX@XXXXX.COM>writes
Quote
In article <XXXX@XXXXX.COM>,
XXXX@XXXXX.COM says...
>I use Delphi 5 enterprise with Sybase SQLAnywhere 8.0.2 databases. Since
>upgrading the databases from 6 to 8 there seem to be problems updating
>records. Sometimes the app will just hang when an update is applied and
it
>will be several hours before the update will be successful. For example
a
>user tried to delete a set of master/detail records this afternoon. The
app
>hung after clicking the save button. I tried to delete the records and
the
>delete was successful for me. I then tried to delete a second set of
>master/detail records from the same two tables and that transaction
hung. I
>am using cached updates. Why would the first delete be fine and the
second
>one hang? Can anyone explain to me what is going on? Does this have
>anything to do with isolation levels and locking of records? Thanks.
>
>
>

Were your deletes dropping unique sets of records?? Or was their overlap
in the detail records?

Did you do a Commit and ApplyUpdates after the first delete??

Your second delete may have been waiting for the first transaction to
complete. Surprised that it hung that long. You should have received a
timeout error, unless you app does automatic retries, in which case you
can be easily hosed like this.

Generally I find that calling a stored proc is more efficient for doing
deletes than using client code. YMMV.

Allen.