Web lists-archives.com

Re: need help from the list admin

On 3/30/2016 1:26 PM, Lentes, Bernd wrote:
----- On Mar 30, 2016, at 7:04 PM, Reindl Harald h.reindl@xxxxxxxxxxxxx wrote:

So i should use the default (autocommit=1)?

no, you should what is appropriate for your application

if you don't care about inserts/updates triggered by let say a
webrequest are half written due a crash or restart use autocommit

Autocommit means that every statement is committed implicitly. Right ?
Commit works only in conjunction with InnoDB tables and transaction. That's what i understand.
I thought when i make e.g. an insert into a InnoDB table, and that insert is not done completely (due to a crash, restart, what ever)
it is rolled back automatically after the restart. Is that wrong ?

it depends: If the transaction made it into the Binary Log (if it is enabled) and the REDO log as "committed", then InnoDB will finish the commit (put the actual data in its proper place in the data files) after recovery. If not, it will rollback and your data remains as it was.


if you care that all or nothing is written use transactions
if you care that way don't mix non-transactional tables with innodb

I'm planning to convert the MyISAM tables to InnoDB.

That will solve many of your data consistency problems (particularly those related to how things are recorded in the Binary Log), presuming you surround changes that involve multiple commands with transaction control commands.

If your sets of data changes only need one command to complete, then the overhead of issuing explicit START TRANSACTION and COMMIT commands is just going to create work you don't need for your workflow. If you need more than one command to make a complete and consistent update to your data, then use a transaction. If not, operating in autocommit mode is ideal.


Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ for details.

MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql