MySQL Transaction implementation method steps

Requirements Description:

Case background: In the course of bank transfer, accidents are unavoidable. To avoid unnecessary losses caused by an accident, the transaction is handled in the same way:

A account has an existing balance of $1000 and a transfer of $500 to a balance of 200 B accounts. Possible for a reason:

A account has an error while deducting the transfer amount, using a transaction rollback to return to the initial state

A after the account has successfully deducted the transfer amount, the B account adds the transfer amount error, using the transaction rollback to the initial state

Tip: First build the data table account, the field includes the name (username), the balance (money), and then use the transaction to handle the above two cases respectively.

[SQL]View PlainCopy 

2. #创建账户表
4. CREATE TABLE IF not EXISTS account (
6. ID INT (one)not NULL auto_increment PRIMARY KEY,
8. Username VARCHAR (+)not NULL,
10. Money DECIMAL (9,2)
12. ) Engine=innodb;
14. #插入用户数据
16. INSERT into account ( Username,money)VALUES (' A ', 1000.00);
18. INSERT into account ( Username,money)VALUES (' B ', 200.00);
22. /* Transaction Processing */
24. # A account remittance failed
26. SELECT * from account ;
28. #第一步 Turn off transaction autocommit mode
30. SET autocommit=0;
32. #第二步 Start a transaction
34. START TRANSACTION;
36. #第三步 Find the remittance failed, rollback the transaction ROLLBACK | | Remittance successfully commits the event
38. #假设语法错误
40. UPDATE account SET money=money-500 WHERE username=' A ';
42. SELECT * from account ;
44. UPDATE account SET money=money+200 WHERE username=' B ';
46. ROLLBACK;
48. #第四步 Restore the automatic submission of MySQL database
50. SET autocommit=1;
52. SELECT * from account ;
58. /*B Receive remittance failure */
60. SELECT * from account;
62. SET autocommit = 0;
64. START TRANSACTION;
66. UPDATE account SET money=money-500 WHERE username=' A ';
68. SELECT * from account ;
70. #假设语法错误
72. UPDATE account SET money=money+200 WHERE username =' B ';
74. ROLLBACK;
76. SET autocommit = 1;
78. SELECT * from account ;
82. #清除数据
84. <pre name="code" class="sql" >TRUNCATE Account;

MySQL Transaction implementation method steps