MySql事务

登录数据库

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进程重启的话，就会解除锁定，相当于回滚，你的操作等于取消。