Shulou(Shulou.com)05/31 Report--

This article is to share with you about what transactions are in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. In a MySQL environment, a transaction consists of one or more SQL statements as a single unit. Each SQL statement in this unit is interdependent, and the unit as a whole is inseparable. If a statement in the unit cannot be completed, the entire unit is rolled back (undone) and all affected data is returned to the state before the start of the transaction. Therefore, only if all statements in the transaction are executed successfully can it be said that the transaction was executed successfully.

two。 Not all storage engines support transactions, such as InnoDB and BDB, but MyISAM and MEMORY do not.

Through the InnoDB and BDB table types, the MySQL transaction system can fully meet the transaction security ACID testing. Older table types, such as the MyISAM type, do not support transactions. Therefore, transactions in such a system can only be implemented through direct table locking.

The term "ACID" is an abbreviation, and the processing of each transaction must meet the ACID principle, that is, atomicity: a (atom), consistency: C (consistent), isolation: I (independence), persistence: d (durability).

(1) atomicity

Atomicity means that each transaction must be considered an indivisible unit. Suppose a transaction consists of two or more tasks, and the statements in it must be successful at the same time to be considered successful. If the transaction fails, the system returns to the previous state of the transaction.

In the example of adding employees, atomicity means that it is not possible to add employees to the employee database if the corresponding payroll and department records are not created. Www.2cto.com

The execution of atoms is a proposition that either all or nothing happens. In an atomic operation, if any statement in the transaction fails, the previously executed statement will be returned to ensure that the integrity of the data is not affected. This is particularly important in some critical systems, where real-world applications (such as financial systems: money transfers) perform data entry or updates and must ensure that there is no data loss or data errors to ensure data security.

(2) consistency

Whether the transaction completes successfully or fails halfway, there is consistency when the transaction puts the system in a consistent state. With reference to the previous example, consistency means that if an employee is removed from the system, all data related to that employee, including salary data and group membership, will also be deleted.

In MySQL, consistency is mainly handled by MySQL's logging mechanism, which records all changes in the database and provides tracking records for transaction recovery. If an error occurs in the middle of a transaction, the MySQL recovery process uses these logs to find out whether the transaction has been fully successful and whether it needs to be returned. Therefore, the consistency property ensures that the database never returns an unfinished transaction.

(3) isolation

Isolation means that each transaction occurs in its own space, isolated from other transactions that occur in the system, and the results of the transaction can only be seen when it is fully executed. Even if multiple transactions occur at the same time in such a system, the isolation principle ensures that the results of a particular transaction are invisible until it is fully 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, it may lead to the destruction of a large amount of data, such as the integrity of the respective space of each transaction is quickly violated by other conflict firms.

The only way to achieve absolute isolation is to ensure that only one user can access the database at any one time. This is not a practical solution when dealing with a multi-user RDBMS like MySQL. However, most transaction systems use page-level locking or row-level locking to isolate changes between different transactions at the expense of performance. For example, MySQL's BDB table handler uses page-level locking to ensure the security of handling multiple simultaneous transactions, while InnoDB table processor uses better row-level locking.

(4) persistence

Persistence means that even if the system crashes, a committed transaction still exists. Persistence begins to take effect when a transaction is completed and the database log has been updated. Most RDBMS products keep the data persistent by keeping a log of all the behaviors that change the data in any way in the database. The database log records all table updates, queries, reports, and so on.

If the system crashes or the data storage media is corrupted, by using logs, the system can recover the last successful update before reboot, reflecting changes in transactions that were in the process at the time of the crash.

MySQL achieves persistence by saving a binary transaction log file that records changes in the system during the transaction. If you encounter hardware damage or a sudden system shutdown, you can easily recover the lost data by using the final backup and log when the system is rebooted.

By default, the InnDB table is 100% persistent (all transactions performed by the system before the crash can be reliably recovered during the recovery process). The MyISAM table provides partial persistence, and all changes made before the last FLUSH TABLES command are guaranteed to be saved.

Www.2cto.com

3. A transaction is made up of a set of SQL statements that are entered by a user and terminated by modification to a persistent or scrolled to its original state.

In MySQL, when a session starts, the system variable AUTOCOMIT value is 1, that is, the autocommit function is turned on. When the user executes a SQL statement, the changes to the database are immediately committed as persistent changes saved to disk, and a transaction ends. Therefore, the user must turn off autocommit before the transaction can be composed of multiple SQL statements. After SET@@AUTOCOMMIT=0; executes this statement, it must explicitly indicate the termination of each transaction, and the changes made to the database by the SQL statements in the transaction can become persistent changes. For example, execute the following statement:

DELETE FROM XS WHERE student number = '081101'

SELECT * FROM XS

From the execution result, it is found that a row has been deleted from the table. However, this change is not persisted because autocommit has been turned off. The user can undo the change through ROLLBACK or persist the change using the commit statement.

4. Details how to handle a transaction.

1)。 Start a transaction

When the first SQL statement of an application or the first SQL after the COMMIT or ROLLBACK statement (described later) is executed, a new transaction begins. You can also use a start transaction statement to start a transaction explicitly. Syntax format: START TRANSACTION | BEGINWORK

A BEGIN WORK statement can be used instead of a STARTTRANSACTION statement, but STARTTRANSACTION is more common.

2)。 End transaction

The COMMIT statement is a commit statement that makes all data modifications performed since the start of the transaction a permanent part of the database and marks the end of a transaction. The syntax format is: COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

Note: the optional and chain clause starts a new transaction immediately at the end of the current transaction, and the new transaction has the same level of isolation as the transaction that just ended. The Release clause disconnects the server from the current client after it terminates the current transaction. Include NO keywords to suppress CHAIN or RELEASE completion.

Note: MySQL uses a flat transaction model, so nested transactions are not allowed. After using the START TRANSACTION command in the first transaction, the first transaction is automatically committed when the second transaction starts. Similarly, the following MySQL statements execute a COMMIT command implicitly at run time:

● DROP DATABASE / DROP TABLE

● CREATE INDEX / DROP INDEX

● ALTER TABLE / RENAME TABLE

● LOCK TABLES / UNLOCK TABLES

● SET AUTOCOMMIT=1

3)。 Undo a transaction

The Rollback statement is an undo statement that undoes changes 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 executing this statement, the previous delete action will be undone, and you can use the select statement to see if the row of data has been restored.

4)。 Roll back the transaction

Www.2cto.com

In addition to undoing the entire transaction, the user can also use the ROLLBACK TO statement to roll back the transaction to a certain point, before using the save point statement to set a SavePoint.

The SAVEPOINT syntax format is: SAVEPOINTidentifier (name of SavePoint).

The ROLLBACK TO SAVEPOINT statement rolls back a transaction to a named SavePoint. If the current transaction makes changes to the data after the SavePoint is set, those changes are undone in the rollback. The syntax format is:

ROLLBACK [WORK] TO SAVEPOINT identifier

When the transaction rolls back to a SavePoint, the SavePoint set after that SavePoint is deleted. The RELEASESAVEPOINT statement removes named Savepoints from a set of Savepoints in the current transaction. No commit or rollback occurs. If the save point does not exist, an error occurs. The syntax format is: RELEASE SAVEPOINT identifier

5. Example: the following statements illustrate the handling of a transaction:

1. START TRANSACTION

2. UPDATE...

3. DELETE...

4. SAVEPOINT S1

5. DELETE...

6. ROLLBACK WORK TO SAVEPOINT S1

7. INSERT...

8. COMMIT WORK

Note: in the above statement, line 1 starts a transaction; lines 2 and 3 modify the data but do not commit; line 4 sets a SavePoint; line 5 deletes the data but does not commit; line 6 rolls back the transaction to SavePoint S1, when the changes made in line 5 are undone; line 7 modifies the data Line 8 ends the transaction, and the changes made to the database by lines 2, 3, and 7 are persisted.

6. Each transaction has a so-called isolation level, which defines the degree to which users are isolated and interact with each other. As mentioned earlier, one of the most important properties of transactional RDBMS is that it can "isolate" different sessions that are being processed on the server. In a single-user environment, this attribute does not matter: only one session is active at any one time. But in a multi-user environment, many RDBMS sessions are active at any given moment. In this case, it is important that RDBMS be able to isolate transactions so that they do not affect each other and ensure that database performance is not affected.

Www.2cto.com

In order to understand the importance of isolation, it is necessary to take some time to think about what will happen if isolation is not imposed. If there is no transaction isolation, different SELECT statements will retrieve different results in the context of the same transaction, because during this period, the basic data has been modified by other transactions. This leads to inconsistency, and it is difficult to trust the result set, so the query results cannot be used as a basis for calculation. Thus isolation forces some degree of isolation of the transaction to ensure that the application sees consistent data in the transaction.

Based on the ANSI/ISO SQL specification, MySQL provides the following four isolation levels: serialization (serialization), repeatable read (repeatable read), committed read (read committed), and uncommitted read (read uncommitted).

7. Only a storage engine that supports transactions can define an isolation level. The SET TRANSACTION statement can be used to define the isolation level. Syntax format:

SET [GLOBAL | SESSION] TRANSACTION Isolationlevel

SERIALIZABLE

| | REPEATABLE READ |

| | READ COMMITTED |

| | READ UNCOMMITTED |

Description:

If you specify GLOBAL, the defined isolation level applies to all SQL users; if you specify SESSION, the isolation level applies only to currently running sessions and connections. MySQL defaults to the REPEATABLE READ isolation level.

(1) Serialization

If the isolation level is serialization, users provide maximum isolation between transactions by executing the current transaction one after another.

(2) repeatable

At this level, transactions are not seen as a sequence. However, the current changes in executing transactions are still not visible, that is, if a user executes the same SELECT statement several times in the same transaction, the result is always the same.

(3) submit for reading

The security of READ COMMITTED isolation level is worse than that of REPEATABLE READ isolation level. Not only transactions at this level can see new records added by other transactions, but also changes made by other transactions to existing records can also be seen once committed. That is, this means that during a transaction, if another transaction modifies the corresponding table, multiple SELECT statements of the same transaction may return different results.

(4) not submitted for reading

Www.2cto.com

Provides minimal isolation between transactions. In addition to illusory read operations and unrepeatable read operations, transactions at this isolation level can read data that other transactions have not yet committed, if this transaction uses changes that other transactions do not commit as the basis for calculation, then those uncommitted changes are undone by their parent transaction, which leads to a large number of data changes.

By default, the value of this system variable is set on a per-session basis, but the value of the global system variable can be modified by adding the GLOBAL keyword to the SET command line.

RDBMS performance is also affected when users move from the unprotected READUNCOMMITTED isolation level to the more secure SERIALIZABLE level. The reason is simple: the more data integrity the system is required to provide, the more work it needs to do and the slower it will run. Therefore, there is a need to coordinate between the isolation requirements and performance of RDBMS.

MySQL defaults to the REPEATABLE READ isolation level, which is suitable for most applications and needs to be changed only if the application has specific requirements for a higher or lower isolation level. There is no standard formula to determine which isolation level applies to the application-in most cases, this is a subjective decision based on the application's fault tolerance and the application's judgment on the impact of potential data errors. There is no standard for the choice of isolation level for each application. For example, different transactions of the same application require different isolation levels based on the tasks performed.

Thank you for reading! This is the end of this article on "what is business in MySQL?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.