MySQL事務詳解

1. 事務：一個事務是一個連續的一組資料庫操作，就好像它是一個單一的工作單元進行。換言之，永遠不會是完整的事務，除非該組内的每個單獨的操作是成功的。如果在事務的任何操作失敗，則整個事務将失敗。也是一組原子性的SQL查詢、或者是一個或多個SQL語句組成的獨立工作單元。

2. 事務日志：

MariaDB [test]> SHOW GLOBAL VARIABLES LIKE 'Innodb%log%';
+-------------------------------------------+---------+
| Variable_name                             | Value   |
+-------------------------------------------+---------+
| innodb_flush_log_at_trx_commit            | 1       |
| innodb_locks_unsafe_for_binlog            | OFF     |
| innodb_log_block_size                     | 512     |
| innodb_log_buffer_size                    | 8388608 |
| innodb_log_file_size                      | 5242880 |
| innodb_log_files_in_group                 | 2       |
| innodb_log_group_home_dir                 | ./      |
| innodb_mirrored_log_groups                | 1       |
| innodb_recovery_update_relay_log          | OFF     |
| innodb_use_global_flush_log_at_trx_commit | ON      |
+-------------------------------------------+---------+

3. ACID測試：

A：AUTOMICITY，原子性；整個事務中的所有操作要麼全部成功執行，要麼全部失敗後復原；

C：CONSISTENCY，一緻性；資料庫總是應該從一個一緻性狀态轉為另一個一緻性狀态；

I：ISOLATION，隔離性；一個事務所做出的操作在送出之前，是否能為其它事務可見；出于保證并發操作之目的，隔離有多種級别；

D：DURABILITY，持久性；事務一旦送出，其所做出的修改會永久儲存。

4. 自動送出：單語句事務

MariaDB [test]> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
MariaDB [test]> SET @@session.autocommit=0;
MariaDB [test]> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

5. 手動控制事務：

啟動：START TRANSACTION;

送出：COMMIT;

復原：ROLLBACK;

事務支援savepoints：

SAVEPOINT identifier;

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier

示例：

MariaDB [test]> CREATE TABLE tbl1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name CHAR(30) NOT NULL);
MariaDB [test]> SHOW TABLE STATUS LIKE 'tbl1';
MariaDB [test]> START TRANSACTION;
MariaDB [test]> INSERT INTO tbl1 (name) VALUES ('tom'),('jerry');
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
+----+-------+
MariaDB [test]> ROLLBACK;
MariaDB [test]> SELECT * FROM tbl1;
Empty set (0.00 sec)
MariaDB [test]> START TRANSACTION;
MariaDB [test]> INSERT INTO tbl1 (name) VALUES ('tom'),('jerry');
MariaDB [test]> SAVEPOINT firsttag;
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
+----+-------+
MariaDB [test]> DELETE  FROM tbl1 WHERE id=4;
MariaDB [test]> SAVEPOINT secondtag;
MariaDB [test]> INSERT INTO tbl1 (name) VALUES ('obama');
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  3 | obama |
+----+-------+
MariaDB [test]> ROLLBACK TO secondtag;
MariaDB [test]> SELECT * FROM tbl1;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
MariaDB [test]> ROLLBACK TO firsttag;
MariaDB [test]> COMMIT;

6. 事務隔離級别：

READ-UNCOMMITTED：讀未送出 --> 髒讀;

READ-COMMITTED：讀送出 --> 不可重複讀;

REPEATABLE-READ：可重複讀 --> 幻讀;

SERIALIZABLE：串行化;

示例(兩個連接配接節點)：

MariaDB [test]> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host      | db   | Command | Time | State | Info             | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
|  1 | root | localhost | test | Query   |    0 | NULL  | SHOW PROCESSLIST |    0.000 |
|  2 | root | localhost | test | Sleep   |  393 |       | NULL             |    0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
MariaDB [test]> SELECT @@session.tx_isolation;                                       #預設
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
MariaDB [test]> SET @@session.autocommit=0;                                          #關閉自動送出

讀未送出 --> 髒讀：

節點1：
MariaDB [test]> SET @@session.tx_isolation='READ-UNCOMMITTED';
節點2：
MariaDB [test]> SET @@session.tx_isolation='READ-UNCOMMITTED';
節點1：
MariaDB [test]> START TRANSACTION;
節點2：
MariaDB [test]> START TRANSACTION;
節點1：
MariaDB [test]> INSERT INTO tbl1 (name) VALUES ('trump');
節點2：
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
|  4 | trump |
+----+-------+
節點1：
MariaDB [test]> ROLLBACK;
節點2：
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
+----+-------+
MariaDB [test]> COMMIT;

讀送出 --> 不可重複讀:

節點1：
MariaDB [test]> SET @@session.tx_isolation='READ-COMMITTED';
節點2：
MariaDB [test]> SET @@session.tx_isolation='READ-COMMITTED';
節點1：
MariaDB [test]> START TRANSACTION;
節點2：
MariaDB [test]> START TRANSACTION;
節點1：
MariaDB [test]> INSERT INTO tbl1 (name) VALUES ('jack;');
節點2：
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
+----+-------+
節點1：
MariaDB [test]> COMMIT;
節點2：
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
|  5 | jack; |
+----+-------+
MariaDB [test]> COMMIT;

可重複讀 --> 幻讀:

節點1：
MariaDB [test]> SET @@session.tx_isolation='REPEATABLE-READ';
節點2：
MariaDB [test]> SET @@session.tx_isolation='REPEATABLE-READ';
節點1：
MariaDB [test]> START TRANSACTION;
節點2：
MariaDB [test]> START TRANSACTION;
節點1:
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
|  5 | jack; |
+----+-------+
MariaDB [test]> DELETE FROM tbl1 WHERE name='tom';
節點2：
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
|  5 | jack; |
+----+-------+
節點1：
MariaDB [test]> COMMIT;
節點2：
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
|  5 | jack; |
+----+-------+
MariaDB [test]> ROLLBACK;
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  2 | jerry |
|  5 | jack; |
+----+-------+

串行化：

節點1：
MariaDB [test]> SET @@session.tx_isolation='SERIALIZABLE';
節點2：
MariaDB [test]> SET @@session.tx_isolation='SERIALIZABLE';
節點1：
MariaDB [test]> START TRANSACTION;
節點2：
MariaDB [test]> START TRANSACTION;
節點1：
MariaDB [test]> INSERT INTO tbl1 (name) VALUES ('tom');
節點2：
MariaDB [test]> SELECT * FROM tbl1;(被阻塞)
節點1：
MariaDB [test]> COMMIT;
節點2：
MariaDB [test]> SELECT * FROM tbl1;
+----+-------+
| id | name  |
+----+-------+
|  2 | jerry |
|  5 | jack; |
|  6 | tom   |
+----+-------+

MySQL事務詳解

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案