Re: Commit after transacton is completed

On Fri, 25 Nov 2005 00:23:28 -0800, Pradeep wrote:

> Expert-one-on-one mentions that we should commit only after the
> transaction completes, otherwise it takes more time and generates more
> redo. I agree to it. But suppose if my transaction takes a few minutes
> to complete, i will be holding locks for that time and other people
> will be reading the history data. So isn't this the disadvantage of
> commiting after transaction??
>

Absolutely - it is a HUGE disadvantage to have readers scan the data as it was before the transaction started. By forcing the users to go to the before-TX image, you lead to the following banking scenario:

T1:  Acct1  $500     Acct2 $200    User: Rqst to transfer $200 to Acct2
T2:                                Network delays
T3:  Acct1  $300     Acct2 $200    Syst: Remove $200 from Acct 1
T4:                                Network delays
T5:  Acct1  $300     Acct2 $400    Syst: Add the above $200  
T6:                                Syst: Commit

and any other user would read either the T1 data before commit or the T6 data after commit.

The alternate, releasing locks (by intermediate commits) to avoid reading history allows the bank to report the T3 data (and consider that accurate).

Personally I'd prefer to display a balance of $700 at any time from T1 to T6, and the only way I can do that is to go to the 'before tx start' information until the transaction has committed.

I happen to like the definition of database as 'a mechanism of storing a model of the real world' and transaction as 'the steps involved in the transition from one real-world state to another'. In my world, the $500 balance at T3 and T4 do not model the real world - the balance at any time MUST be $700 or I will scream bloody murder at the bank.

But, yes, modeling the real world, restricting the model to represent only the real world, and forcing the rest of the users to go either (and only) the pre- or post- TX information can be a huge disadvantage.

(It might be serious if there was a discernable cost to those locks.)

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting replies guarantees I won't respond. ***