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...?
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