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

Editor to share with you what the meaning of affairs in MySQL, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

I. the concept of transaction

A transaction consists of one or more SQL statements in a single unit, in which each MySQL statement is interdependent. The whole unit as an indivisible whole, if a SQL statement in the unit fails or produces an error, the whole unit will be rolled back. All affected data will return to the state before the start of the thing; if all the SQL statements in the unit are executed successfully, the thing will be executed smoothly.

Storage engines in MySQL and storage engines that support transactions and do not support transactions

1. The concept of storage engine: data in mysql is stored in files (or memory) using a variety of different technologies.

Each of these technologies uses different storage mechanisms, indexing techniques, and ultimately provides a wide range of functions and capabilities. By selecting different technologies, additional speed or functionality can be obtained to improve the overall functionality of the application.

2. These different technologies and related functions are called storage engines (also known as table types) in mysql.

3. Check the storage engines supported by mysql through show engines;.

4. The most frequently used storage engines in mysql are: innodb,bdb,myisam, memory and so on. Among them, innodb and bdb support transactions, while myisam and others do not.

Third, the four attributes of the transaction:

1. Atomicity: a transaction is made up of one or a set of interrelated SQL statements, which are considered to be an indivisible unit.

2. Consistency: the changes to the database are consistent, that is, the data checked by multiple users is the same. Consistency is mainly handled by mysql's logging mechanism, which records changes in data and provides tracking records for transaction recovery.

3. Isolation (isolation): each transaction has its own space, isolated from other transactions that occur in the system, and the result of the transaction can only be seen when it is fully executed.

4. Persistence: but the changes and updates to the data after the transaction is committed are permanent. When a transaction is completed and the log of the database has been updated, persistence can play its unique role. In mysql, if the system crashes or the data storage medium is destroyed, through the log, the system can recover the last successful update before restart, which can reflect the changes of things in the execution process when the system crashes.

IV. The creation and life cycle of transactions

For a storage engine that supports transactions, the cycle of a transaction:

1. In the process of creating a transaction, you need to create a data table of innodb or bdb type. The basic command structure is as follows:

Create table table_name (file defintions) type=innodb/bdb

2. Modify the table type

Alert table table-name type = innodb/bdb

3. The whole process of the transaction

Use databases; / / use a database

Start transaction; / / start the transaction. You can also use begin and beginwork here.

Insert into stu1 values ('',); / / perform related operations

Commit / / submit things

Rollback / / undo transaction (transaction rollback)

Five: mysql behavior

1. After we use mysql, if we turn it off and open it in the query, we will find which data has been saved, but we know that there is no display or implicit submission in the process. Because we set up autocommit in mysql, we can also commit manually.

2. Set autocommit = 0; / / disable automatic submission

3. Set autocommit = 1; / / enable automatic submission

6. Isolation of transactions (isolation)

1. It is important to use isolation levels when using multiple users to ensure that these transactions do not affect each other and that database performance is not affected.

2. There are four types of isolation levels available in mysql:

The SQL standard defines four types of isolation levels, including specific rules that define which changes inside and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrent processing and have lower system overhead.

Read Uncommitted (read uncommitted)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because its performance is not much better than other levels. Reading uncommitted data is also known as Dirty Read.

Read Committed (read submission)

This is the default isolation level for most database systems (but not the default for MySQL). It satisfies the simple definition of isolation: a transaction can only see changes that have been committed to the transaction. This isolation level also supports so-called non-repeatable reads (Nonrepeatable Read), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

Repeatable Read (reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. But in theory, this leads to another thorny problem: Phantom Read. To put it simply, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, they will find a new "phantom" row. InnoDB and Falcon storage engines solve this problem through multi-version concurrency control (MVCC,Multiversion Concurrency Control) mechanisms.

Serializable (serializable)

This is the highest isolation level, and it solves the problem of phantom reading by forcing the ordering of transactions so that they cannot conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a lot of timeouts and lock competition.

These four isolation levels are implemented with different lock types, which are prone to problems if the same data is read. For example:

Dirty reading (Drity Read): one transaction has updated a piece of data, and another transaction has read the same data at this time. For some reason, if the previous RollBack operates, the data read by the latter transaction will be incorrect.

Non-repeatable read (Non-repeatable read): data inconsistencies between two queries of a transaction, which may be due to the insertion of original data updated by a transaction between the two queries.

Phantom Read: the number of data pens is inconsistent in two queries of a transaction, for example, one transaction queries several columns of data (Row), while another transaction inserts new columns of data at this time, the previous transaction will find several columns of data that it did not have before in the next query.

In MySQL, these four isolation levels are implemented, each of which may cause problems as follows:

3. View and modify the transaction orphan level:

View: select @ @ tx_isolation

Modifying: orphaned level of set global transaction isolation level settin

7. Pseudo transaction (locked)

1. According to different requirements, many storage engines are provided in MySQL, but some storage engines do not support transactions. In this case, table locking can be used instead of transactions.

2, for the storage engine MYISAM type data tables that do not support transactions, when users insert, modify, delete, these operations will be immediately saved to disk, when multiple users operate a table at the same time, you can use table locking to avoid multiple users operating on the specified table in the database at the same time, so as to avoid interference in the process of user operation of the data table. Other users can access these modified data tables only after the operation that the user releases the table is locked.

The lock here is the same as the lock in Java multithreading, which can be understood in this way.

3. The process of locking the specified table:

(1) lock table table-name locktype; / / there are read and write in locktype.

Lock multiple tables:

Lock table table-name1 lock type, table- name2 lock type, table- name3 lock type

(2) perform the corresponding operation in the specified table

(3) when the user completes the operation of locking the data table, unlock it.

Unlock tables / / released all locks on the locked table.

The above is all the content of this article "what is the meaning of transaction in MySQL?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to 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.