MySQL: Transactions and Autocommit

by Roland Bouman

Some people believe that the ability to commit or rollback multiple statements as a single unit is a defining characteristic of the concept of transactions. They draw the -false- conclusion that enabling autocommit is the same as disabling transactions. It is easy to demonstrate why this is not true.


7 Comments

Synaptic
2007-08-19 01:23:46
Great. But in the real world, there are often records inserted into multiple tables with foreign key constraints. With autocommit enabled, when a subsequent query fails, you are left with orphaned records.


Showing some special case of inserting multiple records into one table with a single query doesn't really show anything other than the fact that you get paid per article.

Roland Bouman
2007-08-19 04:43:06
"Great. But in the real world, there are often records inserted into multiple tables with foreign key constraints. With autocommit enabled, when a subsequent query fails, you are left with orphaned records."


mm, you mean *without* foreign key constraints...Otherwise, I don't see how you can get orphans if your constraints are enabled?


As for "the real world": well, the real world is diverse. Some databases have autocommit on by default - others have it off by default. The purpose of this article was to explain why having autocommit on is still transactional - it does not aim to judge whether autocommit is good or bad.


"Showing some special case of inserting multiple records into one table with a single query doesn't really show anything other than the fact that you get paid per article."


Well, I guess you are living in another real world than I do. In my experience, inserting many rows per statement happens quite often, and it is in fact one of the reasons why transactions are great to have. So it is by no means special in my opinion. Obviously my example in this article is not convincing, but that is besides the point.


BTW - I don't really understand where the remark is coming from with regard to "getting paid per article".


I don't get paid for writing articles. I have had the odd offer to write a column or article in paid-for magazines for a fee, but so far I have declined those. I am not a full-time writer, and I fear that comitting to a deadline for a fee might sometimes mean submitting articles that don't live up to my personal quality standards. But suppose I would recieve money for writing - I still don't see why you should think badly of it.

Frank
2007-11-04 12:33:14
Very well written article. Thank you very much, you have cleared up doubt about using autocommit while still being transactional.
Earnie
2007-12-13 10:58:28
'"Great. But in the real world, there are often records inserted into multiple tables with foreign key constraints. With autocommit enabled, when a subsequent query fails, you are left with orphaned records."


mm, you mean *without* foreign key constraints...Otherwise, I don't see how you can get orphans if your constraints are enabled?'


It is possible to have data inserted into the controlling table that should not remain if the dependent rows do not successfully commit. In this instance the transaction needs to be one large transaction that commits only at the end of the process. If you are doing this, a SET autocommit=0 is always recommended (it's best not to assume).

Arnab
2008-02-11 15:07:14
What is the syntax to create an auto-commit transaction in SQL Server 2005.


Please Help

Roland Bouman
2008-02-11 15:24:29
Arnab,


To be sure, the article was about MySQL - not Microsoft SQL Server. For Microsoft SQL Server, look here:


http://msdn2.microsoft.com/en-us/library/aa213069(SQL.80).aspx


"Autocommit mode is the default transaction management mode of Microsoft® SQL Server™.
...
A SQL Server connection operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on."


I hope this helps

mike
2008-07-29 18:09:26
"BTW - I don't really understand where the remark is coming from with regard to "getting paid per article".


Pay no attention to this griefer. It was intended as an insult, as in, padding with useless content (like MS writing OS/2 KLOC's).