Re: Ghost update in concurrent transaction

"Sudin" <sudinkc_at_yahoo.com> wrote in message news:1dcfba52.0408291014.303025b_at_posting.google.com...
> Hello can anyone explain what ghost update does in a concurrent
> transaction? What I haven't understood is suppose an integrity
> constraint exists which says x+y+z = 1000 and concurrent transaction
> table below:
> ========
> Transaction1 Transaction2
> bot
> r1(x)
> bot
> r2(y)
> r1(y)
> y = y - 100
> r2(z)
> z = z + 100
> w2(y)
> w2(z)
> commit
> r1(z)
> s = x+y+z
> commit
> =======
> The textbook explains this as to what Ghost update is:
> Transaction2 doesn't violate integrity constraint whereas the
> Transaction1 (variable s) becomes 1100 ?? and then transaction1
> observes only "some" of the effects of Transaction2 & thus has a state
> the doesn't satisfy integrity constraint. And that is Ghost Update.
>
> Now what i partly understand in the above transaction as:
> 1. t1 starts with read x
> 2. t2 starts with read y
> 3. t1 reads y
> 4. t2 decreases y by 100
> 5. t2 reads z
> 6. t2 increases z by 100
> 7. t2 writes y & z
> 8. t2 commit ( so this means y,z values are already written to the
> disk?
> and Transaction2 ends??)
> 9. t1 reads z which has been increased by 100 (z+100)
> 10. t1 assigns s = x+y+ (z+100)
> 11. t1 ends with commit.
>
> So now Ghost update exist because of the written value of z (i.e
> z+100) & the integrity constraint between them but isn't that what is
> to be assumed in a transaction ( one transaction reads, writes,
> commits & the other transactions reads its value from the disk??? ) Is
> this a ghost update because of the integrity constraint that says the
> total must be 1000?
> I would appreciate ur help. :)

Hi Sudin,

I am assuming that the original values (before T1 and T2) are such that x+y+z = 1000.

Might I suggest that you create two serial schedules, each reflecting a serial execution of one transaction and then the other (T1->T2 and then T2->T1), with no interference of interleaving by the other transaction. If you work through these two scenarios, you will see that in either case, the integrity constraint is not violated by either transaction at commit time. In other words, the variable s would have a value of 1000 for T1 in either case, and x+y+z would still evaluate to 1000 for T2 in either case.

The issue is not really the integrity constraint (in fact, the mode of integrity constraint as either deferred or immediate might have an effect on the effectiveness of the check), but the fact that the schedule involves the interleaving of operations of T1 and T2 and thefore allows for a condition where the two transactions interfere with one another and results in inconsistency. We can determine this because we can define a consistent transition of states by using a formal criterion based on what would happen if transactions were only allowed to occured in a serial fashion (T1 and then T2, or vice versa), totally isolated from one another. Since the result of the "Ghost Update" schedule does not produce a final state that is equivalent to a schedule that is serial across transactions, we can determine that the *isolation* of each transaction is circumspect. Since the effects of the interleaved schedule presented by the textbook are not equivalent to a serial schedule, the schedule is not considered to be *serializable*.

The "Ghost Update" is really another term for the "Inconsistent Analysis" problem.

BTW, in SQL-92 parlance, this problem seems to imply that the isolation level defined across transactions is *Read Uncommited* because we can see that both a dirty read and a non-repeatable read are allowed.

> cheers
> Sudin.

HTH, Dan G. Received on Mon Aug 30 2004 - 04:46:49 CEST