Re: need help from the list admin
- Date: Mon, 28 Mar 2016 15:53:06 -0400
- From: "shawn l.green" <shawn.l.green@xxxxxxxxxx>
- Subject: Re: need help from the list admin
On 3/28/2016 3:36 PM, Lentes, Bernd wrote:
----- On Mar 27, 2016, at 2:49 PM, Reindl Harald h.reindl@xxxxxxxxxxxxx wrote:
Am 27.03.2016 um 14:34 schrieb Lentes, Bernd:
You would be better served by first converting your MyISAM tables to
InnoDB to stop mixing storage engine behaviors (transactional and
non-transactional) within the scope of a single transaction. But if you
cannot convert them, using MIXED will be a good compromise.
Is this a big problem ? Something to take care of ? Currently we have a mix.
I will ask the girl who developed it why we have both kinds. I hope i can
surely - when you have non-transactional tables involved in
updates/inserts you can go and forget using transactions at all since
interruption or rollback would not rollback already written changes in
transactions are all about consistency - impossible with a mix of InnoDB
and MyISAM tables
I read that the converting is not difficult. But has the code of our webapp to be changed ? It's written in php and perl.
What i understand is that inserts/updates/deletions in InnoDB tables have to be commited. Yes ?
No. The server's default is to have --autocommit=1, which means that
there is an implicit commit at the end of every command. You do not need
to state explicitly "COMMIT" every time you want this to happen.
In fact, disabling autocommit has gotten many new users into trouble
because they did not understand the behavior they changed.
This has to be done in the code ? Or can we use the system variable autocommit ?
You should need to change nothing.
That means that everything is commited immediately ? Is this a good solution ?
It is going to behave better than the data you have now. The changes to
the tables you will convert from MyISAM to InnoDB will not become
visible to other sessions until after the COMMIT (implicit or explicit)
completes. For finer-grained control over data visibility, you need to
understand the broader topic of transaction isolation.
What means "By default, client connections begin with autocommit set to 1" in the doc ?
It means that every command is already running in its own private
mini-transaction. To start a multi-statement transaction you do not need
to disable autocommit, you simply need to use the START TRANSACTION
Here is a reference from the 5.0 manual to illustrate that this behavior
has been around for a long time:
That every client connection established via perl/php is started with autocommit=1 ?
It is as long as:
1) the global variable autocommit=1
2) the client does nothing to change its own session variable to
And when does the commit happen ? When the connection is closed ? Is that helpful ?
The commit happens at the end of each command. If you need to contain
multiple commands within a single transaction, use START TRANSACTION and
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/
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql