MYSQL basics: basic operations for Transaction Processing

MYSQL Getting Started: Link to basic transaction processing: MYSQL Getting Started: basic operations http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.htmlMYSQL Entry 4: MYSQL Data Types http://www.bkjia.com/database/201212/175536.htmlMYSQL Entry 5: MYSQL Character Set http://www.bkjia.com/database/201212/175541.htmlMYSQL Getting started 6: MYSQL Operators http://www.bkjia.com/database/201212/175862.htmlMYSQL Entry 7: MYSQL common functions http://www.bkjia.com/database/201212/175864.htmlMYSQL Step 8: basic database and table operations http://www.bkjia.com/database/201212/175867.htmlMYSQL 9: simple indexing operations http://www.bkjia.com/database/201212/176772.htmlMYSQL 10: basic view operations http://www.bkjia.com/database/201212/176775.htmlMYSQL Quick Start 11: Basic trigger operations http://www.bkjia.com/database/201212/176781.htmlMYSQL 12: basic operations of Stored Procedures http://www.bkjia.com/database/201212/177380.htmlMYSQL Step 13: basic operations for custom functions http://www.bkjia.com/database/201212/177382.htmlMYSQL 14: basic operations on cursors http://www.bkjia.com/database/201212/177384.html Transaction processing (transaction processing) can be used to maintain the integrity of the database. It ensures that batch MySQL operations are either completely executed or not executed at all. Www.2cto.com, but not all database engines Support transaction processing. MyISAM and InnoDB are two of the most commonly used engines. The former does not support clear transaction processing management, while the latter does. 1. terms related to transaction processing transactions (transaction) refer to a group of SQL statements; rollback refers to the process of revoking a specified SQL statement; commit) writing unstored SQL statement results to the database table; savepoint refers to the temporary placeholder (placeholder) set in transaction processing ), you can release rollback for it (unlike the rollback process for the entire transaction ). 2. The key to controlling transaction processing is to extract SQL statement components into logical blocks and specify when data should be rolled back and when data should not be rolled back. 1. start transaction. Use the preceding statement to identify the START of the TRANSACTION. 2. Use the ROLLBACK command to roll back (UNDO) the MySQL statement. The following example starts a TRANSACTION, then, all contents in the table test_inn are deleted, and the table content is displayed, indicating that the table has been cleared. However, after the ROLLBACK transaction is executed, the data in the table is re-queried. The data in the table is still: [SQL] mysql> start transaction; mysql> delete from test_inn; mysql> select * from test_inn; Empty set (0.00 sec) mysql> rollback; mysql> select * from test_inn; + ------ + ------------ + | id | birthday | + ------ + ------------ + | 1 | 2012-12-12 | 1 | 0000-00-00 | 1 | 2012-12-00 | 1 | 0000-00 -00 | + ------ + ------------ + mysql> show create table test_inn; + ---------- + ----------------------- ----- | Table | Create Table + ---------- + -------------------------- | test_inn | create table 'test _ inn '('id' int (11) default null, 'birthday' date default null) ENGINE = InnoDB default charset = latin1 | + ---------- + ---------------------------- note: In the preceding example, table test_inn uses the InnoDB ENGINE. If a table using the MyISAM ENGINE performs the preceding operations, even after ROLLBACK, we can find that the data is actually cleared. 3. Generally, MySQL statements using COMMIT are implicitly submitted (implicit commit), that is, the COMMIT (write or save) operation is automatically performed. However, commit is not performed implicitly in the transaction processing block. The COMMIT statement is required for explicit submission. Example: [SQL] mysql> start transaction; mysql> delete from test_inn; mysql> commit; When a COMMIT or ROLLBACK statement is executed, the started transaction is automatically closed.
4. Use reserved points in order to support the rollback of some transactions, the reserved points must be placed in a proper position in the transaction processing block. Create retention point: SAVEPOINT sap_name; roll back TO retention point: rollback to sap_name; RELEASE retention point: RELEASE sp_name; retention point can also be completed in the transaction processing (execute a ROLLBACK or COMMIT) and then automatically released. 5. Change the default submission Behavior SET autocommit = 0. The autocommit flag is for each connection, not the server. Reference: MySQL required knowledge