从SQL语言的分类谈COMMIT和ROLLBACK的用法

分享一下我老师大神的人工智能教程。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！https://blog.csdn.net/jiangjunshow

               

从SQL语言的分类谈COMMIT和ROLLBACK的用法

mysql禁用autocommit,以及遇到的问题

mysql禁用autocommit,以及遇到的问题

张映 发表于 2011-03-15

分类目录： mysql

mysql的autocommit（自动提交）默认是开启，其对mysql的性能有一定影响，举个例子来说，如果你插入了1000条数据，mysql会commit1000次的，如果我们把autocommit关闭掉，通过程序来控制，只要一次commit就可以了。

1,我们可以通过set来设置autocommit

查看 复制 打印 ?

1. mysql> set global init_connect="set autocommit=0";  //提示你用权限更高的财户来设置  
2. ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation  
3. mysql> set autocommit=0;  
4. Query OK, 0 rows affected (0.00 sec)  
5.   
6. mysql> select @@autocommit;   //查看一下autocommit的设置  
7. +--------------+  
8. | @@autocommit |  
9. +--------------+  
10. |            0 |  
11. +--------------+  
12. 1 row in set (0.00 sec)  

2,我们可以修改mysql的配置文件my.cnf来关闭autocommit

查看 复制 打印 ?

1. [mysqld]  
2. init_connect='SET autocommit=0'  //在mysqld里面加上这些内容  

用第二种方法关，有一点要注意，连接mysql用户的权限不能大于启动mysql的用户的权限，不然init_connect='SET autocommit=0'根本不会启作用，也不会报任何错误，汗一个先。看以下实例

查看 复制 打印 ?

1. zhangy@ubuntu:~$ mysql -umysql  
2. Welcome to the MySQL monitor.  Commands end with ; or \g.  
3. Your MySQL connection id is 1  
4. Server version: 5.5.2-m2-log Source distribution  
5.   
6. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
7.   
8. mysql> select @@autocommit;     //mysql是启动用户，关闭autocommit成功  
9. +--------------+  
10. | @@autocommit |  
11. +--------------+  
12. |            0 |  
13. +--------------+  
14. 1 row in set (0.00 sec)  
15.   
16. mysql> Ctrl-C -- exit!  
17. Aborted  
18. zhangy@ubuntu:~$ mysql -uroot  
19. Welcome to the MySQL monitor.  Commands end with ; or \g.  
20. Your MySQL connection id is 2  
21. Server version: 5.5.2-m2-log Source distribution  
22.   
23. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
24.   
25. mysql> select @@autocommit;    //用root财户启动，不成功。  
26. +--------------+  
27. | @@autocommit |  
28. +--------------+  
29. |            1 |  
30. +--------------+  
31. 1 row in set (0.00 sec)  

这个会不会是mysql的bug呢？我在网上找了找这方面的问题，还真有。部分内容如下：
If a user has SUPER privilege, init_connect will not execute
(otherwise if init_connect will a wrong query no one can connect to server).

Note, if init_connect is a wrong query, the connection is closing without any errors
and next command will clause 'lost connection' error.

里面有一点说的很清楚If a user has SUPER privilege, init_connect will not execute，如果用户有更高级的权限，init_connect根本不会执行。

0

 SQL Server 事务执行一半出错是否自动回滚整个事务　

2013-08-08 16:55:21

标签： sql server rollback whole transcation  回滚事务

原创作品，允许转载，转载时请务必以超链接形式标明文章  原始出处 、作者信息和本声明。否则将追究法律责任。 http://lzf328.blog.51cto.com/1196996/1266888

大家都知道SQL Server事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除。

所以是不是说事务出错一定会回滚整个事物呢？　先看几个个例子：　

--create table

createtabletestrollback(id intprimarykey, name varchar(10))

 

SETXACT_ABORTOFF--Default Settings 

begintran

insertinto testrollback values (1,'kevin')

insertinto testrollback values (2,'kevin')

insertinto testrollback values (1,'kevin')

insertinto testrollback values (3,'kevin')

committran

三条成功插入只有第三条语句出错回滚

--use SET XACT_ABORT ON

SETXACT_ABORTON;

begintran

insertinto testrollback values (1,'kevin')

insertinto testrollback values (2,'kevin')

insertinto testrollback values (1,'kevin')

insertinto testrollback values (3,'kevin')

committran

 

select*from testrollback 

全部回滚没有数据插入

 

---use try catch to catch error and rollback whole transcation 

begintran

begintry

insertinto testrollback values (1,'kevin')

insertinto testrollback values (2,'kevin')

insertinto testrollback values (1,'kevin')

insertinto testrollback values (3,'kevin')

committran

endtry

begincatch

rollback

endcatch

 

全部回滚没有数据插入

对于上面的测试可以看到默认情况下SQL Server只是Rollback出错的语句而不是整个事物。所以如果想Rollback整个事物的话可以通过SET XACT_ABORT选项设置或者使用Try Catch之类的捕获错误进行Rollback. 

对于出现网络问题会跟上面的结果有点不一样。在执行事务的过程中，如果Client断开，那么SQL Server会自动Rollback整个事物。

在SSMS客户端执行第一条语句，去掉commit Tran 

SETXACT_ABORTOFF--Default Settings 

begintran

insertinto testrollback values (1,'kevin')

insertinto testrollback values (2,'kevin')

insertinto testrollback values (1,'kevin')

insertinto testrollback values (3,'kevin')

之后断开连接，在服务器上用DBCC OPENTRAN查看open的事务：

间隔一段时间再执行发现DBCC OPENTRAN已经没有了，而且查询表数据也没有，说明整个事物回滚了。所以在客户端断开且事务没有完成的情况下整个事物回滚。

对于上面的测试微软有详细的解释：

If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Database Engine rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.

If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. For more information, see SET XACT_ABORT (Transact-SQL)

更多信息参考　Controlling Transactions

张映 发表于 2011-03-15

分类目录： mysql

mysql的autocommit（自动提交）默认是开启，其对mysql的性能有一定影响，举个例子来说，如果你插入了1000条数据，mysql会commit1000次的，如果我们把autocommit关闭掉，通过程序来控制，只要一次commit就可以了。

1,我们可以通过set来设置autocommit

查看 复制 打印 ?

1. mysql> set global init_connect="set autocommit=0";  //提示你用权限更高的财户来设置  
2. ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation  
3. mysql> set autocommit=0;  
4. Query OK, 0 rows affected (0.00 sec)  
5.   
6. mysql> select @@autocommit;   //查看一下autocommit的设置  
7. +--------------+  
8. | @@autocommit |  
9. +--------------+  
10. |            0 |  
11. +--------------+  
12. 1 row in set (0.00 sec)  

2,我们可以修改mysql的配置文件my.cnf来关闭autocommit

查看 复制 打印 ?

1. [mysqld]  
2. init_connect='SET autocommit=0'  //在mysqld里面加上这些内容  

用第二种方法关，有一点要注意，连接mysql用户的权限不能大于启动mysql的用户的权限，不然init_connect='SET autocommit=0'根本不会启作用，也不会报任何错误，汗一个先。看以下实例