intro
Multiversion concurrency control or MVCC is a mechanism that helps us provide concurrent access to the database for multiple transactions. Discover everything you need to know about MVCC and MVCC connect in this blog!
As you might already know, MySQL is a powerful beast. A part of the reason is that it consists of multiple storage engines that make up the DBMS. We’ve written about the ins and outs of one of the most important files in the primary database management system — InnoDB — before, but we haven’t yet told you about one of the mechanisms that makes it tick. That mechanism is called Multiversion Concurrency Control, or MVCC.
What is MVCC?
MVCC is a short acronym for a term referred to as Multiversion Concurrency Control. This is a method DBAs and developers use to optimize their database, and it is commonly used by multiple database management systems to accomplish a goal of providing concurrent access to the database and provide data consistency.
Consider this: if you’re writing to the database at the same time someone is reading from it, you don’t want the reader to see an inconsistent (not fully completed) set of data, right? The question then becomes, how do you approach your database so that all operations would be completed when other people see it? The answer is simple — you use MVCC connect!
Purpose of MVCC
By using MVCC, you ensure that if the situation described above occurs to your database, everyone involved will see a completed — or consistent — set of data.
No matter what kind of relational database management system you use, the core purpose of MVCC is to accomplish a couple of goals. These goals are related to isolation, consistency, and durability and these properties will ensure the following:
MVCC connect-compliant database engines like InnoDB ensure these properties are being followed to the tea: if they’re not, the engine is not MVCC compliant.
Your Database and MVCC
Many relational databases will follow MVCC properties by default and there’s no “way to disable MVCC connect” in any DBMS, but there are ways to force certain properties upon your database.
“Forcing” certain properties is not necessarily a bad thing — in many cases, that just means that you’re forcing your database to take things slower to ensure that all MVCC properties are followed.
A very popular way to make a database follow MVCC connect properties later on is by disabling automatic commits by using queries like autocommit=0 or equivalents to this query clause. The autocommit=0 query does exactly what you think it does: it disables automatic commit operations until you tell your database to resume them, or until the transaction is finished (i.e., the entire data dump is re-imported back into your database.)
That is important because by default, many database management systems including MySQL would save the results to your database by committing operations after every INSERT. This can become problematic for those running bigger data sets because such operations would necessitate your database pulling a lot of weight — your database would need to insert data, commit, insert data, commit, insert data, commit, then repeat the same cycle n thousand times.
Not great, right? Setting autocommit to 0 and using COMMIT after all of the data is already inserted into your database ensures that your database works without any overhead added to its operations — do that and you will essentially tell your database “insert all data, commit once you’re done and that’s it.” Your database does that by delaying some MVCC connect properties (consistency) to the very last moment and saves you a lot of headaches in the process.
Modifying MVCC Properties
“If delaying MVCC can be useful, wouldn’t it be more useful to delay, or turn off, MVCC as a whole?”, — we hear you asking. And no, that’s not the case — that’s not the case because.
While delaying MVCC may be useful for those working with bigger sets of data (delaying MVCC commit properties would make bulk INSERT queries faster because of the reasons described above), MVCC is vitally important for your database because without it — you’ve guessed — there would be no data consistency.
So no, turning off MVCC connect properties isn’t possible and doing that wouldn’t be a smart idea even if it would be viable, but there are ways to delay them.
Delaying MVCC connect is a great idea when your data sets get bigger because by doing that, you force your database to skip certain steps when loading data into your database, and thus, speed up INSERT operations. To delay MVCC, do one (or more) of the following:
With that said, do keep in mind that delaying MVCC only helps with data sets that are middle-sized, meaning that these methods would be viable for those working with data sets of under 100 million rows, but at the same time, they may not work for bigger data.
MVCC and Big Data
“Can’t I delay MVCC for big data sets?”, — you ask. Technically you can: practically, your storage engine will do that for you. In other words, once your data set gets bigger, forget about fiddling with MVCC because your database will do that for you — you have better things to care about, don’t you?
Once your data gets bigger, it will be no longer viable to delay MVCC because operations related to it would need to be skipped due to overhead to begin with — in other words, we’d need to use LOAD DATA INFILE instead of INSERT in the place, and thus, disabling autocommit operations applicable to INSERTs would no longer make sense.
SQL Clients
When your data gets bigger, your eyesight will likely turn to database clients. After all, you’re reading a blog of an SQL client right now, aren’t you? You’re in the right place — once your data gets bigger and you’ll have more operations to take care of, you will inevitably need some assistance.
Then, you would need to turn to tools that provide that assistance and DbVisualizer is one of them — from importing table data to its powerful SQL editor with autocomplete capabilities, the features of DbVisualizer are nothing short of impressive.
DbVisuazlier can format your SQL queries and split their results into separate tabs (example below — tabs can also be re-arranged or dragged into separate windows if necessary), help you edit your data as if it was a spreadsheet by opening a set of data in Excel for your convenience, help set user privileges for specific queries, or even create monitors to query your database in a set interval.
Did we mention that DbVisualizer supports more than 50 data sources? Not finding your database in the list would be close to impossible, so try DbVisualizer today, tell us how it goes by tweeting us at DbVisualizer, and until next time.
Summary
Multiversion Concurrency Control — or MVCC connect for short — has to do with providing concurrent access to consistent data sets. MVCC ensures that a person reading from the database will see a data set that has been fully acted on and no “surprises” will await him afterward.
MVCC ensures that data remains isolated, consistent, and durable — and that you, as a DBA, can go on with your pleasant day.
Aside from MVCC, your database needs to take care of a multitude of other things too — as most of them can be automated by SQL clients, DbVisualizer is a great choice both for starters and senior DBAs alike.
FAQ
What is MVCC connect?
MVCC connect is a technique exclusive to database management systems that ensures concurrent access to a consistent data set. MVCC ensures that data remains isolated, consistent, and durable.
Can MVCC connect be turned off?
No — MVCC connect can, however, be delayed. Delaying MVCC may be useful when importing middle-sized data sets.
Where can I learn more about MVCC and other database topics?
You can learn more about MVCC and other database topics by following database-related blogs like the one you’re reading right now (TheTable), attending conferences, workshops and gatherings, or by watching YouTube videos on channels like Database Dive.
