Relationship between autocommit and SQL rollback in mysql _ MySQL

Relationship between autocommit and SQL rollback in mysql bitsCN.com
Relationship between autocommit and SQL rollback in mysql. First, I think this answer should be related to autocommit. First, let me explain the background. The problem is repeated: execute SQL: 1root @ wlb 12:48:30> update wlb_schedule_02 set status = status * 10 where gmt_create <= "1:00:00"; then an error is reported: 1 ERROR 1197 (HY000): Multi-statement transaction required more than 'max _ binlog_cache_size 'bytes of storage; 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 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 statement will be rolled back, so there is no data in the database and then open autocommit 1 set autocommit = 1; then execute the insert statement: 1 insert into tes T1 (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, when the first two items are executed, the data will be inserted into the database, and an error will be reported in the third step. Therefore, the database contains two pieces of data. Parse to simulate the actual problem today. we set autocommit to true 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: James bitsCN.com