在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务, MyISAM 不支持
mysql> show engines; -- 表格显示
mysql> show engines \G -- 行显示
*************************** 1. row ***************************
Engine: InnoDB -- 引擎名称
Support: DEFAULT -- 默认引擎
Comment: Supports transactions, row-level locking, and foreign keys -- 描述
Transactions: YES -- 支持事务
XA: YES
Savepoints: YES -- 支持事务保存点
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY --内存引擎
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO -- MyISAM不支持事务
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
6.事务提交方式
事务的提交方式常见的有两种:
自动提交
手动提交
查看事务提交方式
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
用 SET 来改变 MySQL 的自动提交模式
mysql> SET AUTOCOMMIT=0; #SET AUTOCOMMIT=0 禁止自动提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
mysql> SET AUTOCOMMIT=1; #SET AUTOCOMMIT=1 开启自动提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+