mysql 内置功能事务介绍

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性

创建数据库db12

create database db12 charset=utf8;

use db12;

create table user(

id int primary key auto_increment,

name char(32),

balance int

);

insert into user(name,balance)

values

('wsb',1000),

('egon',1000),

('ysb',1000);

mysql> select * from user;

+----+------+---------+

| id | name | balance |

+----+------+---------+

|  4 | wsb  |    1000 |

|  5 | egon |    1000 |

|  6 | ysb  |    1000 |

+----+------+---------+

3 rows in set (0.00 sec)

#原子操作

start transaction; 开启事务

事务结束：End Transaction;

start transaction;

update user set balance=900 where name='wsb'; #买支付100元

update user set balance=1010 where name='egon'; #中介拿走10元

update user set balance=1090 where name='ysb'; #卖家拿到90元

commit;

commit ;提交

#出现异常，回滚到初始状态

rollback ;回滚
只要没有commit提交 就可以回滚到初始的金额

start transaction;

update user set balance=900 where name='wsb'; #买支付100元

update user set balance=1010 where name='egon'; #中介拿走10元

update user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到

rollback;

commit;

没有commit; 执行rollback;

mysql> start transaction;

mysql> update user set balance=900 where name='wsb'; #买支付100元

Query OK, 1 row affected (0.02 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set balance=1010 where name='egon'; #中介拿走10元

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set balance=1090 where name='ysb'; #卖家拿到90元

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;

+----+------+---------+

| id | name | balance |

+----+------+---------+

|  1 | wsb  |     900 |

|  2 | egon |    1010 |

|  3 | ysb  |    1090 |

+----+------+---------+

3 rows in set (0.00 sec)

回滚到初始

mysql> rollback;

Query OK, 0 rows affected (0.12 sec)

mysql> select * from user;

+----+------+---------+

| id | name | balance |

+----+------+---------+

|  1 | wsb  |    1000 |

|  2 | egon |    1000 |

|  3 | ysb  |    1000 |

+----+------+---------+

3 rows in set (0.00 sec)

commit ; 提交后再执行 rollback; 不能回滚了

只要不执行commit; 就不会修改数据你都可以任意回滚回去

ysql> update user set balance=900 where name='wsb'; #买支付100元

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set balance=1010 where name='egon'; #中介拿走10元

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set balance=1090 where name='ysb'; #卖家拿到90元

Query OK, 1 row affected (0.05 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;

+----+------+---------+

| id | name | balance |

+----+------+---------+

|  4 | wsb  |     900 |

|  5 | egon |    1010 |

|  6 | ysb  |    1090 |

+----+------+---------+

3 rows in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;

+----+------+---------+

| id | name | balance |

+----+------+---------+

|  4 | wsb  |     900 |

|  5 | egon |    1010 |

|  6 | ysb  |    1090 |

+----+------+---------+

3 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;

+----+------+---------+

| id | name | balance |

+----+------+---------+

|  4 | wsb  |     900 |

|  5 | egon |    1010 |

|  6 | ysb  |    1090 |

+----+------+---------+

3 rows in set (0.00 sec)

 开启事务检测sql语句的执行，如果监测到一条sql语句有错误时候，执行失败，立马所有执行回滚操作

巴特西

mysql 内置功能事务介绍

最新文章

热门文章

巴特西

mysql 内置功能 事务 介绍

最新文章

热门文章

mysql 内置功能事务介绍