Hello Bernd,
On 3/28/2016 3:36 PM, Lentes, Bernd wrote:
----- On Mar 27, 2016, at 2:49 PM, Reindl Harald h.rei...@thelounge.net 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
convert
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
MyISAM tables
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
command.
Here is a reference from the 5.0 manual to illustrate that this behavior
has been around for a long time:
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-transactions.html
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
autocommit=0
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
COMMIT.
Bernd
--
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
