Implementation method steps of MySQL transaction processing
Description of requirements:
Case background: accidents are inevitable in the process of bank transfer. In order to avoid unnecessary losses caused by accidents, transactional processing is used:
An account has a balance of 1000 yuan and transfers 500 yuan to B account with a balance of 200. It may be for some reason:
An account has an error while deducting the transfer amount, using transaction rollback to return to the initial state
After account A successfully deducts the transfer amount, there is an error in adding the transfer amount to account B, and the transaction is used to roll back to the initial state.
Tip: first build the data table account, and the fields include name (username) and balance (money), and then make use of the above two cases respectively.
# create account table CREATE TABLE IF NOT EXISTS account (id INT (11) NOT NULL AUTO_INCREMENT PRIMARY KEY, username VARCHAR (32) NOT NULL, money DECIMAL (9Magazine 2)) ENGINE=INNODB; # insert user data INSERT INTO account (username,money) VALUES ('Achievement Magazine 1000.00); INSERT INTO account (username,money) VALUES (' Bamboo Magazine 200.00); / * transaction * / # An account remittance failed SELECT * FROM account # step 1 turn off the transaction auto-commit mode SET autocommit=0; # step 2 start the transaction START TRANSACTION; # step 3 find the remittance failed and roll back the transaction ROLLBACK | | the remittance successfully commit the event # assume syntax error UPDATE account SET money=money-500 WHERE username='A'; SELECT * FROM account; UPDATE account SET money=money+200 WHERE username='B'; ROLLBACK; # step 4 restore the autocommit SET autocommit=1; SELECT * FROM account of the Mysql database / * B failed to receive remittance * / SELECT * FROM account SET autocommit = 0; START TRANSACTION; UPDATE account SET money=money-500 WHERE username='A'; SELECT * FROM account; # assumes syntax error UPDATE account SET money=money+200 WHERE username=' B'; ROLLBACK; SET autocommit = 1; SELECT * FROM account; # clears data TRUNCATE account
Note:
An explanation of creating a data table using IF NOT EXISTS
