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

.

Relevant Pages

  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... the file then that inode would be added to the journal list and the pages ...
    (Linux-Kernel)
  • Re: Behavior of Connection.commit()
    ... the DBMS message when when the DBMS has already killed the whole tx, and in that case, throw an exception from any subsequent connection, statement or result set method that would/could do an update or query saying "The DBMS has killed the current transaction. ... The user application may depend on holding locks in order, and once it is allowed to proceed obtaining the lock for data 2, it may then go on to obtain other locks before trying to commit, and this not-expected order of locking may cause deadlocks that could kill other innocent, correct transactions. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... delayed allocation, starting a new transaction could to happen a lot to ...
    (Linux-Kernel)
  • Re: [PATCH 2/2] improve ext3 fsync batching
    ... array than it takes to complete the transaction. ... when commit times go up to seconds? ... Transactions on that busier drive would take longer, we would sleep ... longer which would allow us to batch up more into one transaction. ...
    (Linux-Kernel)
  • Re: Backups and Transaction Log file size
    ... It sounds like a classic case of a long running open transaction. ... Find the client and either commit or roll it back. ... If you know it is a garbage connection you can kill the SPID and it will roll back any changes that the SPID may have open and allow you to backup and truncate properly. ... Once the committed trans have been ...
    (microsoft.public.sqlserver.setup)