Sunday, February 11, 2007

MySQL: Transactions and Autocommit

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.

Autocommit


With autocommit enabled, every statement is wrapped within its own transaction. Successful execution of a statement is implicitly followed by a COMMIT, and the occurrence of an error aborts the transaction, rolling back any changes.

By default, autocommit is enabled in MySQL. You can check the current setting by executing the following statement:
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

The @@ prefix denotes a server variable. The 1 means that autocommit is currently enabled. Let's leave it enabled so we can test the difference with not using transactions.

Transactions and Storage Engines


First, we create a table backed by a storage engine that is capable of handling transactions. I'm using the new FALCON engine in these examples, but you can try it with any of the storage engines that is capable of handling tranactions, such as InnoDB, ndbcluster, PBXT or SolidDB. If you want to try with falcon, please download and install MySQL 5.2.

Let's create that FALCON table in the test database:
mysql> use test;
Database changed
mysql> create table test_falcon(i int primary key)
-> engine = falcon
-> ;
Query OK, 0 rows affected (0.70 sec)

A word of warning: be sure to check the message after creating the table. If you accidentally type the name of the storage engine wrong, a table might be created anyway. If you see a warning after creating the table, always check the warning:
mysql> create table t(i int) engine = ferrari;
Query OK, 0 rows affected, 1 warning (0.14 sec)

mysql> show warnings;
+-------+------+--------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------+
| Error | 1286 | Unknown table engine 'ferrari' |
+-------+------+--------------------------------+
1 row in set (0.00 sec)

You can completely avoid this problem by including NO_ENGINE_SUBSTITUTION in the SQL_MODE. (In fact, I recommend to include it and make sure the proper mode is set when starting the server by including it in the [mysqld] section of your my.ini or my.cnf option file)

Testing Atomicity


Now, we execute a single statement that inserts two rows of data into the table:
mysql> insert into test_falcon values (1),(2);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0

The query executed succesfully, so we can assume two rows are now stored in the table. We can of course attempt to roll back to undo these changes:
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

Of course, when we check, the rows are still there:
mysql> select * from test_falcon;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.02 sec)

This is completely normal and expected behaviour. The rows were automatically committed right after the insert statement was executed: that is the meaning of autocommit.

We will now attempt to add three more rows to the table:
mysql> insert into test_falcon values (3),(4),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Whoops! This statement did not execute succesfully. We attempted to insert three rows, but at least one of them would violate the PRIMARY KEY constraint if it would be inserted. So at least, the violating row is rejected. But what about the other rows? Let's find out:
mysql> select * from test_falcon;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

Aha! The statement did not execute successfully, and therefore, all changes that would result from executing that statement are undone. With a posh word, we can say that the statement was executed atomically. The property of transactions that allows for atomic execution of statements is called atomicity

To say that the statement was executed atomically just means that the entire statement, and all of the changes it would achieve is executed as one single unit, undividable. If it succeeds, all associated changes on all rows it touches are made permanent. If it fails, none of the changes are made.

So, Atomicity ensures that statement execution is always complete: It either completely succeeds, or it completely fails. How does this compare to not using transactions at all? Well, we can test this with the MyISAM storage engine. The MyISAM storage engine does not implement transactions, and has no understanding of the autocommit setting. It is completely ignorant of it, because it can't commit or rollback anyway.

So, let's create a table again, this time using the MyISAM storage engine:
mysql> create table test_myisam (i int primary key)
-> engine = myisam
-> ;
Query OK, 0 rows affected (0.00 sec)

Again, we insert two rows in a single statement.
mysql> insert into test_myisam values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

Now for the interesting bit. Again, we execute one statement in order to insert multiple rows, and again, we deliberately construct the statement in a manner to violate the primary key constraint:
mysql> insert into test_myisam values (3),(4),(1),(5);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Like with the test_falcon table, this statement is not acceptable, because there is again at least one row that would violate the PRIMARY KEY constraint if it would be inserted. So, how is this different from the previous test? Let's find out by looking what's in the table...
mysql> select * from test_myisam;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)

Boom! The last statement attempted to insert no less than four rows, yet it added only two! Why only two? Well, after inserting two rows (3 and 4) succefully, a row was encountered that would violate the primary key constraint if it would be inserted (the value 1 was already present). At that point, an error condition was raised, and execution stopped. The remainder of the rows (5) was not even processed.

It's impossible give a clear answer to the question whether the statement succeeded or failed. We ordered four rows to be inserted, yet it failed to insert two of them. Clearly, the statement did not succeed completely. It did not fail completely either, because it succeeded in inserting two of the four rows.

This indeterminate state is the consequence of the lack of atomicity. The MyISAM engine does not support atomic execution of statements. The statement may succeed for some of the rows, and fail for others. This is simply impossible for the transactional engines. Statements on transactional statements are always executed atomically, regardless of the setting of autocommit.

Disabling Autocommit


So, I hope it's clear now why autocommit has everything to do with transactions. Still for a lot of people, the added value for using transactions is the ability to execute multiple statements within a single transactions. To do that, one needs to disable autocommit.

Within an application's session it is pretty easy to disable autocommit. One just needs to execute the proper SET statement:
SET autocommit=0

However, a lot of people are not really satisfied with this solution. Especially those that are used to Oracle, is seems daft to have to explicitly specify it.

There is a trick one can use to automatically disable autocommit for each newly created session. In the my.ini or my.cnf configuration file, you can specify the init_connect option. This option can be used to automatically execute a statement or sequence of statements at the beginning of each new session.
[mysqld]
init_connect='SET autocommit=0'

It's documented in the manual, and the example happens to use autocommit. Take heed though: the statements specified for the init_connect option are not executed for users that have the SUPER privilege.

38 comments:

Sheeri K. Cabral said...

Congrats! This article was linked from SSWUG.org as a members only article.

Alejandro Gomez said...

Very useful post.
Thanks

James said...

Great post, especially the init_connect info for us Oracle folks who liken autocommit=1 to nails across a chalkboard. Thanks...

rpbouman said...

Thanks all for the kind comments!

Anonymous said...

useful posting..

Anonymous said...

good article

Anonymous said...

do we have to alaways put commit statement after insert or update if we set autocommit = 0

rpbouman said...

Hi Anonymous,

"do we have to alaways put commit statement after insert or update if we set autocommit = 0"

No. The whole point of having autocommit off is that you can issue multiple statements and commit them all at once.

If autocommit is off and you are going to commit explicitly anyway after each statement, then you're better off setting autocommit on.

Prakash GR said...

Nice article.

Cheers

Prkash GR

Anonymous said...

Is there a way to set autocommit=0 for specific users only?

Anonymous said...

Very good and useful article. But to group statements in the same transaction, wouldn't you typically do START TRANSACTION instead of setting autocommit to 0? I assume that works...

rpbouman said...

@Anonymous1: "Is there a way to set autocommit=0 for specific users only?"

Not that I know. Would be a nice feature though.

@Anonymous2: "wouldn't you typically do START TRANSACTION instead of setting autocommit to 0? I assume that works..."

Oh yes, that does work. I mainly wrote the article to explain the diff between "autocommit on" and "no transactions", but you raise a good point, i probably should have been more complete and mention that syntax. Thanks!

Anonymous said...

Roland
Thanks for the post
Have a question:
I noticed on InnoDB if autocommit=1
and you cancel update statement with Ctrl-C on the middle, the data is still commited partially. This is bad, as it really should rollback.
with autocommit=0 it works fine.
Any comments on that?
THANKS
Dmitriy

rpbouman said...

Hi Dmitry!

"if autocommit=1 and you cancel update statement with Ctrl-C on the middle, the data is still commited partially. This is bad, as it really should rollback."

uhm..if it does, this is a bug.

That said, with all respect, i highly doubt it. Please tell me more about your execution environment (mysql command line? query browser?) your table definitions (do SHOW CREATE TABLE), your dataset and your update statement.

kind regards,

Roland

Anonymous said...

I have an example but can't copy and paste ast it si disabled.
any way to send you an example?
just try it it is simple if you have a table non productin wth at least 200k rows
issue simmilar statement agaibst column that has at least varchar(160)
update mytable set mycolumn = RPAD('H',150,'K');
as its running issue Ctrl-C it will says that the query is canceled. However check data and see it is commited and you can;t rollback. howrver is use disable autocommit it works fine.

rpbouman said...

Hi Dmitry!

you can always mail me at roland.bouman@gmail.com

Can you please tell me whcih tool you are doing the Ctrl+C in? Also, can you please post the SHOW CREATE TABLE statement? I trust you of course but I want to be 100% sure it's an InnoDB table.

Thanks!

Roland

Anonymous said...

I connected to server vi mac terminal through ssh
after that just a command line to connect to sql
the server runs on linux 32-bit server

rpbouman said...

Thanks! and your SHOW CREATE TABLE ?

Anonymous said...

Roland
Your window is not very friendly , I can't copy and paste
but i checked and it is InnoDB
ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_inicode_ci
I will send you email, but you can do you test on any 200K table. it is simple.

Dmitriy

rpbouman said...

Hi!

sorry if you had an issue with the blogger comment interface. Indeed it has its flaws (though I can paste fine)

I'll try and test later this evening. Thanks so far !

rpbouman said...

Dmitriy, you are right.

This looks like a really nasty bug to me. I have filed a bug report here:

http://bugs.mysql.com/bug.php?id=45923

If anybody can reproduce it, please add your information to the bug report. I hope it will be fixed soon.

thanks again! this was a really good spot.

Anonymous said...

Thanks you , Roland for reporting teh Bug
I've added some comments there.
it seams to me that even MYSQL doesn't like to rely on the autcommit=1, as you neve know when commit took place, expecially in Master/Slave env.
Of course MYSQL should fix it, however
I'd always recoomment to set it to 0.
as I've explained in my comments.

Reagrds,
Dmitriy Royzenberg

rpbouman said...

Hi Dmitriy

"it seams to me that even MYSQL doesn't like to rely on the autcommit=1"

mm, with all due respect to Michael who commented on bug 45923 - I am not ready to accept his opionn as the final word from all of MySQL. As for never knowing when commit took place - this doesn't really convince me. The way I see it, there is no problem at all with noticing the abort. I mean, seriously, if the process is in a stage where some changes cannot be undone anymore, the abort request should simply be discarded.

Another way of looking at it is this. Suppose you would do as you and Michael say, and leave autocommit disabled, and now you do a manual commit, and then a Ctrl+C aborts the commit command - you would be in exactly the same situation, right? It should be like this IMO: either the server knows it can recover and rollback, and chooses to honour the abort command, or it knows it cannot guarantee it, and simply ignores the abort command, pretending it arrived late or something like that.

Anyways, thanks for spotting the bug.

Anonymous said...

Hi Roland
The way I see it, is if you disable autocommit
even if you issue Ctrl-C , you are in pretty
safe state. Even if you transaction is partially processed and you see some changes are made to the data. these changes are NOT YET COMMITTED and they only visible to your session. You have an Option
to either Commit or Rollback. Of course you would choose to Rollback in this case as you SQL failed.
But even if you do nothing and decide to exit, MYSQL will do implicit Rollback once you terminate your session to preserve transaction consistency.
Hence your transaction will not cause any issue, and in addition your Master/Slave consistency is preserved as well.
You can do the same test you did previously while disabling autocommit. to see how mysql behaves.
Regards,
Dmitriy Royzenberg

Anonymous said...

Roland,
Is there a good way to make init_connnect also apply to SUPER user? It seamed disabled for a reason, but often DBAs are connect as SUPER and it is annoying
if you have to set all variables manually.

Thanks,
Dmitriy

Anonymous said...

FYI, As per Brian
this Bug is not found in previous release
http://forums.mysql.com/read.php?22,269979,270026#msg-270026
So it is seams like a 5.1 issue.
Dmitriy

rpbouman said...

Dmitriy, really, it should make no difference at all. The semantics of autocomit is as if autocommit is disabled, and every statement is executed as

START TRANSACTION;

...statement...;

COMMIT;

Now, if you agree that the above sequence of statements should execute atomically even if an abort is received along the way, then it shouldn't matter how this sequence of statements came about - implicitly through autocommit or explicitly as true separate statements. In both cases there is a possibility of noticing the abort when it is too late already (read: when you can't undo the changes anymore), and it is a bug to actually chicken out of the statement in that case.

rpbouman said...

Dmitriy,

"Is there a good way to make init_connnect also apply to SUPER user?"

No - there isn't. Unfortunately you can't just set

autocommit=0

in your my.cnf either.

rpbouman said...

Dmitriy,

ok. so it's a regression then.

I suggest to move any discussion about the bugs to bugs.mysql.com. Please note that all additional comments should be posted to bug http://bugs.mysql.com/bug.php?id=45309 as the bug report I filed is a duplicate.

Note that a patch has been pushed already, so it'd be really great if someone could check it out. Thank you.

Anonymous said...

my.cnf

init_connect='SET autocommit=0'


this setup is not working. Is there any alternate way ?

mani said...

Auto commit off

linux comes with an application called 'expect'. it interacts with the shell in such a way as to mimic your key strokes. it can be set to start mysql, wait for you to enter your password. run further commands such as SET autocommit = 0; then go into interactive mode so you can run any command you want.


spawn /usr/local/mysql/bin/mysql
expect "mysql>"
send "set autocommit=0;\r"
expect "mysql>" interact


Try to this

rpbouman said...

@Anonymous: I have no idea why it isn't working for you. It should. What steps did you take to verify the autocommit setting?

rpbouman said...

@mani: thanks, useful tip :)

Naganathan G said...

Hi Mani, I have applied your way. It is working . What a great tip




Regards,
Naganathan G
Pace Automation Ltd
Chennai

anonymous transactions said...

anonymous transactions

Omar Abdallah said...

Thanks alot, I didn't know about falcon's reaction against failed statements.

rpbouman said...

Omar, it's not really relevant anymore since development on Falcon has stopped.

ie10 said...

Thanks. working perfectly.

UI5 Tips: Persistent UI State

This tip provides a way to centrally manage UI state, and to persist it - automatically and without requiring intrusive custom code sprinkle...