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 →
10 Older Entries »
Introduction Unlike SQL Server which, by default, relies on the 2PL (Two-Phase Locking) to implement the SQL standard isolation levels, Oracle, PostgreSQL, and MySQL InnoDB engine use MVCC (Multi-Version Concurrency Control). However, providing a truly Serializable isolation level on top of MVCC is really difficult, and, in this post, I’ll demonstrate that it’s very difficult … Continue reading A beginner’s guide to the Phantom Read anomaly, and how it differs between 2PL and MVCC →
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.
To this extent, I will set up two clusters using MySQL-Sandbox. The instructions for MGR in …[Read more]
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
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
copy data from a table?
A: Both tools do not change the server’s default transaction isolation level. Use either
I was recently asked this question by an experienced academic at the NY Oracle Users Group event I presented at.
Does MySQL support ACID? (ACID is a set of properties essential for a relational database to perform transactions, i.e. a discrete unit of work.)
Yes, MySQL fully supports ACID, that is Atomicity, Consistency, Isolation and Duration. (*)
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 …[Read more]
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
MySQL replication in action - Part 1: GTID &
CoMySQL replication in action - Part 2 - Fan-in
In the previous article, we saw the basics of establishing replication from multiple origins to the same destination. By extending that concept, we can deploy more complex topologies, such as the point-to-point (P2P) all-masters topology, a robust and …
Long running transactions can be problematic for OLTP workloads, particularly where we would expect most to be completed in less than a second. In some cases a transaction staying open just a few seconds can cause behaviour that is entirely unexpected, with the developers at a loss as to why a transaction remained open. There are a number of ways to find long running transactions, luckily versions of MySQL from 5.6 onwards provide some very insightful instrumentation.
Here we will use the information_schema coupled with the …[Read more]
Today I was doing some tests with XA transactions in MySQL
The output of the XA RECOVER command to list transactions was hard to read because of the representation of the data column:
The good news is that 5.7 has transaction information in performance_schema:
mysql> select trx_id, isolation_level, state, xid, xa_state, access_mode[Read more]
-> from performance_schema.events_transactions_current;
| trx_id | isolation_level | state | xid | xa_state | access_mode |
| NULL | REPEATABLE READ | ACTIVE | x-1 | PREPARED | READ WRITE |
| 421476507015704 | REPEATABLE READ | …
10 Older Entries »