Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Got those old Mutation Blues

Re: Got those old Mutation Blues

From: Richard Shea <rshea_at_my-deja.com>
Date: 21 Nov 2001 02:11:14 -0800
Message-ID: <43160f6f.0111210211.577d64f4@posting.google.com>


Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9tb56m01ica_at_drn.newsguy.com>...
> In article <43160f6f.0111181703.387b5b50_at_posting.google.com>, rshea_at_my-deja.com
> says...
> >

[original question (written by me) snipped]
>
> 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 --

Yeah you're quite right. I'd got rather focussed on the current application problem which always only involves one update per transaction but I appreciate that trigger ought to be setup to handle a more general situation than that.

>
> 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.
>

OK. I'll give it a spin. Thanks for taking the time to give such a detailed answer, very helpful, I appreciate it.

regards

richard shea. Received on Wed Nov 21 2001 - 04:11:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US