Example of defining MySQL transactions

The MySQL transaction should be defined by every MySQL database user. The following describes how to define MySQL transactions, which is helpful to you.

By default, MySQL runs a COMMIT statement after each SQL statement is executed to effectively separate each statement into a transaction.
In complex application scenarios, this method cannot meet the requirements.
To open a transaction and allow execution of multiple statements before COMMIT and ROLLBACK, we need to do the following two steps:
1. Set the autocommit attribute of MySQL to 0. The default value is 1.
2. Use the start transaction statement to explicitly open a TRANSACTION.

If a TRANSACTION has been opened, SET autocommit = 0 does not work, because start transaction implicitly commits all the current changes in the session, ends the existing TRANSACTION, and opens a new TRANSACTION.

Example of a stored procedure using the set autocommit statement:

 
 

  
  
CREATE PROCEDURE tfer_funds      
  
  
     (from_account int, to_account int, tfer_amount numeric(10,2))      
  
  
BEGIN      
  
  
     SET autocommit=0;      
  
  
     
  
  
     UPDATE account_balance SET balancebalance=balance-tfer_amount WHERE account_id=from_account;      
  
  
     
  
  
     UPDATE account_balance SET balancebalance=balance+tfer_amount WHERE account_id=to_account;      
  
  
     
  
  
     COMMIT;      
  
  
END;    
 
 

The preceding describes how to define MySQL transactions.

Mysql Stored Procedure FAQs

Example of creating a MySQL Stored Procedure

Mysql Stored Procedure Call Method

Use of mysql ifnull Functions

Learn more about MySQL GROUP_CONCAT Functions