登录数据库
Windows PowerShell
版权所有 (C) Microsoft Corporation。保留所有权利。
PS C:\Users\xxxx> mysql -h localhost -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 296
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
创建账户表，插图数据
mysql> show show databases;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'show databases' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gprsproject |
| mysql |
| netcore |
| performance_schema |
| smartdncserver |
| sys |
| zoo |
+--------------------+
8 rows in set (0.00 sec)
mysql> use zoo;
Database changed
mysql> create table account//创建账户表
-> (
-> id int primary key auto_increment,
-> name varchar(40),
-> money float
-> ) character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.46 sec)
mysql> insert into account(name,money) values('a',100);//插入一条数据
Query OK, 1 row affected (0.13 sec)
mysql> insert into account(name,money) values('b',100);//插入一条数据
Query OK, 1 row affected (0.07 sec)
模拟场景:转账失败
用户a与用户b 银行卡各有 余额100元，现用户a向用户b转账50元
//开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
用户a开始转账，余额减少50元
mysql> update account set money=money-50 where name='a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查询用户a余额
mysql> select * from account where name='a';
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 50 |
+----+------+-------+
1 row in set (0.00 sec)
查询用户a余额已经减少了50元，加入此时系统出现bug,崩溃了。用户a的钱转出了，但还没有到用户B账户
用quit命令模拟系统崩溃
mysql> quit
Bye
PS C:\Users\82511>
再次连接数据库，查询用户a的余额
PS C:\Users\82511> mysql -h localhost -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 301
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use zoo;
Database changed
mysql> select * from account where name='a';
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 100 |
+----+------+-------+
1 row in set (0.06 sec)
mysql>
可见用户a的余额并未减少，保持不变。这是因为系统异常时，数据库会自动回滚掉用户a转账的sql语句（update account set money=money-50 where name='a';）造成的影响，也就是说这条sql语句没有执行
模拟场景:转账成功
用户a与用户b 银行卡各有 余额100元，现用户a向用户b转账50元
mysql> start transaction//开启事务
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set money=money-50 where name='a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update account set money=money+50 where name='b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 50 |
| 2 | b | 150 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 50 |
| 2 | b | 150 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql>
使用
commit提交事务，这样事务提交前执行的sql语句才会真正执行。
如果想要在事务提交之前，不想执行sql语句，可以使用回滚事务命令rollback
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 100 |
| 2 | b | 100 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set money=money-50 where name='a';//a开始转账
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update account set money=money+50 where name='b';//b接到转账
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 50 |
| 2 | b | 150 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;//回滚事务
Query OK, 0 rows affected (0.08 sec)
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 100 |
| 2 | b | 100 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql>
public bool UpdateUserInfo(Userinfo model1,Userinfo model2)
{
StringBuilder Sqlstr = new StringBuilder();
Sqlstr.Append(" UPDATE user set ");
Sqlstr.Append("Name=@Name,Age=@Age,Salary=@Salary");
Sqlstr.Append(" where ID=@ID");
try
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
IDbTransaction transaction = connection.BeginTransaction();
var query = connection.Execute(Sqlstr.ToString(), model1);
var query = connection.Execute(Sqlstr.ToString(), model2);
transaction.Commit();//提交事务
return query>0;
}
}
catch(System.Exception)
{
transaction.Rollback();//回滚事务
return false;
}
}
在事务开启后，不提交
1没有提交也没有回滚，会导致表一直锁着，那么在此期间如果有涉及被锁表的操作，就一直等着。
但是sql server进程重启的话，就会解除锁定，相当于回滚，你的操作等于取消。
网友评论