Max

Stolbynsky

IT manager. Consultant. Cloud architect.


Marginalia

This is a place where i would like to share some side notes about my life, thoughts and ideas. All listed below is my personal opinion and open for discussion. You can find there some different sides of my life and do not confuse if found something in-mixable. Life is really interesting thing and includes various sides.

MySQL 5 has completely weired transaction concept

November 16, 2012Max Stolbynsky0 Comments

Want to share one kind a weir thing I found today.

I’m long time use transactions in Mysql, but never run in case when transaction is opened, but not closed.And today in the testing process I have found that if you start transaction and then your script dies in a middle for any reason and it is not catched by exception which calls rollback, all data you have modified are PRESENT in db. So Mysql commits it automatically by default. And even more: all data modified INSIDE transaction are available to other connections IMMEDIATELY. So you risky to have inconsistent data in parallel queries. So seems like classic transaction model was avoided by default.

You need to setup autocommit = 0 manually after each connect to avoid it.

Maybe it is really easier for dummies who forget to close transaction by commit, but i was frustrated when i saw that.

So if you really need to use classical transaction model in Mysql 5 you need after each connect use following SQL statement:

 SET autocommit = 0

For more detailed reference you can visit official page:

http://dev.mysql.com/doc/refman/5.5/en/commit.html

Leave a Reply