Are there transactions in mysql?

There are transactions in mysql, but only databases or tables using the Innodb database engine support transactions. Transaction processing can be used to maintain the integrity of the database. It submits or revokes operation requests to the system together with all commands as a whole, that is, this set of database commands is either executed or not executed.

(Recommended tutorial: mysql video tutorial)

Database transaction (Transaction) is a mechanism, a An operation sequence contains a set of database operation commands. A transaction submits or revokes an operation request to the system together with all commands as a whole, that is, this set of database commands are either executed or not executed, so the transaction is an indivisible logical unit of work.

When performing concurrent operations on a database system, transactions are used as the smallest control unit, which is especially suitable for database systems operated by multiple users at the same time. For example, airline booking systems, banks, insurance companies, and securities trading systems.

• In MySQL, only databases or tables using the Innodb database engine support transactions.

• Transaction processing can be used to maintain the integrity of the database and ensure that batches of SQL statements are either all executed or not executed at all.

• Transactions are used to manage insert, update, delete statements

Transactions have 4 characteristics, namely atomicity, Consistency, Isolation and Durability, these four characteristics are often referred to as ACID.

1. Atomicity

A transaction is a complete operation. The elements of a transaction are indivisible (atomic). All elements in the transaction must be committed or rolled back as a whole. If any element in the transaction fails, the entire transaction fails.

Take the bank transfer transaction as an example. If the transaction is submitted, the data of the two accounts will be updated. If for some reason the transaction terminates before successfully updating both accounts, the balances of both accounts will not be updated, modifications to any account balances will be undone, and the transaction cannot be partially committed.

2. Consistency

When the transaction is completed, the data must be in a consistent state. That is, the data stored in the database is in a consistent state before the transaction begins. During an ongoing transaction, the data may be in an inconsistent state, for example, the data may be partially modified. However, when the transaction completes successfully, the data must be returned to a known consistent state again. Modifications made to data through transactions cannot damage the data, or transactions cannot leave the data storage in an unstable state.

Take bank transfer transactions as an example. Before the transaction begins, the total of all account balances is in a consistent state. During the course of the transaction, the balance of one account is reduced, while the balance of the other account has not been modified. Therefore, the total of all account balances is inconsistent. After the transaction is completed, the total account balance is restored to a consistent state again.

3. Isolation

All concurrent transactions that modify data are isolated from each other, which indicates that the transaction must be independent and it should not be dependent in any way In or affect other affairs. A transaction that modifies data can access the data before another transaction that uses the same data begins, or after another transaction that uses the same data ends.

In addition, when a transaction modifies data, if any other process is using the same data at the same time, the modifications to the data cannot take effect until the transaction is successfully committed. The transfer between Zhang San and Li Si and the transfer between Wang Wu and Zhao Er are always independent of each other.

4. Durability

The durability of a transaction means that regardless of whether the system fails, the results of the transaction are permanent.

After a transaction completes successfully, the changes it makes to the database are permanent, even if the system fails. That is to say, once the transaction is committed, any changes made to the data by the transaction will be permanently retained in the database.

The ACID principle of transactions ensures that a transaction is either successfully committed or failed and rolled back, one of the two. Therefore, its modifications to the transaction are recoverable. That is, when a transaction fails, its data modifications will be restored to the state before the transaction was executed.

The above is the detailed content of Are there transactions in mysql?. For more information, please follow other related articles on the PHP Chinese website!