mysql中autocommit與sql回滾的關系
今天群裡有人問了一個mysql中sql執行出錯是否會回滾的問題。第一個感覺這個答案應該和autocommit有關。
首先交代下背景，問題復現一下：
執行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<="2012-08-16 1:00:00";
然後報了error：
1
ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
首先這是對多行進行了操作，如果其中一行出錯或者執行到某行時出錯，那麼之前的語句是否會回滾？答案是肯定的。
但是什麼時不會回滾，什麼時候又會回滾呢？這個和autocommit又有什麼關系呢？
1
show VARIABLES like '%autocommit%';
顯示autocommit是否打開，然後關閉autocommit
1
set autocommit=0;
然後對test1表插入數據，其中那麼字段是非空的，所以插入空值時肯定報錯了
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');
因為不會自動提交，所以執行到第三條是會全部回滾，所以最後數據庫中沒有數據
然後把autocommit打開
1
set autocommit=1;
再執行插入語句：
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');
因為會自動提交，所以執行完前兩條的時候，數據會插入數據庫，然後第三條的時候就報錯了。所以數據庫中會有兩條數據。
解析來我們來模擬今天的實際問題，我們把autocommit設置成ture
www.2cto.com
然後執行下面的語句
1
INSERT INTO test1 (age,name) VALUEs (20,'1'),(20,'2'),(20,null),(20,'4');
雖然是autocommit了，整個語句也會報錯，執行到第三條的時候也會報錯，但是前兩條也沒有提交成功。
總結一下，如果是多次插入或修改多行的數據時，autocommit會影響執行成功的數據是否提交。如果在一條語句中執行的時候，那麼會把整個操作當成一個事務來執行，所以這時候autocommit是否打開也沒有作用了。單條語句成功就成功，失敗即回滾。
作者 艮子明
