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
, 0rows
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. Commandsend
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
thecurrent
input
statement. mysql>SELECT
*FROM
mydb.Test; Emptyset
(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教程的這一部分中，我們處理了事務.
