CODEWITHSUNDEEP
mysqlcommitautocommit
Less
Less

Reputation: 3207

Cannot disable autocommit?

I have Ubuntu 12.04 guest VM with a MySQL 5.5.35 instance on it. I cannot seem to bypass / turn off autocommit feature...

I wanted to try out some scenarios related to SELECT ... FOR UPDATE;, so I opened two database sessions - one from the MySQL workbench on the VM itself, and another one from mysql CLI, on my Windows host. Whatever I do in either of the two sessions is instantly visible in another. Autocommit or no autocommit set, transaction or no transaction, explicit commit or no explicit commit...

I tried the following:

1) disabling it through configuration

[mysqld]  
autocommit = 0

which effectively set the global autocommit variable to OFF, according to SHOW GLOBAL VARIABLES. Does not work.

2) running this command as the very first one

set autocommit=0;
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

Does not work.

3) opening an explicit transaction

start transaction; // tried begin; as well
...
commit;

Does not work.

The damn database acts as auto-commit is always ON, for each single INSERT / UPDATE statement, even though database settings and variables show differently... commit statement always returns 0 rows affected.

Am I missing something here...? Could this relate to users / privileges in any possible way...?

Upvotes: 3

Views: 3051

Answers (1)

Marc B
Marc B

Reputation: 360762

Are you using a transaction-capable DB engine? e.g. if you're using MyISAM, then there are no transactions, and auto commit would stay on regardless

Upvotes: 2

Related Questions