MySQL 10-1-transaction

MySQL experience 10-1-transaction 1. In the MySQL environment, a transaction is composed of one or more SQL statements as a separate unit. Each SQL statement in this unit is mutually dependent, and the Unit as a whole is inseparable. If a statement in a unit cannot be completed, the entire unit will be rolled back (UNDO), and all the affected data will be returned to the status before the start of the transaction. Therefore, the transaction can be successfully executed only when all statements in the transaction are successfully executed. Www.2cto.com 2. Not all storage engines support transactions, such as InnoDB and BDB, but MyISAM and MEMORY do not. Through InnoDB and BDB table types, the MySQL transaction system can fully meet the ACID test of Transaction Security. The old table type, such as MyISAM, does not support transactions. Therefore, transactions in this system can only be implemented through direct table locking. The term "ACID" is short for each transaction. The processing of each transaction must meet the ACID principle, that is, atomicity: A (atom), consistency: C (consistent), isolation: I (independence) durability: D (durability ). (1) atomicity means that each transaction must be considered an inseparable unit. Assume that a transaction is composed of two or more tasks. The statement must be successful at the same time before the transaction is considered successful. If the transaction fails, the system returns to the previous state of the transaction. In the example of adding an employee, atomicity means that it is impossible to add an employee to the employee database if the employee's payroll and department records are not created. The execution of the www.2cto.com atom is one or all or nothing. In an atomic operation, if any statement in the transaction fails, all the statements previously executed will be returned to ensure that the integrity of the data is not affected. This is particularly important in some key systems. real-world applications (such as financial systems: Transfers) must ensure that no data loss or data errors occur when performing data input or updates, to ensure data security. (2) consistency no matter whether the transaction is completed completely successfully or fails midway through, there is consistency when the transaction keeps the system in a consistent state. As shown in the preceding example, consistency means that if an employee is deleted from the system, all data related to the employee, including salary data and membership of the Group, will also be deleted. In MySQL, consistency is mainly handled by the MySQL Log Mechanism, which records all changes in the database and provides tracking records for transaction recovery. If an error occurs during transaction processing, the MySQL recovery process uses these logs to check whether the transaction has been fully successfully executed and whether the transaction needs to be returned. Therefore, the consistency attribute ensures that the database never returns an unfinished transaction. (3) Isolation refers to the isolation of each transaction in its own space from other transactions in the system, and the results of the transaction can be seen only when it is fully executed. Even if multiple transactions occur in such a system at the same time, the isolation principle ensures that the result of a specific transaction is invisible before it is completely completed. This is especially important when the system supports multiple simultaneous users and connections (such as MySQL. If the system does not follow this basic rule, a large amount of data may be damaged. For example, the integrity of the space of each transaction is quickly compromised by other conflicting firms. The only way to obtain absolute isolation is to ensure that only one user can access the database at any time. This is not an actual solution when dealing with RDBMS with multiple users like MySQL. However, most transaction systems use page-level locks or row-level locks to isolate changes between different transactions, at the cost of reducing performance. For example, the MySQL BDB table processing program uses page-level locking to ensure the security of processing multiple concurrent transactions. the InnoDB table processing program uses better row-level locking. (4) Persistence refers to the existence of a committed transaction even if the system crashes. When a transaction is completed and database logs are updated, durability begins to take effect. Most RDBMS products ensure data persistence by saving logs of all actions, which are used to change data in any way in the database. Database logs record all updates, queries, and reports to tables. If the system crashes or the data storage media is damaged, by using logs, the system can restore the last successful update before the restart, reflecting the changes of the transactions in the process during the crash. MySQL stores a binary transaction log file that records system changes in the transaction process for durability. If the hardware is damaged or the system is suddenly shut down, the lost data can be easily restored by using the last backup and log when the system is restarted. By default, the InnDB table is 100% persistent (all transactions performed by the system before the crash can be reliably restored during the recovery process ). The MyISAM table provides partial persistence. All changes made before the last flush tables command can be guaranteed to be stored on the disk. Www.2cto.com 3. A transaction is composed of a group of SQL statements, which are input by a user and terminated by modifying to a persistent state or rolling to the original state. In MySQL, when a session starts, the AUTOCOMMIT value of the system variable is 1, that is, the automatic submission function is enabled. After each user executes an SQL statement, the modification to the database by this statement is immediately committed to the persistent modification and saved to the disk, and a transaction ends. Therefore, you must disable automatic commit before a transaction can be composed of multiple SQL statements. The statement is SET @ AUTOCOMMIT = 0. After the statement is executed, you must explicitly indicate the termination of each transaction. modifications made to the database by the SQL statement in the transaction can become persistent modifications. For example, run the following statement: delete from xs where student ID = '000000'; SELECT * from xs; a row has been deleted FROM the execution result. However, this modification is not persistent because automatic submission is disabled. You can undo this modification through ROLLBACK, or use the COMMIT statement to persist this modification. 4. describes how to process a transaction in detail. 1). Start transaction when the first SQL statement of an application or the first SQL statement after the COMMIT or ROLLBACK statement (described later) is executed, a new transaction starts. In addition, you can use a start transaction statement to start a transaction. Syntax format: start transaction | BEGINWORK a begin work statement can be used to replace STARTTRANSACTION statements, but start transaction is more common. 2 ). the commit statement of the end transaction is a COMMIT statement that makes all the data executed since the beginning of the transaction a permanent part of the database and marks the end of a transaction. Its Syntax format is: COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] Note: The optional and chain clause immediately starts a new transaction at the end of the current transaction, and the new transaction has the same isolation level as the just-concluded transaction. After the Release clause terminates the current transaction, it will disconnect the server from the current client. Keywords containing NO can suppress CHAIN or RELEASE. Note: MySQL uses a flat transaction model, so nested transactions are not allowed. After the start transaction command is used in the first TRANSACTION, the first TRANSACTION is automatically committed when the second TRANSACTION starts. Similarly, the following MySQL statements implicitly execute a COMMIT command: ● drop database/drop table ● create index/drop index ● alter table/rename table ● lock tables/unlock tables ● set autocommit = 1 3 ). the Rollback statement of the Undo transaction is a undo statement that revokes the modification made by the transaction and ends the current transaction. Syntax format: ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] in the previous example, if you add the following statement at the end: ROLLBACKWORK; after this statement is executed, the previous Delete action is revoked. You can use the SELECT statement to check whether the row data is restored. 4). In addition TO revoking the entire transaction, you can use the rollback to statement TO roll back the transaction TO a certain point. Before that, you need TO use the savepoint statement TO set a save point. The SAVEPOINT syntax format is: SAVEPOINTidentifier (the name of the saved vertex ). The rollback to savepoint statement rolls back a transaction TO the named save point. If the current transaction changes the data after the save point is set, the changes will be undone in the rollback. Syntax format: ROLLBACK [WORK] to savepoint identifier when the transaction rolls back TO a save point, the saved point set after the save point is deleted. The RELEASESAVEPOINT statement deletes the named save point from a set of save points of the current transaction. Do not submit or roll back. If the save point does not exist, an error occurs. Syntax format: release savepoint identifier 5. Example: The following statements describe the TRANSACTION processing process: 1. start transaction 2. UPDATE... 3. DELETE... 4. SAVEPOINT S1; 5. DELETE... 6. rollback work to savepoint S1; 7. INSERT... 8. commit work; Description: In the preceding statements, 1st rows of statements start a transaction; 2nd and 3 rows of statements modify the data but are not submitted; row 4th sets a storage point. Row 5th deletes the data but does not commit the data. Row 6th rolls back the transaction to the storage point S1, and the modifications made by row 5th are revoked; 7th rows modified the data; 8th rows ended the transaction, and the changes made to the database in 2nd, 3, and 7 rows were persisted. 6. Each transaction has a so-called isolation level, which defines the degree of isolation and interaction between users. As mentioned above, one of the most important attributes of transaction RDBMS is that it can "isolate" different sessions being processed on the server. In a single user environment, this attribute does not matter because only one session is active at any time. However, in a multi-user environment, many RDBMS sessions are active at any given time. In this case, it is important for RDBMS to isolate transactions so that they do not affect each other and ensure that database performance is not affected. Www.2cto.com it is necessary to take some time to consider what will happen if isolation is not imposed. If there is no transaction isolation, different SELECT statements will retrieve different results in the same transaction environment, because during this period, data has basically been modified by other firms. This will lead to inconsistency, and it is difficult to trust the result set, so that the query results cannot be used as the basis for calculation. Therefore, isolation forces transaction isolation to some extent to ensure that the application sees consistent data in the transaction. Based on ANSI/iso SQL specifications, MySQL provides the following four isolation levels: serialization, repeatable read, and read committed) read uncommitted ). 7. Only the storage engine that supports transactions can define an isolation level. You can use the set transaction statement to define the isolation level. Syntax format: SET [GLOBAL | SESSION] TRANSACTION Isolationlevel SERIALIZABLE | repeatable read | read committed | read uncommitted Description: if GLOBAL is specified, the defined isolation level applies to all SQL users; if a SESSION is specified, the isolation level applies only to the currently running sessions and connections. MySQL is repeatable read isolation by default. (1) serialization if the isolation level is serialization, users can execute the current transaction in one order to provide the maximum isolation between transactions. (2) At this level, transactions are not considered as a sequence. However, the changes in the current transaction execution are still invisible. That is to say, if you execute the same SELECT statement several times in the same transaction, the results will always be the same. (3) committed read committed isolation-Level Security is worse than repeatable read isolation-level security. Not only can the transactions at this level view the new records added by other transactions, but also the changes made by other transactions to the existing records can be seen once they are committed. That is to say, this means that during the transaction processing, if other transactions modify the corresponding table, multiple SELECT statements of the same transaction may return different results. (4) uncommitted read www.2cto.com provides minimum isolation between transactions. In addition to illusory read operations and repeated read operations, transactions at this isolation level can read data that has not been committed by other transactions, if this transaction uses changes not committed by other transactions as the basis for computing, and those uncommitted changes are revoked by their parent transactions, this leads to a large amount of data changes. By default, the value of this system variable is SET based on each session, but you can modify the value of this GLOBAL system variable by adding the GLOBAL keyword to the SET command line. When users migrate from unprotected READUNCOMMITTED isolation level to more secure SERIALIZABLE level, the performance of RDBMS will also be affected. The reason is simple: the more data integrity the system requires, the more work it requires, and the slower the running speed. Therefore, it is necessary to coordinate the isolation requirements and performance of RDBMS. MySQL uses the repeatable read isolation level by default. This isolation level applies to most applications and must be changed only when the application has specific requirements for higher or lower isolation levels. There is no standard formula to determine which isolation level applies to applications-in most cases, this is a subjective decision, it is based on the fault tolerance capability of applications and the impact of application developers on potential data errors. Isolation-level selection is not standard for each application. For example, different transaction execution-based tasks of the same application require different isolation levels.
Author tianyazaiheruan