Re: Got those old Mutation Blues

In article <43160f6f.0111181703.387b5b50_at_posting.google.com>, rshea_at_my-deja.com says...
>
>Hi - I've got a table that looks a bit like this ...
>
>-- where entryid is a PK
>entryid NUMBER
>personid NUMBER,
>startdatetime DATE,
>enddatetime DATE
>
>... if you think of each record as a diary entry for an individual
>that would be a good model.
>
>I'd like to ensure that no person allocates the same period in the day
>twice. I tried to enforce this via a row level trigger (by doing a
>SELECT on the table for records belonging to the same person covering
>the same time period)and I got a mutation error when I do an update so
>I read ...
>
>http://govt.oracle.com/~tkyte/Mutate/index.html
>
>... plus the technet link and now understand a little more. BUT ! My
>question is this.
>
>If in the trigger I explicitly avoid selecting the record I'm changing
>(by having a 'entryid != :new.entryid') why does that still provoke a
>mutation error ? I mean surely it can be determined that I'm not
>involving any record that's changing ? Or to put it another way that
>I'm happy to ignore the record that is changing ?
>
>I'm not just philosphising here I was hoping someone could confirm
>that even using a 'entryid != :new.entryid' type clause clause I
>should still be getting a mutation error ? I'm wondering if I've made
>mistake elsewhere.
>

the table is mutating - that is why you cannot read it. Its to protect you from yourself actually. Suppose you had this data:

>-- where entryid is a PK
>entryid NUMBER
>personid NUMBER,
>startdatetime DATE,
>enddatetime DATE

entryid         personid         startdatetime           enddatetime
1               1                11am monday             12pm monday
2               1                12:30pm monday          1:30pm monday

and you issue an update like:

update t
  set startdatetime = startdatetime+2/24,

      enddatetime = enddatetime+2/24
 where personid = 1;

(we need to push this persons schedule back 2 hours -- their plane is late).

Well, if we let you read this mutating table AND we updated the rows in the order they are depicted above -- it would fail!

11am would becom 1pm -- that overlaps with entryid 2.

OTOH, if the rows were updated in the other other -- it would succeed! 12:30 would become 2:30 and then 11am would become 1pm -- everthing is OK.

The physical order in which the rows were modified would affect the OUTCOME -- this is especially important when you use this information to derive OTHER information -- not just enforce business rules are you are. In that case -- the answers you derive can be different depending on the order of rows processed in the table! imagine debugging that one (well on my system I get x=5 but on this other system with the same data was get x=10, whats going on there?)

So, the mutating table rule is there to remove this ambiguity.

You need to postpone this check until the END. That should be easy, you'll store the entryid in a plsql table as described on my site (the link you have). You'll be able to query up any overlaps that are NOT that entryid easily.

If you are interested and have my book -- you might read pages 30 - 32. It describes your case exactly and also points out another "bug" that might be waiting for you! With multi-versioning, your trigger won't see other peoples inserts/updates! So, if two people simultaneously hit the enter key and schedule this guy for a 12pm meeting -- they BOTH will win since NEITHER will see the others inserts. You'll need to serialize these modifications at the resource level (the person level in this case) using DBMS_LOCK or a select for update on the person row.

>any help would be appreciated.
>
>richard shea
>rshea_at_my-deja.com

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp