Transaction isolation levels are the least understood feature of
relational databases. Most developers don’t know them and just
use the default one. Actually, a relevant part of them even
believe they use MySQL without transactions.

Amongst isolation levels, READ UNCOMMITTED is the
least understood. So here’s a quick note about what it is and why
– if you know about it – you probably have false beliefs.

Basically, READ UNCOMMITTED is totally inconsistent.
It sees changes (new, deleted, modified rows) made by other
transactions, that didn’t COMMIT yet. And actually,
it’s possible that those transactions will fail, which leads
READ UNCOMMITTED to see something that will never
happen.

For most people (including me until recently)
database XA transactions are a fuzzy concept. In over eight years
with Percona, I have never had to deal with XA transactions. Then
a few weeks ago I got two customers having issues with XA
transactions. That deserves a post.

XA 101

What are XA transactions? XA transactions are useful when you
need to coordinate a transaction between different systems. The
simplest example could be simply two storage engines within
MySQL. Basically, it follows this sequence:

XA START

Some SQL statements

XA END

XA PREPARE

XA COMMIT or ROLLBACK

Once prepared, the XA transaction survives a MySQL crash. Upon
restart, you’ll see something like this in the MySQL error log:

Introduction While doing my High-Performance Java Persistence
training, I came to realize that it’s worth explaining how a
relational database works, as otherwise, it is very difficult to
grasp many transaction-related concepts like atomicity,
durability, and checkpoints. In this post, I’m going to give a
high-level explanation of how a relational database works
internally while … Continue reading How does a relational
database work →

In my previous post, we saw the usage of MySQL Group Replication
(MGR) in single-primary mode. We know that Oracle
does not recommends using MGR in multi-primary mode, but there is
so much in the documentation and in presentations about MGR
behavior in multi-primary, that I feel I should really give it a
try, and especially compare this technology with the already
existing multiple master solution introduced in 5.7: multi-source replication.

Installation

To this extent, I will set up two clusters using MySQL-Sandbox. The
instructions for MGR in …

How InnoDB initializes AUTO_INCREMENT counters is actually not a
bug, but a documented mechanism. There were some complaints and
even people who lost data over this. To initialize an
auto-increment counter after a server restart, InnoDB executes
the equivalent of the following statement on the first insert
into a table containing an AUTO_INCREMENT column. SELECT
MAX(ai_col) FROM table_name FOR

In this blog, I will provide answers to the Q
& A for the “Introduction into storage engine
troubleshooting” webinar.

First, I want to thank everybody for attending the July 14
webinar. The recording and slides for the webinar are available
here. Below is the list of your questions that
I wasn’t able to answer during the webinar, with responses:

Q: At which isolation level do

pt-online-schema-change

and

pt-archive

copy data from a table?

A: Both tools do not change the server’s default
transaction isolation level. Use either

This is contrary to the first Google response found searching
this question which for reference states “The standard table
handler for MySQL is not ACID compliant because it doesn’t
support consistency, isolation, or durability”.

The question is however not a simple Yes/No because it depends on
timing …

The binary log contains “events” that describe database changes.
On a basic installation with default options, it's not
turned on. This log is essential for accommodating the possible
following requirements:

Replication: the binary log on a master replication server
provides a record of the data changes to be sent to slave
servers. Point in Time recovery: allow to recover a database from
a full

Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.