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 |
+----+-------+
