Shulou(Shulou.com)06/01 Report--

This article mainly introduces the affairs in MySQL, the contents of the articles are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the affairs in MySQL.

An example:

Bank citation is a classic example of a transaction: if the bank has two tables, a cheque table and a savings table, and now needs to transfer RMB200 from the Jones user's checking account to a savings account, then at least three steps are required:

1. Check whether the checking account balance of Jones is greater than ¥200.

2. Jones's checking account-200RMB

3. Jones savings account + 200RMB

The above three steps can form a transaction, and when steps 2 or 3 fail, the operations previously performed will be automatically rolled back to ensure data consistency.

MySQL transactions are mainly used to deal with data with large amount of operations and high complexity. For example, in the personnel management system, if you delete a person, you need to delete not only the basic information of the person, but also the information related to that person, such as mailboxes, articles, etc., so that these database operation statements constitute a transaction!

In MySQL, only databases or tables that use the Innodb database engine support transactions.

Transactions can be used to maintain the integrity of the database, ensuring that batches of SQL statements are either executed or not executed.

Transactions are used to manage insert,update,delete statements

Generally speaking, a transaction must satisfy four conditions (ACID): atomicity (Atomicity, or indivisibility), consistency (Consistency), isolation (Isolation, also known as independence), and persistence (Durability).

Atomicity: all operations in a transaction are either completed or not completed, and do not end at some point in the middle. An error occurs during the execution of a transaction and is Rollback back to its state before the transaction starts, as if the transaction had never been executed.

Consistency: the integrity of the database is not compromised before the transaction starts and after the transaction ends. This means that the data written must fully comply with all the preset rules, including the accuracy and concatenation of the data, and that the subsequent database can spontaneously complete the scheduled work.

Isolation: the ability of a database to allow multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent data inconsistencies caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including read uncommitted (Read uncommitted), read commit (read committed), repeatable read (repeatable read) and serialization (Serializable).

Persistence: after the transaction is completed, the modification of the data is permanent, even if the system failure will not be lost.

Note: under the default settings of the MySQL command line, transactions are committed automatically, that is, the COMMIT operation is executed immediately after the SQL statement is executed. So to explicitly open a transaction, you must use the command BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0 to disable autocommit using the current session.

Transaction control statement:

BEGIN or START TRANSACTION; explicitly open a transaction

COMMIT; can also use COMMIT WORK, but the two are equivalent. COMMIT commits the transaction and makes all changes made to the database permanent

ROLLBACK; can use ROLLBACK WORK, but the two are equivalent. A rollback will end the user's transaction and undo any uncommitted changes in progress

SAVEPOINT identifier;SAVEPOINT allows you to create a SavePoint in a transaction, and there can be multiple SAVEPOINT in a transaction

RELEASE SAVEPOINT identifier; deletes a SavePoint for a transaction, and executing the statement throws an exception when there is no SavePoint specified

ROLLBACK TO identifier; rolls back the transaction to the marked point

SET TRANSACTION; is used to set the isolation level of the transaction. The isolation levels provided by the InnoDB storage engine for transactions are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

There are two main methods of MYSQL transaction processing:

1. Implement it with BEGIN, ROLLBACK and COMMIT.

START TRANSACTION # start a transaction

ROLLBACK # rollback the transaction

COMMIT # commit transaction

When there are too many SAVEPOINT identifier # transaction statements, you can create a SavePoint and restore to this point separately.

ROLLBACK [WORK] TO [SAVEPOINT] identifier # restore transaction to this point

RELEASE SAVEPOINT identifier # Delete SavePoint 2,

2. Directly use SET to change the automatic submission mode of MySQL:

SET AUTOCOMMIT=0 forbids automatic submission

SET AUTOCOMMIT=1 enables auto-submission

Isolation level:

1. READ UNCOMMITTED (read unsubmitted)

2. READ COMMITTED (read submit)

3. REPEATEABLE READ (repeatable)

4. SERIALIZABLE (serialization)

Problems that may arise from each isolation level:

a. Dirty read, the current thread transaction can read the uncommitted modified data of another thread transaction.

b. Can not be read repeatedly, the data seen before the transaction commit is inconsistent (other threads modify the commit); thread 1 starts the transaction query data to be the same, and when thread 2 starts the transaction modification commit, thread 1 queries again and finds that the data is modified (uncommitted). This is non-repeatable.

c. Phantom read, repeatable read level ensures that the data seen before transaction commit is consistent through MVCC mechanism, but there is a new problem. When thread 2 modifies the data to commit, thread 1 sees the original data before modification, but the actual underlying data has been changed. When thread 1 commits, it finds that the data has changed, like an illusion, this is illusion.

d. Lock read, SERIABLIZEBLE in order to solve the problem of phantom reading, when thread transaction 1 queries data, thread transaction 2 modifies data will be locked because the data is uncertain.

After reading the above about the affairs in MySQL, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.