Difference between COMMIT and ROLLBACK 3

[grnzbra]

We have two possible transactions. I'm trying to decide which I should use? They will be covering an UPDATE statement followed by a DELETE statement.

BEGIN TRAN A
[indent]do some stuff[/indent]
COMMIT

BEGIN TRAN B
[indent] do some other stuff[/indent]
ROLLBACK

What is the difference?.

When is the stuff done? With the COMMIT is the stuff just set up during the "do stuff" statement and actually done when the COMMIT statement runs. What happens if it fails during the COMMIT?
With the ROLLBACK is the stuff done on the do "some other stuff" statement and then, if something goes wrong, the transaction is reversed?

 

[Andrzejek]

If there is no other COMMIT's or ROLLBACK's, the way I see it is:

[pre]
BEGIN TRAN A
do some stuff
COMMIT [green]-- all 'stuff' is sent to the DB, no turning back[/green]

BEGIN TRAN B
do some other stuff
ROLLBACK [green] -- all 'stuff' is ignored since last COMMIT, no DB changes[/green]
[/pre]

>What happens if it fails during the COMMIT?
It doesn’t. If anything is wrong, it will fail on ‘do some stuff’ before it gets to COMMIT

I my mind, you are working on your own ‘copy’ of the DB while ‘do some stuff’, you can even see the changes: deletes, updates, inserts, etc. before the COMMIT, but nobody else can see it. All other users of DB will have your changes after COMMIT

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Mike Lewis]

The short answer to your question is that COMMIT and ROLLBACK are the opposite of each other. As Andy has pointed out, COMMIT sends your updates to the database (as its name suggests); ROLLBACK cancels the changes.

The code that you posted does not follow the normal pattern. The following would be more usual:

BEGIN TRANSACTION
  do some stuff 
  IF an error has occurred
    BEGIN
      ROLLBACK TRANSACTION
      exit the process
    END 
  END
COMMIT TRANSACTION

In other words, you woudl normally COMMIT the transaction if all has gone well. But if you detect an error at any stage, you would do a ROLLBACK. The aim is to avoid a situation where part of the update has succeeded but another part failed.

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[grnzbra]

I was not thinking in terms of running the first and then the second statements. I just wanted to present the two statements and find out what went on with each one.
So, if I understand correctly, the COMMIT statement actually implements the statements within the transaction and nothing can go wrong during the COMMIT? In that case, what would the ROLLBACK version be used for? (These are two entirely different statements, only one of which is going to be run).

 

[Mike Lewis]

Let me give you an example. Suppose you have an application that handles bank transactions. You receive a request to transfer a certain amount of money from Account A to Account B. So your code first reduces the balance of Account A, and then increases - by the same amount - the balance of Account B (or it could do it the other way round; it would make no difference).

So, your code might look like this:

UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = 'A'
UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = 'B'

So far so good. But suppose an error occurs after the money leaves A but before it arrives at B. The money has disappeared and the accounts no longer balance - a definite no-no in banking circles.

The solution is to wrap the whole thing in a transaction:

BEGIN TRANSACTION

UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = 'A'
UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = 'B'

COMMIT TRANSACTION

Now, if an error occurs, neither update will be committed, and so the accounts are still in balance.

Of course, if an error occurs, you need to do something about it: to report it in some way. So you could actually test for the error, take some action, and then explicitly roll back the transaction:

BEGIN TRANSACTION

UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = 'A'
IF @@ERROR > 0
  -- Report the error in some way
  ROLLBACK TRANSACTION
  RETURN
END

UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = 'B'
IF @@ERROR > 0
  -- Report the error in some way
  ROLLBACK TRANSACTION
  RETURN
END

COMMIT TRANSACTION

Does that help at all?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[grnzbra]

Thank you everyone. This has been most helpful.