rules and transactions 
Author Message
 rules and transactions



Quote:
>The problem we face here looks similar to this:

>We have a DB with about eight tables t1...t8.

>t1 can be considered to be the "main" relation, the other
>tables are subordinate to this table and can be joined on
>a common key.

>We now created a rule after delete on t1, that deletes
>the associated data in the other tables t2-t8.

>So far, it is ok, but: we have one application that
>updates any attribute in anyone of the app. 8 tables.
>It does this by deleting the data set in t1 (and
>triggering the deletion in the other tables). After
>this an insert with the same key is issued.

>It looks like this:

>   delete from t1 where key=something;
>             -> rule gets triggered:
>                -> delete from t2 where key=something;
>                   delete from t3 where key=something;
>                   ...
>                   delete from t8 where key=something;

>   insert into t1 (...) where key=something;
>   insert into t2 (...) where key=something;
>   ...

>   commit;

>_Sometimes_ we get an error: Duplicate key or
>duplicate rows found. Obviously, the procedure
>triggered by the delete rule has not yet completed
>the deletes when the insert statement starts.

>The documentation says:

>     The statement that fires a rule and the database procedure invoked
>     by the rule are treated as part of the same statement.

Rules get compiled into the query plans of their containing operations.
There is no way for a statement to complete before it's fired off rules
complete.  At least this was true as of Ingres 6.5. I can't garantee that CA
hasn't changed this but I would be supprised as this would be very much a
change in the architecture of how rules were implemented.  I don't have any
answer for what is going wrong with your inserts and deletes.




Wed, 26 Aug 1998 03:00:00 GMT
 rules and transactions

Quote:

>The problem we face here looks similar to this:

>We have a DB with about eight tables t1...t8.

>t1 can be considered to be the "main" relation, the other
>tables are subordinate to this table and can be joined on
>a common key.

>We now created a rule after delete on t1, that deletes
>the associated data in the other tables t2-t8.

>So far, it is ok, but: we have one application that
>updates any attribute in anyone of the app. 8 tables.
>It does this by deleting the data set in t1 (and
>triggering the deletion in the other tables). After
>this an insert with the same key is issued.

>It looks like this:

>   delete from t1 where key=something;
>             -> rule gets triggered:
>                -> delete from t2 where key=something;
>                   delete from t3 where key=something;
>                   ...
>                   delete from t8 where key=something;

>   insert into t1 (...) where key=something;
>   insert into t2 (...) where key=something;
>   ...

>   commit;

>_Sometimes_ we get an error: Duplicate key or
>duplicate rows found. Obviously, the procedure
>triggered by the delete rule has not yet completed
>the deletes when the insert statement starts.

>The documentation says:

>     The statement that fires a rule and the database procedure invoked
>     by the rule are treated as part of the same statement.

>I read this as: "The deletes (triggered by the rule) have completed
>when the delete statement (triggering the rule) has completed."
>Or: When "delete from t1 is completed, then the deletes from t2 to tn
>have also completed"...

>And further:

>     The database procedure is executed before the statement that
>     fired the rule completes.
>     [SQL Reference Manual, Chapter 6, "Rules"]

>Same conclusion...

>Actually, it is not a big problem for us today, as the rule is not
>important (it was just a convenient thing when developing on that
>database), and additionally I could think of some workarounds, but:
>where is the bug? A documentation error, an implementation error or
>an error in my understanding?

>Comments are welcome...

>BTW: Our System: OI 1.1/04 on SunOS 4.1.3

>Thomas Mack
>TU Braunschweig, Abt. Datenbanken

Just to clarify:  when the manual says "complete", it means "made permanent.
In the above example, for the delete that triggers the rule, the row has already
been removed from the table before any of the rule-triggered deletes occur.  If
there are no errors in any part of the set, then the entire set of statements
is made permanent (within the construct of the transaction).  A different way
to state it is that "either all the statements within this grouping execute
successfully, or they are rolled back".

As far as getting the duplicate key problem, that should not be caused by what
you have described, unless perhaps something in the delete set fails.  Be sure
you are checking for errors after your initial delete statement.
--

Steve Caswell           | (770) 448-7727       | "The opinions expressed are my

The Palmer Group        | http://www.tpghq.com |  but they're all I've got."



Fri, 28 Aug 1998 03:00:00 GMT
 rules and transactions

Quote:


>As far as getting the duplicate key problem, that should not be caused by what
>you have described, unless perhaps something in the delete set fails.  Be sure
>you are checking for errors after your initial delete statement.

You are right - as indicated in another answer, the problem is,
that the delete statement itself fails. And it fails SOMETIMES,
because it obviously does not like a delete statement in the
triggered procedure:

create procedure del_dok (
   doknr=varchar(10) not null
) as
begin
   delete from titelwoerter
   where id in (
      select id from titelwortidx
      where id in (
         select distinct id from titelwortidx
         where doknr = :doknr
      )
      group by id
      having count(id) = 1
   );
end;

Sometimes it fails with rowcount == -1 and errornumber == 0(!)
and no errormessage, sometimes it fails with a message:

E_LQ0009 Communications or transmission error received without
text. The generic error number is 39100, the local error is
590342, and the number of parameters is 0 (should be 1).

(bringing the server down by this). And 80% of the time it works
without any problems...

And - I checked - the problem does not seem to exist, if I
remove the rule (and procedure) issue the statements directly
in the 4gl code.

I will be talking with tech support on this...

Thanks for your answers,

Thomas Mack
TU Braunschweig, Abt. Datenbanken



Fri, 04 Sep 1998 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. transaction rule book???

2. Does SQL 2000 offer Server based Rules similar to the Rules Wizard

3. Rules triggered by rules

4. why there is cost when I am running with RULE hint or optimizer_goal=RULE

5. Soft Rules Rule System Beta

6. One Big Rule or Many Small Rules

7. distributed transaction,nested transactions,sp_getapplock

8. SQL Server transaction and ADO transaction

9. New transaction cannot enlist in the specified transaction coordinator

10. ERROR:New transaction cannot enlist in the specified transaction

11. New transaction cannot enlist in the specified transaction coordinator

12. New transaction cannot enlist in the specified transaction coordinator


 
Powered by phpBB® Forum Software