Should I Commit or Rollback a Read Transaction

Should I commit or rollback a read transaction?

You commit. Period. There's no other sensible alternative. If you started a transaction, you should close it. Committing releases any locks you may have had, and is equally sensible with ReadUncommitted or Serializable isolation levels. Relying on implicit rollback - while perhaps technically equivalent - is just poor form.

If that hasn't convinced you, just imagine the next guy who inserts an update statement in the middle of your code, and has to track down the implicit rollback that occurs and removes his data.

After a read-only database transaction is it better to commit or rollback?

Some RDMS (at least Oracle ) have READ ONLY isolation level. Otherwise, I prefer ROLLBACK since it doesn't create an entry in transaction log (a bit better from performance point of view).

Update
Some explanations.

Most RDMS log all committed transactions and changes associated with transaction. Even if no changes are made, server logs a transaction. I don't see any points in wasting server resources by storing useless data.

Why do we need a Rollback command while making a transaction or tell me a when is it appropriate to use ROLLBACK

Using the rollback method, you can close the transaction, reverting all associated changes made to the database.
Eg:

Above diagram illustrate the three query at a time.Tansaction of query1 & query2 were successful but query3 got error. And when query3 got error all transaction will revert.

Transaction: commit() vs rollBack()

Both of them seems identical to me

That's wrong. Transaction by definition is Atomic in nature means either it will happen and succeed executing all commands in the group or none at all. If it's successful and you want to persist the change then COMMIT else if any of the statement in the group fails then ROLLBACK to get back to pristine state.

So in your case, you would want to have all the below statement execute successfully and if that then COMMIT to persist the change but if any of the statement fails for any so called reason then it may end up giving a undesired result which you don't want to persist and so ROLLBACK and get back to previous consistent state.

$sth1 = $dbh->exec("DROP TABLE fruit");
$sth2 = $dbh->exec("UPDATE dessert SET name = 'hamburger'");
$sth3 = $dbh->exec("INSERT INTO names(id, name) VALUES (NULL, 'peter')");

Read about Transaction and also see this another post PHP + MySQL transactions examples

Read Committed vs Read Uncommited if both transaction do not rollback

If you work with read committed isolation level, T2 needs to wait on step 4 for T1 to finish and commit its work. furthermore T1 in step 6 cannot find Nome with Maria% thus, deletes 0 rows.

but on read uncommitted isolation level, both read/write operations can be done simultaneously.

Result For read committed isolation level,

Pessoas (Jaoa Silva, 96.....)
Pessoas (Maria Fon..., 9199...)
Pessoas (Joao Manuel Silva, 9699...)

whereas for read uncommitted isolation level

Pessoas (Joao Manuel Silva, 9699...)

Does ROLLBACK TRANSACTION imply a commit if not rolled-back

No. The transaction will remain open and uncommitted until it is committed, which will probably eventually cause blocking on your database.

If transactions are nested, rollback will rollback to the beginning of the outer transaction, not the inner transaction - See http://msdn.microsoft.com/en-us/library/ms181299.aspx

I would consider a structure something like

 begin try
      begin tran
      -- do query
      commit tran
 end try
 begin catch
      if @@trancount>0 
      begin
          rollback
      end
      -- handle error here
 end catch

---

Related Topics