事务是一个并发控制的单位,是用户定义的一个操作序列,这些操作不可以分割,要么都成功,要么都是失败。
常用命令:
- 开启事务:
start transaction;
- 提交事务:
commit;
- 回滚:
rollback;
例子:开启事务,修改address表中id=4的description属性,比较是否提交事务两种情况有什么不同
①提交事务关闭窗口查询
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update address set description='UN' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
重新打开窗口查询数据:
mysql> select * from address;
+----+-------------+
| id | description |
+----+-------------+
| 1 | France |
| 2 | England |
| 3 | China |
| 4 | UN |
+----+-------------+
4 rows in set (0.00 sec)
②不提交事务关闭窗口查询
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update address set description='UN' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from address;
+----+-------------+
| id | description |
+----+-------------+
| 1 | France |
| 2 | England |
| 3 | China |
| 4 | NULL |
+----+-------------+
4 rows in set (0.00 sec)
结论:开启事务以后,如果没有提交事务就关闭窗口,则所做修改不进入数据库保存。
注意:MySQL数据库DML语句默认自动提交,Oracle数据库默认手动提交。