MySQL中的事务(第十七课)

Transactions in MySQL

MySQL中的事务

In this part of the MySQL tutorial, we will mention transactions.

在MySQL教程的这一部分中，我们来说说事务。

Definition of a transaction

事务的定义

A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.

事务是针对一个或多个数据库中数据的数据库操作的基本单位。事务中所有SQL语句的影响可以全部提交给数据库，也可以全部回滚。

MySQL supports several storage engines. The InnoDB is fully ACID compliant. The ACID stands for Atomicity, Consistency, Isolation and Durability. Reliable transactions must support all these four properties.

MySQL支持多种存储引擎。 InnoDB完全符合ACID。 ACID代表原子性，一致性，隔离性和耐久性。可靠的交易必须支持所有这四个属性。

Operations within a transaction must be atomic. This means that either all operations succeed or fail. This is all or nothing rule. The consistency property ensures that the database is in a consistent state after the transaction is finished. The data is valid and there are no half-finished records. For example there are no customers left with no payment records or there are no payment records without customers. Isolation is the requirement that other operations cannot access data that has been modified during a transaction that has not yet completed. The question of isolation occurs in case of concurrent transactions. Without isolation, the data may end up in inconsistent state. Durability is the ability of the database system to recover the committed transaction updates against any kind of system failure.

事务内的操作必须是原子的。这意味着所有操作都将成功或失败。这是全有或全无规则。一致性属性可确保事务完成后数据库处于一致状态。数据有效，没有半完成的记录。例如，没有客户没有付款记录，或者没有客户没有付款记录。隔离是其他操作不能访问在尚未完成的事务期间已修改的数据的要求。在并发事务的情况下会出现隔离问题。没有隔离，数据可能会以不一致的状态结束。耐久性是数据库系统针对任何类型的系统故障恢复已提交事务更新的能力

Isolation levels

隔离度

In a highly concurrent environment, highly isolated transactions may lead to deadlocks. A deadlock is a situation, where transactions compete over resources and effectively prevent each other from accessing the resource. Furthermore there is a tradeoff between isolation level and performance of the database. Therefore, database systems offer several levels of isolation for transactions.

在高度并发的环境中，高度隔离的事务可能导致死锁。 死锁是一种情况，其中事务争用资源并有效阻止彼此访问资源。 此外，在隔离级别和数据库性能之间需要权衡。 因此，数据库系统为事务提供了几个隔离级别。

MySQL offers four levels of transaction isolation:

MySQL提供了四个级别的事务隔离：

• Serializable//可序列化
• Repeatable read//可重复读
• Read committed//阅读已提交
• Read uncommitted//阅读未提交

In the serializable isolation level all transactions occur in a completely isolated fashion. All transactions are executed one after the other. In a repeatable read isolation level statements cannot read data that has been modified but not yet committed by other transactions. No other transactions can modify data that has been read by the current transaction until the current transaction completes. It is the default isolation level for InnoDB. In a read committed isolation level statements cannot read data that has been modified but not committed by other transactions. Statements wait until rows of data that are write-locked by other transactions are unlocked before they acquire their own locks. This prevents them from reading dirty data. In a read uncommitted isolation level, statements can read rows that have been modified by other transactions but not yet committed.

在可序列化隔离级别中，所有事务都以完全隔离的方式发生。 所有事务都一个接一个地执行。 在可重复读取隔离级别中，语句无法读取已被其他事务修改但尚未提交的数据。 在当前事务完成之前，没有其他事务可以修改当前事务已读取的数据。 这是InnoDB的默认隔离级别。 在读取的提交隔离级别中，语句无法读取已被其他事务修改但未提交的数据。 语句等待直到被其他事务写锁定的数据行被解锁，然后才能获取自己的锁。 这样可以防止他们读取脏数据。 在读取未提交隔离级别中，语句可以读取已被其他事务修改但尚未提交的行。

Phantom reads, non-repeatable reads and dirty reads are issues, which can be encountered, when the transactions are not completely separated. A phantom read occurs when a transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction. A non-repeatable read occurs when a transaction re-reads data it has previously read and finds that data has been modified by another transaction. That committed since the initial read. A dirty read occurs when a transaction reads data from a row that has been modified by another transaction, but not yet committed.

当事务没有完全分开时，可能会遇到幻影读取，不可重复读取和脏读取的问题。 当事务重新执行返回返回满足搜索条件的行的集合的查询，并发现满足条件的行的集合由于另一个最近提交的事务而发生了更改时，将发生幻像读取。 当事务重新读取它先前读取的数据并发现该数据已被另一个事务修改时，就会发生不可重复的读取。 自从初次阅读以来就实现了。 当事务从已被另一事务修改但尚未提交的行中读取数据时，发生脏读取。

The following table shows all isolation levels and possible issues encountered with them.

下表显示了所有隔离级别以及它们遇到的可能的问题。

Isolation levelPhantom readNonrepeatable readDirty readSerializableNot possibleNot possibleNot possibleRepeatable readPossibleNot possibleNot possibleRead committedPossiblePossibleNot possibleRead uncommittedPossiblePossiblePossible.

隔离级别幻像读取不可重复读取脏读可序列化不可能不可能可能重复读取可能不可能不可能读已提交可能可能不可能读未提交可能可能.

The default transaction isolation level for MySQL is repeatable read.

MySQL的默认事务隔离级别是可重复读取.

<code>mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
/<code>

The current isolation level is stored in the tx_isolation server variable.

当前隔离级别存储在tx_isolation服务器变量中。

<code>mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

mysql> SELECT @@tx_isolation;
+----------------+ 

| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
/<code>

We can change the isolation level with the SET TRANSACTION ISOLATION LEVEL statement.

我们可以使用SET TRANSACTION ISOLATION LEVEL语句更改隔离级别。

Autocommit

自动提交

MySQL also automatically commits statements that are not part of a transaction. The results of any UPDATE or INSERT statement not preceded with a START will immediately be visible to all connections.

MySQL还自动提交不属于事务一部分的语句。 任何未带有START的UPDATE或INSERT语句的结果的连接都是可见的.

<code>mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
/<code>

The autocommit variable is set by default.

默认情况下设置自动提交变量。

<code>mysql> SET autocommit=0;

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
 
+--------------+
/<code>

The autocommit can be turned off.

Now we are going to demonstrate the autocommint variable.

自动提交可以关闭。

现在，我们将演示autocommit变量。

<code>mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

CREATE TABLE Test(Num INTEGER NOT NULL) engine=InnoDB;
/<code>

The autocommit is set. We create a simple Test table with InnoDB storage engine, which supports transactions.

自动提交已设置。 我们使用InnoDB存储引擎创建一个简单的Test表，该表支持事务。

<code>mysql> INSERT INTO Test VALUES (
1
), (
2
), (
3
);

mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
|   1 |
|   2 |
|   3 |
+-----+
/<code>

We insert three rows into the column of the table. The values are immediately committed.

我们将三行插入到表的列中。 值将立即被提交。

<code>mysql> SET autocommit=
0
;

mysql> INSERT INTO Test VALUES (
4
), (
5
);

mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+
/<code>

Now we set the autocommit variable to false. We insert two values and select all data from the table. We have now 5 rows in the table.

现在，我们将autocommit变量设置为false。 我们插入两个值，然后从表中选择所有数据。 现在表中有5行。

<code>mysql> ROLLBACK;

mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
|   1 |
|   2 |
|   3 |
+-----+
/<code>

However, the data is not permanently written to the table. With the ROLLBACK statement, we take them back.

但是，数据不会永久写入表中。 使用ROLLBACK语句，我们将其收回.

<code>mysql> INSERT INTO Test VALUES (
4
), (
5
);

mysql> COMMIT;

mysql> ROLLBACK;

mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+
/<code>

Now we insert values 4, 5 again. This time, the rows are committed with the COMMIT statement. Subsequent rollback statement has no effect.

现在，我们再次插入值4、5。 这次，使用COMMIT语句提交行。 后续回滚语句无效。

Starting transactions

开始事务

With autocommit enabled, each single SQL statement is wrapped automatically in its own transaction. To start our own transaction, we issue the START TRANSACTION statement. The transaction is later finished with the COMMIT or ROLLBACK statements. Multiple statements may be issued in the body of the transaction. All are either committed or rolled back as one unit.

启用自动提交功能后，每个单个SQL语句都会自动包装在其自己的事务中。 要开始我们自己的事务，我们发出START TRANSACTION语句。 事务稍后使用COMMIT或ROLLBACK语句完成。 事务主体中可能会发布多个语句。 全部作为一个单元提交或回滚。

<code>
mysql
> 
TRUNCATE
 
Test
;
Query
 
OK
, 0 
rows
 
affected
 (0
.02
 
sec
)

mysql
> 
SELECT
 * 
FROM
 
Test
;
Empty
 
set
 (0
.00
 
sec
)/<code>

We will work with the same Test table. We truncate the data in the table.

我们将使用相同的测试Test表。 我们删除表中的数据。

<code>mysql> START TRANSACTION;

mysql> INSERT INTO Test VALUES (
1
), (
2 
);

mysql> INSERT INTO Test VALUES (
3
), (
4
);

mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
+-----+
/<code>

In the above code, we start a transaction and insert four rows into the table. The values are not yet committed. From the current connection the rows are visible.

在上面的代码中，我们开始一个事务并将四行插入到表中。 这些值尚未提交。 从当前连接中，行可见。

<code>$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands 
end
 
with
 ; or \g.
Your MySQL connection id is 65
Server version: 5.1.41-3ubuntu12.9 (Ubuntu)

Type '
help
;' or '\h' for help. 
Type
 
'\c'
 
to
 
clear
 the 
current
 
input
 statement.

mysql> 
SELECT
 * 
FROM
 mydb.Test;
Empty 
set 
 (
0.00
 sec)
/<code>

However, from a different connection, the Test table is empty. We launch a new instance of a mysql client program. This is a different connection to the MySQL database. From this connection, the values are not yet visible.

但是，从另一个连接，测试Test表为空。 我们启动一个mysql客户端程序的新实例。 这是与MySQL数据库的不同连接。 从此连接，这些值尚不可见。

<code>
 COMMIT;
/<code>

Finally, the COMMIT statement commits the data to the table. The rows are visible from both connections.

We start another transaction. This time the data will be rolled back.

最后，COMMIT语句将数据提交到表。 这些行在两个连接中均可见。

我们开始另一笔事务。 这次将回滚数据。

<code>mysql> START TRANSACTION;

mysql> INSERT INTO Test VALUES (
5
), (
6
);

mysql> INSERT INTO Test VALUES (
7
), (
8
);

mysql> ROLLBACK;

mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
|   1 |
|   2 | 

|   3 |
|   4 |
+-----+
/<code>

In the above SQL code, we start a new transaction. We insert four values into the Test table. We roll the changes back with the ROLLBACK statement. Subsequent select from the table shows that the data was not committed to the table.

In this part of the MySQL tutorial, we have worked with transactions

在上面的SQL代码中，我们开始一个新事务。 我们在测试Test表中插入四个值。 我们使用ROLLBACK语句回滚更改。 从表中进行的后续选择显示,数据未提交到表当中去。

在MySQL教程的这一部分中，我们处理了事务.

關鍵字: +-----+ 第十七 data