Relationship between autocommit and SQL rollback in mysql

Relationship between autocommit and SQL rollback in mysql. First, I think this answer should be related to autocommit. First, let's explain the background. Let's reproduce the problem: Execute SQL: www.2cto.com 1 root <a href =" http://my.oschina.net/u/203327 "Target =" _ blank "rel =" nofollow "> @ wlb </a> 12:48:30> update wlb_schedule_02 set status = status * 10 where gmt_create <=" 1:00:00 "; then error: 1 ERROR 1197 (HY000): Multi-statement transaction required more than 'max _ binlog_cache_size 'bytes of storage is reported; increase this mysqld variable and try again first, this operation is performed on multiple rows. If one row has an error or an error occurs when a row is executed, will the previous statement be rolled back? The answer is yes. But when will it not be rolled back? What is the relationship between this and autocommit? 1 show VARIABLES like '% autocommit %'; show whether autocommit is enabled, disable autocommit1set autocommit = 0; then insert data to table test1, where the field is not empty, therefore, when a null value is inserted, 1 insert into test1 (age, name) VALUEs (20, '1'); 2 insert into test1 (age, name) VALUEs (20, '2'); 3 www.2cto.com insert into test1 (age, name) VALUEs (20, null); 4 insert into test1 (age, name) VALUEs (20, '4 '); because it will not be automatically submitted, the execution of the third will be all rolled back, so there is no data in the database and then open autocommit 1 set autocommit = 1; then execute the insert statement: 1IN Sert into test1 (age, name) VALUEs (20, '1'); 2 insert into test1 (age, name) VALUEs (20, '2 '); 3 insert into test1 (age, name) VALUEs (20, null); 4 insert into test1 (age, name) VALUEs (20, '4'); because it is automatically submitted, therefore, when the first two items are executed, the data will be inserted into the database, and an error will be reported when the third item is executed. Therefore, the database contains two pieces of data. Let's simulate the actual problem. We set autocommit to true www.2cto.com and then execute the following statement 1 insert into test1 (age, name) VALUEs (20, '1 '), (20, '2'), (20, null), (20, '4'); although it is autocommit, the entire statement will report an error, an error is reported when the third entry is executed, but the first two are not submitted successfully. To sum up, if you insert or modify multiple rows of Data multiple times, autocommit will affect whether the data that is successfully executed is submitted. If you execute a statement, the entire operation will be executed as a transaction, so it does not work if autocommit is enabled. If a statement is successful, it is rolled back. Author Ji ziming