Tutorial Addendum on SQL - Transaction Administration

This affiliate describes:

    

    
• What is a transaction?
  
      



    
• How does MySQL abutment of transaction management?
  
      



    
• What are transaction abreast levels.
  
      



    
• How does MySQL adumbrate uncommitted changes from additional applicant sessions?
  
      



    
• How does MySQL supports transaction abreast levels?
  
      



    
• MySQL transaction abreast akin tests - apprehend uncommitted, apprehend committed, and
  
      

  repeatable read.
  
      



    

All addendum in affiliate are based MySQL 5.0 server.

    

What Is A Transaction

    

Transaction - A arrangement of executions of SQL statements that can be advised

    

as a individual assemblage in which all data changes can be committed or annulled

    

as a whole.

    

Most database servers action two transaction administration modes:

    

    
• Auto Accomplish On: Anniversary SQL account is a transaction. Data changes resulted
  
      

  from anniversary account are automatically committed.
  
      



    
• Auto Accomplish Off: Affairs are absolutely started and concluded by
  
      

  the applicant program. Data changes are not committed unless requested by the
  
      

  client program.
  
      



    

Most database server supports the afterward statements for transaction management:

    

    
• Commit Account - To accomplish all changes in the accepted transaction.
  
      



    
• Rollback Account - To acknowledgment all changes in the accepted transaction.
  
      



    
• Start Transaction Account - To alpha a new transaction.
  
      



    

MySQL Abutment of Transaction Management

    

MySQL abutment of transaction administration follows the afterward rules:

    

    
• Only two accumulator engines abutment transaction management: InnoDB and BDB.
  
      



    
• The absence accumulator engine, MyISAM, doesn t abutment transaction management.
  
      



    
• To force a table to use a non-default accumulator engine, you haveto specify
  
      

  the engine name in the "create table" statement.
  
      



    

Statements accompanying to transaction management:

    

    
SET AUTOCOMMIT = 0 | 1;

    
START TRANSACTOIN;

    
COMMIT;

    
ROLLBACK;

    

Note that:

    

    
• SET AUTOCOMMIT = 1 - Turns on the auto-commit option. It aswell commits and terminates
  
      

  the accepted transaction.
  
      



    
• SET AUTOCOMMIT = 0 - Turns off the auto-commit option. It aswell starts a new transaction
  
      



    
• By default, auto-commit advantage is angry on if a new affair is established.
  
      



    
• COMMIT - Commits the accepted transaction.
  
      



    
• ROLLBACK - Rolls aback the accepted transaction.
  
      



    
• START TRANSACTION - Commits the accepted transaction and starts a new transaction.