How to update and then delete a record inside of a transaction

[former_member584270]

(This is a repost from the old forum. I didn't realize there was a new place to post questions. Feel free to delete from either place)

I have a problem related to transactions + RI rules.

I have a master/detail table set up. I have an RI rule set up on these tables to Update/Cascade and Delete/Set Null.

I begin a transaction.

I update a child detail record (to reset default values).

I delete the parent header record.

This causes the following error: QryTemp: Error 7200: AQE Error: State = HY000; NativeError = 5035; [iAnywhere Solutions][Advantage SQL][ASA] Error 5035: The requested lock could not be granted. The file or record may be locked by another user. This record lock was necessary because of a cascaded update or delete due to exising referential integrity rules. Table: \FS01\CON1\STOPS.ADT Record #: 46079

Why is the engine considering the child record locked when both operations happen under the same AdsConnection object, and within the same transaction. This code works fine outside of the transaction.

Thanks for your time, Dusten

[Former Member]

I already answered this on the devzone forum:

How to update and then delete a record inside of a transaction - ADS Forum

Here is how I understand what's going on:

1. The UPDATE statement on the detail table inside the transaction starts an implicit lock on the detail table.
2. The DELETE on the master table causes the RI rule to kick in.
3. The RI rule can't acquire a lock on the detail table due to the (implicit) lock from step 1.

[HakanHaslaman]

What is the ADS Version you are using exactly? If you are not on the latest Version of ADS, so I would recommend to make an update to the latest hotfix and try it again.