Shulou(Shulou.com)06/01 Report--

This article mainly explains the use of mysql transaction management, the content is clear, interested friends can learn, I believe it will be helpful after reading.

What is transaction management:

A series of operations to be performed can be called transactions, and transaction management is to manage these operations either completely or not (a classic example is that A transfers money to B, first of all, A's money is reduced, but the sudden power outage in the database makes it impossible to add money to B, and then due to the loss of data, B does not admit to receiving A's money. The business here is to ensure that both the increase and the reduction of money are fully implemented or not implemented at all, and if the increase fails, then the reduction will not occur. The significance of transaction management: to ensure the integrity of data operations. Not all data engines in mysql support transaction management, only innodb supports transaction management. The characteristics of transaction management: atomicity: the whole operation of a transaction is a whole and cannot be divided, either all succeed or all fail. Consistency: the data in the data table does not change before and after the transaction operation. Isolation: transaction operations are isolated from each other. Persistence: once the data is submitted, it is immutable and permanently changes the data in the data table. Transaction management operation: turn on transaction management: once enabled, the following sql statement is not immediately executed and the results are written to the table, but to the transaction log. Start transaction; fallback operation: fallback clears what is written to the transaction log after transaction management is started, that is, before transaction management is turned on. Syntax: rollback; Note: the fallback operation is only a fallback of "write" content, not for ordinary meter reading select statements. Transaction commit: writes the results of the sql statement to the data table. Syntax: commit:

Experimental table:

Create table bankaccount (id int primary key auto_increment,name varchar (15), money int); insert into bankaccount (name,money) values ("Jobs", 2000); insert into bankaccount (name,money) values ("Bill", 3000)

Add: when a commit or rollback statement is executed, the transaction is automatically closed (future changes are implicitly committed). Locking mechanism: when a transaction operates on a table, if an index is used to take a value, it will be locked to the corresponding row; if an index is not used to take a value, then the entire table will be locked. After locking, other connections cannot manipulate the specified row or table. Rollback point: the rollback point can specify the location of the rollback rollback (for example, if you type 100 commands and find that the 81st command is wrong, you can save a lot of time if you roll back to the point before the 81 command instead of before starting the transaction. Syntax: create rollback point: savepoint rollback point name; rollback to rollback point: rollback to rollback point name

Add: rollback points fail after transaction management is turned off (after rollback or commit). Do not use rollback points outside the transaction. Default transaction management: by default, transaction management for mysql is turned off (automatic transactions), and the results of the statements are immediately written to the data table. You can check whether automatic transactions are enabled through show variable like 'autocommit';. A value of 1 means that automatic transactions are on, and 0 means that automatic transactions are turned off. Turn off automatic transactions: set autocommit = 0; [commit is required to execute each statement after closing, which is equivalent to starting transaction management] but note that set autocommit is for session variables, so this setting only takes effect in this session connection. After reading the above content, do you have a better understanding of the use of mysql transaction management? if you want to learn more, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.